--order 502759
/*
      ACTION is CREATE Table ROUTEASSISTANTTRANSACTION
      */

CREATE TABLE ROUTEASSISTANTTRANSACTION (
       ROUTEASSISTANTTRANSACTION_ID NUMBER NOT NULL,
       TRANSACTIONTYPE      NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT TransactionType_Valid
                                          CHECK (TRANSACTIONTYPE IN (1, 2, 3, 4)),
       STATUS               NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT StatusRouteAss_Valid
                                          CHECK (STATUS IN (0, 1, 2, 3, 4)),
       IMPORTTIMESTAMP      DATE NOT NULL,
       PROCESSEDBYUSER_ID   NUMBER DEFAULT 0 NOT NULL,
       PROCESSEDTIMESTAMP   DATE NULL,
       CANCELLEDBYUSER_ID   NUMBER DEFAULT 0 NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       CUSTOMER_LINK_ID     NUMBER NOT NULL,
       ROUTECONTROL_ID      NUMBER NOT NULL,
       STOP_ID              NUMBER NOT NULL,
       PRODUCT_LINK_ID      NUMBER NOT NULL,
       SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       FINISHINGMETHOD_ID   NUMBER NOT NULL,
       DELIVERYSCHEME_ID    NUMBER NULL,
       FIXEDQUANTITY        NUMBER NULL,
       CIRCQUANTITY         NUMBER NULL,
       MINIMUMPERCENTAGE    NUMBER NULL,
       BILLINGMETHOD        NUMBER NULL,
       PRICE                NUMBER NULL,
       DATEACTIVE           DATE NULL,
       PRODUCTLISTPERCUSTOMER_ID NUMBER NULL,
       STANDARDDELIVERYLINE_ID NUMBER NULL,
       RENTALQTYADJUSTMENT_ID NUMBER NULL,
       QUITDATE             DATE NULL,
       TEMPDELSTOPSTARTDATE DATE NULL,
       TEMPDELSTOPENDDATE   DATE NULL,
       TEMPDELSTOPRENT      VARCHAR2(1) NULL,
       TEMPORARYDELIVERYSTOP_ID NUMBER NULL,
       WEAREREMPLOYMENT_LINK_ID NUMBER NULL,
       WEARERINVENTORY_ID   NUMBER NULL,
       UNIQUEITEM_LINK_ID   NUMBER NULL,
       ERRORMESSAGE         VARCHAR2(300) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_ROUTEASSISTANTTRANSACTION
START WITH 1
INCREMENT BY 1;

COMMENT ON COLUMN ROUTEASSISTANTTRANSACTION.TRANSACTIONTYPE IS '1 = New contract (PLPC)
2 = Quit product
3 = Modify quantity
4 = Add Temporary delivery stop';

COMMENT ON COLUMN ROUTEASSISTANTTRANSACTION.STATUS IS '0 = New
1 = In progress
2 = Processed (completed)
3 = Error (not processed)
4 = cancelled (not processed)';

CREATE INDEX XIF1ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIF2ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       CUSTOMER_LINK_ID               ASC
);

CREATE INDEX XIF3ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       ROUTECONTROL_ID                ASC
);

CREATE INDEX XIF4ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       STOP_ID                        ASC
);

CREATE INDEX XIF5ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       PRODUCT_LINK_ID                ASC
);

CREATE INDEX XIF6ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       SIZEDEFINITION_LINK_ID         ASC
);

CREATE INDEX XIF7ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       FINISHINGMETHOD_ID             ASC
);

CREATE INDEX XIF8ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       DELIVERYSCHEME_ID              ASC
);

CREATE INDEX XIF9ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       PRODUCTLISTPERCUSTOMER_ID      ASC
);

CREATE INDEX XIF10ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       STANDARDDELIVERYLINE_ID        ASC
);

CREATE INDEX XIF11ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       RENTALQTYADJUSTMENT_ID         ASC
);

CREATE INDEX XIF12ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       TEMPORARYDELIVERYSTOP_ID       ASC
);

CREATE INDEX XIF13ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       WEAREREMPLOYMENT_LINK_ID       ASC
);

CREATE INDEX XIF14ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       WEARERINVENTORY_ID             ASC
);

CREATE INDEX XIF15ROUTEASSISTANTTRANSACTION ON ROUTEASSISTANTTRANSACTION
(
       UNIQUEITEM_LINK_ID             ASC
);

ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT XPKROUTEASSISTANTTRANSACTION PRIMARY KEY (
              ROUTEASSISTANTTRANSACTION_ID) ) ;

      
      /*
      CHANGE REPORT for Table MOBILEDEVICE
          - Adding column LASTLOADTIMESTAMP
          - Adding column INITIALLOADREQUIRED
      ACTION is ALTER or modify Table MOBILEDEVICE
      */

ALTER TABLE MOBILEDEVICE ADD (LASTLOADTIMESTAMP    DATE NULL);


ALTER TABLE MOBILEDEVICE ADD (INITIALLOADREQUIRED  VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationField197
                                          CHECK (INITIALLOADREQUIRED IN ('Y', 
    'N')));

      
      /*
      CHANGE REPORT for Table PLANTPRODUCTSETTINGS
          - Adding column ALLOWROUTEASSISTANTADD
      ACTION is ALTER or modify Table PLANTPRODUCTSETTINGS
      */

ALTER TABLE PLANTPRODUCTSETTINGS ADD (ALLOWROUTEASSISTANTADD VARCHAR2(1) 
    DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField196
                                          CHECK (ALLOWROUTEASSISTANTADD IN ('Y',
     'N')));


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_UNIQ
              FOREIGN KEY (UNIQUEITEM_LINK_ID)
                             REFERENCES UNIQUEITEM_LINK ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_WEAR
              FOREIGN KEY (WEARERINVENTORY_ID)
                             REFERENCES WEARERINVENTORY ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_WEA
              FOREIGN KEY (WEAREREMPLOYMENT_LINK_ID)
                             REFERENCES WEAREREMPLOYMENT_LINK ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_TEMP
              FOREIGN KEY (TEMPORARYDELIVERYSTOP_ID)
                             REFERENCES TEMPORARYDELIVERYSTOP ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_RENT
              FOREIGN KEY (RENTALQTYADJUSTMENT_ID)
                             REFERENCES RENTALQTYADJUSTMENT ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_STAN
              FOREIGN KEY (STANDARDDELIVERYLINE_ID)
                             REFERENCES STANDARDDELIVERYLINE ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_PROD
              FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
                             REFERENCES PRODUCTLISTPERCUSTOMER ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_DELI
              FOREIGN KEY (DELIVERYSCHEME_ID)
                             REFERENCES DELIVERYSCHEME ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_FINI
              FOREIGN KEY (FINISHINGMETHOD_ID)
                             REFERENCES FINISHINGMETHOD ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_SIZE
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_PRO
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_STOP
              FOREIGN KEY (STOP_ID)
                             REFERENCES STOP
                             ON DELETE CASCADE ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_ROUT
              FOREIGN KEY (ROUTECONTROL_ID)
                             REFERENCES ROUTECONTROL
                             ON DELETE CASCADE ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_CUST
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK
                             ON DELETE CASCADE ) ;


ALTER TABLE ROUTEASSISTANTTRANSACTION
       ADD  ( CONSTRAINT ROUTEASSISTANTTRANSACTION_BUSI
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;



