 --order 502753
      /*
      ACTION is CREATE Table ROUTECHANGETRANSACTION
      */

CREATE TABLE ROUTECHANGETRANSACTION (
       ROUTECHANGETRANSACTION_ID NUMBER NOT NULL,
       TRANSACTIONNUMBER    NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT TransactionNumber_Valid
                                          CHECK (TRANSACTIONNUMBER IN (0, 1, 2, 3, 4, 5, 6, 7)),
       STATUS               NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT Status_Valid
                                          CHECK (STATUS IN (0, 1, 2, 3, 4)),
       ERRORMSG             VARCHAR2(1000) NULL,
       BATCHTRANSACTIONNUMBER NUMBER DEFAULT 0 NOT NULL,
       CHANGESTARTDATE      DATE NOT NULL,
       CUSTOMER_ID          NUMBER NULL,
       DELIVERYPOINT_ID     NUMBER NULL,
       PLANT_ID             NUMBER NOT NULL,
       ROUTENUMBER          NUMBER NULL,
       DRIVINGDAY_ID        NUMBER NULL,
       DEPOTCODE            VARCHAR2(6) NULL,
       ROUTE_ID             NUMBER NULL,
       STOPNUMBER           NUMBER NULL,
       ROUTESTOP_ID         NUMBER NULL,
       DELIVERYDAY_ID       NUMBER NULL,
       DELIVERYSCHEME_ID    NUMBER NULL,
       NEWROUTE_ID          NUMBER NULL,
       NEWROUTENUMBER       NUMBER NULL,
       NEWSTOPNUMBER        NUMBER NULL,
       NEWDRIVINGDAY_ID     NUMBER NULL,
       NEWDELIVERYSCHEME_ID NUMBER NULL,
       PRODUCTGROUP_ID      NUMBER NULL,
       CREATIONTIMESTAMP    DATE NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT SystemUserID_Valid
                                          CHECK (SYSTEMUSER_ID IN (0))
);

CREATE SEQUENCE SEQ_ROUTECHANGETRANSACTION
START WITH 1
INCREMENT BY 1;

CREATE INDEX XIF1ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       CUSTOMER_ID                    ASC
);

CREATE INDEX XIF2ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       DELIVERYPOINT_ID               ASC
);

CREATE INDEX XIF3ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       PLANT_ID                       ASC
);

CREATE INDEX XIF4ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       DRIVINGDAY_ID                  ASC
);

CREATE INDEX XIF5ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       ROUTE_ID                       ASC
);

CREATE INDEX XIF6ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       ROUTESTOP_ID                   ASC
);

CREATE INDEX XIF7ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       DELIVERYDAY_ID                 ASC
);

CREATE INDEX XIF8ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       DELIVERYSCHEME_ID              ASC
);

CREATE INDEX XIF9ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       NEWROUTE_ID                    ASC
);

CREATE INDEX XIF10ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       NEWDRIVINGDAY_ID               ASC
);

CREATE INDEX XIF11ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       NEWDELIVERYSCHEME_ID           ASC
);

CREATE INDEX XIF12ROUTECHANGETRANSACTION ON ROUTECHANGETRANSACTION
(
       PRODUCTGROUP_ID                ASC
);

ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT XPKROUTECHANGETRANSACTION PRIMARY KEY (
              ROUTECHANGETRANSACTION_ID) ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_PRODUCT
              FOREIGN KEY (PRODUCTGROUP_ID)
                             REFERENCES PRODUCTGROUP ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_NEWDELI
              FOREIGN KEY (NEWDELIVERYSCHEME_ID)
                             REFERENCES DELIVERYSCHEME ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_NEWDRIV
              FOREIGN KEY (NEWDRIVINGDAY_ID)
                             REFERENCES DAY ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_NEWROUT
              FOREIGN KEY (NEWROUTE_ID)
                             REFERENCES ROUTE ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_DELIVS
              FOREIGN KEY (DELIVERYSCHEME_ID)
                             REFERENCES DELIVERYSCHEME ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_DELIVD
              FOREIGN KEY (DELIVERYDAY_ID)
                             REFERENCES DAY ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_ROUTEST
              FOREIGN KEY (ROUTESTOP_ID)
                             REFERENCES ROUTESTOP ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_ROUTE_I
              FOREIGN KEY (ROUTE_ID)
                             REFERENCES ROUTE ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_DRIVING
              FOREIGN KEY (DRIVINGDAY_ID)
                             REFERENCES DAY ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_PLANT_I
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_DELIVER
              FOREIGN KEY (DELIVERYPOINT_ID)
                             REFERENCES DELIVERYPOINT ) ;


ALTER TABLE ROUTECHANGETRANSACTION
       ADD  ( CONSTRAINT ROUTECHANGETRANSACTION_CUSTOME
              FOREIGN KEY (CUSTOMER_ID)
                             REFERENCES CUSTOMER ) ;



