/*ORDER 502239*/
      /*
      CHANGE REPORT for Table WORKORDER
          - Adding a new relationship WORKORDER_DRIVEREMPLOYEE_ID
          - Adding a new relationship WORKORDER_ROUTECONTROL_ID
          - Adding a new relationship WORKORDER_ROUTESTOP_ID
          - Adding a new relationship WORKORDER_STOP_ID
          - Adding a new relationship WORKORDER_TRUCK_ID
          - Changing column definition of ORIGIN
          - Adding column DRIVERSALES
          - Adding column TRUCK_ID
          - Adding column DRIVEREMPLOYEE_ID
          - Adding column ROUTECONTROL_ID
          - Adding column STOP_ID
          - Adding column ROUTESTOP_ID
          - Adding column EXPECTEDMOUNTINGDATE
          - Adding column MOUNTINGDATE
          - Adding column CONFIRMATIONDATE
          - Adding index XIF14WORKORDER
          - Adding index XIF15WORKORDER
          - Adding index XIF16WORKORDER
          - Adding index XIF17WORKORDER
          - Adding index XIF18WORKORDER
         */


ALTER TABLE WORKORDER ADD (DRIVERSALES            VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew117
                                          CHECK (DRIVERSALES IN ('Y', 'N')));

ALTER TABLE WORKORDER ADD (TRUCK_ID     NUMBER NULL);

ALTER TABLE WORKORDER ADD (DRIVEREMPLOYEE_ID   NUMBER NULL);

ALTER TABLE WORKORDER ADD  (ROUTECONTROL_ID      NUMBER NULL);

ALTER TABLE WORKORDER ADD  (STOP_ID              NUMBER NULL);

ALTER TABLE WORKORDER ADD  (ROUTESTOP_ID         NUMBER NULL);

ALTER TABLE WORKORDER ADD  (EXPECTEDMOUNTINGDATE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL);

ALTER TABLE WORKORDER ADD  (MOUNTINGDATE         DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL);

ALTER TABLE WORKORDER ADD  (CONFIRMATIONDATE     DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL);

COMMENT ON COLUMN WORKORDER.ORIGIN IS '1 = Generated from wearer inventory
2 = Manual created work order
3 = Created by web module
4 = Created by route assistant
5 = Conversion
6 = Sales note
7 = Credit note
8 = Internal Purchase Order
9 = Stock Delivery Order
10 = Mounting Order
11 = Dismounting Order';

CREATE INDEX XIF14WORKORDER ON WORKORDER
(
       TRUCK_ID                       ASC
);

CREATE INDEX XIF15WORKORDER ON WORKORDER
(
       DRIVEREMPLOYEE_ID              ASC
);

CREATE INDEX XIF16WORKORDER ON WORKORDER
(
       ROUTECONTROL_ID                ASC
);

CREATE INDEX XIF17WORKORDER ON WORKORDER
(
       STOP_ID                        ASC
);

CREATE INDEX XIF18WORKORDER ON WORKORDER
(
       ROUTESTOP_ID                   ASC
);

ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_ROUTESTOP_ID
              FOREIGN KEY (ROUTESTOP_ID)
                             REFERENCES ROUTESTOP ) ;


ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_STOP_ID
              FOREIGN KEY (STOP_ID)
                             REFERENCES STOP ) ;


ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_ROUTECONTROL_ID
              FOREIGN KEY (ROUTECONTROL_ID)
                             REFERENCES ROUTECONTROL ) ;


ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_DRIVEREMPLOYEE_ID
              FOREIGN KEY (DRIVEREMPLOYEE_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE WORKORDER
       ADD  ( CONSTRAINT WORKORDER_TRUCK_ID
              FOREIGN KEY (TRUCK_ID)
                             REFERENCES TRUCK ) ;

      /*
      CHANGE REPORT for Table WORKORDERLINE
          - Adding column SALESITEM
          - Adding column STARTSERVICE
          - Adding column TOBEMOUNTED
          - Adding column QUANTITYFIRSTDELIVERY
          - Adding column QUANTITYMOUNTED
      ACTION is ALTER or modify Table WORKORDERLINE
      */

ALTER TABLE WORKORDERLINE ADD (SALESITEM            VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew118
                                          CHECK (SALESITEM IN ('Y', 'N')));


ALTER TABLE WORKORDERLINE ADD (STARTSERVICE         VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew119
                                          CHECK (STARTSERVICE IN ('Y', 'N')));


ALTER TABLE WORKORDERLINE ADD (TOBEMOUNTED          VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_ValidationNew120
                                          CHECK (TOBEMOUNTED IN ('Y', 'N')));


ALTER TABLE WORKORDERLINE ADD (QUANTITYFIRSTDELIVERY NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE WORKORDERLINE ADD (QUANTITYMOUNTED      NUMBER DEFAULT 0 NOT NULL);


INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '05', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'WAITING FOR APPROVAL');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '10', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'NEW');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '12', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'NOT AVAILABLE');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '15', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'PICKED');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '20', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'IN PROGRESS');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '25', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'IN PROGRESS WITH PRIORITY');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '30', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'COMPLETED');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '40', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'CANCELLED');

INSERT INTO STATUS(STATUS_ID,STATUSTYPE,CODE,LEGACYSTATUSCODE,SYSTEMUSER_ID,TIMESTAMP,FACTORYDESCRIPTION)
VALUES(SEQ_STATUS.NEXTVAL, 'MO', '50', '', GETMINSYSTEMUSER(), GETCURRENTDATETIME(), 'CONFIRMED');

INSERT INTO STATUS_DESC
          (STATUS_ID, LANGUAGE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
          SELECT STATUS_ID,
                 GETMINLANGUAGE(),
                 FACTORYDESCRIPTION,
                 SYSTEMUSER_ID,
                 TIMESTAMP
            FROM STATUS
            WHERE STATUS.STATUSTYPE = 'MO' AND STATUS.CODE IN ('05','10','12','15','20','25','30','40','50');