--order 502695 
/*
      CHANGE REPORT for Table DELIVERYSCAN
          - Modify validation rule from (empty) to OUTSCANNODELIVERY IN (0, 1, 2, 3, 4, 5, 6) of column OUTSCANNODELIVERY
      */

ALTER TABLE DELIVERYSCAN ADD ( CONSTRAINT OutscanNoDelivery_Valid
                                          CHECK (OUTSCANNODELIVERY IN (0, 1, 2, 3, 4, 5, 6)));

COMMENT ON COLUMN DELIVERYSCAN.OUTSCANNODELIVERY IS '0 = Normal delivery scan (item is scanned out and 
delivered to customer)
1 = Rejected outscan, item is not delivered to the customer. Only used for modules OUTSCANNODELIVERY and 
BATCHJOB.
2 = Repaired and delivered
3 = Repaired, not delivered
4 = Outline, not delivered
5 = Withdrawal, not delivered
6 = Replacement, not delivered';
      
      /*
      ACTION is CREATE Table WORKORDERLINEREPLACEMENT
      */

CREATE TABLE WORKORDERLINEREPLACEMENT (
       WORKORDERLINEREPLACEMENT_ID NUMBER NOT NULL,
       WORKORDERLINE_ID     NUMBER NOT NULL,
       UNIQUEITEM_LINK_ID   NUMBER NOT NULL,
       REPLACEMENTUNIQUEITEM_LINK_ID NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_WORKORDERLINEREPLACEMENT
START WITH 1
INCREMENT BY 1;

CREATE UNIQUE INDEX XAK1WORKORDERLINEREPLACEMENT ON WORKORDERLINEREPLACEMENT
(
       WORKORDERLINE_ID               ,
       UNIQUEITEM_LINK_ID             
);

CREATE INDEX XIF1WORKORDERLINEREPLACEMENT ON WORKORDERLINEREPLACEMENT
(
       WORKORDERLINE_ID               ASC
);

CREATE INDEX XIF2WORKORDERLINEREPLACEMENT ON WORKORDERLINEREPLACEMENT
(
       UNIQUEITEM_LINK_ID             ASC
);

CREATE INDEX XIF3WORKORDERLINEREPLACEMENT ON WORKORDERLINEREPLACEMENT
(
       REPLACEMENTUNIQUEITEM_LINK_ID  ASC
);


ALTER TABLE WORKORDERLINEREPLACEMENT
       ADD  ( CONSTRAINT XPKWORKORDERLINEREPLACEMENT PRIMARY KEY (
              WORKORDERLINEREPLACEMENT_ID) ) ;

      

ALTER TABLE WORKORDERLINEREPLACEMENT
       ADD  ( CONSTRAINT WORKORDERLINEREPLACEMENT_REPLA
              FOREIGN KEY (REPLACEMENTUNIQUEITEM_LINK_ID)
                             REFERENCES UNIQUEITEM_LINK ) ;


ALTER TABLE WORKORDERLINEREPLACEMENT
       ADD  ( CONSTRAINT WORKORDERLINEREPLACEMENT_UNIQU
              FOREIGN KEY (UNIQUEITEM_LINK_ID)
                             REFERENCES UNIQUEITEM_LINK
                             ON DELETE CASCADE ) ;


ALTER TABLE WORKORDERLINEREPLACEMENT
       ADD  ( CONSTRAINT WORKORDERLINEREPLACEMENT_WORKO
              FOREIGN KEY (WORKORDERLINE_ID)
                             REFERENCES WORKORDERLINE
                             ON DELETE CASCADE ) ;

--order 502716

      /*
      ACTION is CREATE Table MRPCODE
      */

CREATE TABLE MRPCODE (
       MRPCODE_ID           NUMBER NOT NULL,
       CODE                 VARCHAR2(8) NOT NULL,
       DESCRIPTION          VARCHAR2(30) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationField4
                                          CHECK (ACTIVE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE SEQUENCE SEQ_MRPCODE
START WITH 1
INCREMENT BY 1;

CREATE UNIQUE INDEX XAK1MRPCODE ON MRPCODE ( CODE );


ALTER TABLE MRPCODE
       ADD  ( CONSTRAINT XPKMRPCODE PRIMARY KEY (MRPCODE_ID) ) ;

      
      /*
      CHANGE REPORT for Table PRODUCT
          - Adding a new relationship PROD_MRPCODE_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PROD_MRPCODE_ID of table PRODUCT, which references table MRPCODE).
          - Adding column MRPCODE_ID
      ACTION is ALTER or modify Table PRODUCT
      */

ALTER TABLE PRODUCT ADD (MRPCODE_ID           NUMBER NULL);


ALTER TABLE PRODUCT
       ADD  ( CONSTRAINT PROD_MRPCODE_ID
              FOREIGN KEY (MRPCODE_ID)
                             REFERENCES MRPCODE ) ;



