-- ABS-30104
--------------- CLEANROOMBATCH --------------- 
ALTER TABLE CLEANROOMBATCH MODIFY REMARK VARCHAR2(80);

ALTER TABLE CLEANROOMBATCH ADD (INTERNALCART_ID NUMBER,
                                MACHINECOMPARTMENT_ID NUMBER,
                                WASHINGPROCESS_ID NUMBER,
                                REPAIRBATCH VARCHAR2(1) DEFAULT 'N' NOT NULL 
                                            CONSTRAINT YN_VALIDATIONFIELD318 CHECK (REPAIRBATCH IN ('Y', 'N')),
                                REJECTEDEMPLOYEE_ID NUMBER,
                                REJECTEDDATE DATE,
                                WASHEDUSER_ID NUMBER,
                                WASHEDDATE DATE,
                                DRIEDUSER_ID NUMBER,
                                DRIEDDATE DATE,
                                BATCHCARDPRINTEDEMPLOYEE_ID NUMBER,
                                BATCHCARDPRINTEDDATE DATE,
                                FOLDEDUSER_ID NUMBER,
                                FOLDEDDATE DATE,
                                OUTOFCLEANROOMUSER_ID NUMBER,
                                OUTOFCLEANROOMDATE DATE,
                                CLONEDFROMBATCH_ID NUMBER,
                                CANCELLEDEMPLOYEE_ID NUMBER,
                                CANCELLEDDATE DATE);

COMMENT ON COLUMN CLEANROOMBATCH.BATCHSTATUS
  IS '0 = Open 1 = Closed 2 = Completed 3 = Rejected 4 = Washed 5 = Dried 6 = Folded 7 = Out of cleanroom 10 = Cancelled';

CREATE INDEX XIF8CLEANROOMBATCH ON CLEANROOMBATCH (INTERNALCART_ID ASC);
CREATE INDEX XIF9CLEANROOMBATCH ON CLEANROOMBATCH (MACHINECOMPARTMENT_ID ASC);
CREATE INDEX XIF10CLEANROOMBATCH ON CLEANROOMBATCH (WASHINGPROCESS_ID ASC);
CREATE INDEX XIF11CLEANROOMBATCH ON CLEANROOMBATCH (REJECTEDEMPLOYEE_ID ASC);
CREATE INDEX XIF12CLEANROOMBATCH ON CLEANROOMBATCH (BATCHCARDPRINTEDEMPLOYEE_ID ASC);
CREATE INDEX XIF13CLEANROOMBATCH ON CLEANROOMBATCH (CLONEDFROMBATCH_ID ASC);
CREATE INDEX XIF14CLEANROOMBATCH ON CLEANROOMBATCH (CANCELLEDEMPLOYEE_ID ASC);

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_INTERNALCART FOREIGN KEY (INTERNALCART_ID)
  REFERENCES CART (CART_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_MACHINECOMPART FOREIGN KEY (MACHINECOMPARTMENT_ID)
  REFERENCES MACHINECOMPARTMENT (MACHINECOMPARTMENT_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_WASHINGPROCESS FOREIGN KEY (WASHINGPROCESS_ID)
  REFERENCES WASHINGPROCESS (WASHINGPROCESS_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_REJECTEDUSER FOREIGN KEY (REJECTEDEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);  
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CARDPRINTEDEMPL FOREIGN KEY (BATCHCARDPRINTEDEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);  
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CLONEDFROM FOREIGN KEY (CLONEDFROMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CANCELLEDEMPL FOREIGN KEY (CANCELLEDEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
     
ALTER TABLE CLEANROOMBATCH
  DROP CONSTRAINT BATCHSTATUS_VALID1_1;
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT BATCHSTATUS_VALID1_1
  CHECK (BATCHSTATUS IN (0,1,2,3,4,5,6,7,10));

--------------- CLEANROOMBATCHCUSTOMER --------------- 
CREATE SEQUENCE SEQ_CLEANROOMBATCHCUSTOMER
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHCUSTOMER
( CLEANROOMBATCHCUSTOMER_ID NUMBER NOT NULL,
  CLEANROOMBATCH_ID         NUMBER NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
  DELIVERYDATE              DATE NOT NULL,
  DELIVERYROUTE_ID          NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);
  
 
ALTER TABLE CLEANROOMBATCHCUSTOMER
  ADD CONSTRAINT XPKCLEANROOMBATCHCUSTOMER PRIMARY KEY (CLEANROOMBATCHCUSTOMER_ID);

CREATE UNIQUE INDEX XAKCLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (CLEANROOMBATCH_ID ASC, DISTRIBUTIONPOINT_LINK_ID ASC, DELIVERYDATE ASC, DELIVERYROUTE_ID ASC);
CREATE INDEX XIE1CLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (DELIVERYDATE ASC);
CREATE INDEX XIF1CLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (CLEANROOMBATCH_ID ASC);
CREATE INDEX XIF2CLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF3CLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (DELIVERYROUTE_ID ASC);

ALTER TABLE CLEANROOMBATCHCUSTOMER
  ADD CONSTRAINT CLEANROOMBATCHCUSTOMER_BATCH FOREIGN KEY (CLEANROOMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHCUSTOMER
  ADD CONSTRAINT CLEANROOMBATCHCUSTOMER_CPL FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE CLEANROOMBATCHCUSTOMER
  ADD CONSTRAINT CLEANROOMBATCHCUSTOMER_ROUTE FOREIGN KEY (DELIVERYROUTE_ID)
  REFERENCES ROUTE (ROUTE_ID);


--------------- CLEANROOMBATCHCONTENT --------------- 
ALTER TABLE CLEANROOMBATCHCONTENT ADD (PRODUCTWEIGHT NUMBER(15,6),
                                       CLEANROOMBATCHCUSTOMER_ID NUMBER,
                                       CUSTOMERCONTAINER VARCHAR2(15),
                                       INSCANRECORD_ID NUMBER,
                                       OUTSCANRECORD_ID NUMBER);

CREATE INDEX XIF4CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (CLEANROOMBATCHCUSTOMER_ID ASC);
CREATE INDEX XIF5CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (INSCANRECORD_ID ASC);
CREATE INDEX XIF6CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (OUTSCANRECORD_ID ASC);

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_CBC FOREIGN KEY (CLEANROOMBATCHCUSTOMER_ID)
  REFERENCES CLEANROOMBATCHCUSTOMER (CLEANROOMBATCHCUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_INSCAN FOREIGN KEY (INSCANRECORD_ID)
  REFERENCES SCAN (SCAN_ID) ON DELETE SET NULL;
ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_OUTSCAN FOREIGN KEY (OUTSCANRECORD_ID)
  REFERENCES SCAN (SCAN_ID) ON DELETE SET NULL;
  
--------------- CLEANROOMBATCHCOUNTCONTENT --------------- 
CREATE SEQUENCE SEQ_CLEANROOMBATCHCOUNTCONTENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHCOUNTCONTENT
( CLEANROOMBATCHCOUNTCONTENT_ID NUMBER NOT NULL,
  CLEANROOMBATCHCUSTOMER_ID     NUMBER NOT NULL,
  PRODUCT_LINK_ID               NUMBER NOT NULL,
  FINISHINGMETHOD_ID            NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID        NUMBER NOT NULL,
  QUANTITY                      NUMBER NOT NULL,
  ENTRYWORKSTATION_ID           NUMBER NOT NULL,
  PRODUCTWEIGHT                 NUMBER(15,6) NOT NULL,
  CUSTOMERCONTAINER             VARCHAR2(15),
  SOILCOUNT_ID                  NUMBER NOT NULL,
  DELIVERYVARIATION_ID          NUMBER,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL);
  
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT XPKCLEANROOMBATCHCOUNTCONTENT PRIMARY KEY (CLEANROOMBATCHCOUNTCONTENT_ID);

CREATE UNIQUE INDEX XAK1CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (PRODUCT_LINK_ID  ASC, FINISHINGMETHOD_ID ASC, SIZEDEFINITION_LINK_ID ASC, CLEANROOMBATCHCUSTOMER_ID ASC, CUSTOMERCONTAINER ASC);
CREATE INDEX XIF1CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (CLEANROOMBATCHCUSTOMER_ID ASC);
CREATE INDEX XIF2CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF4CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (ENTRYWORKSTATION_ID ASC);
CREATE INDEX XIF6CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (SOILCOUNT_ID ASC);
CREATE INDEX XIF7CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (DELIVERYVARIATION_ID ASC);

ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_BATCHCUST FOREIGN KEY (CLEANROOMBATCHCUSTOMER_ID)
  REFERENCES CLEANROOMBATCHCUSTOMER (CLEANROOMBATCHCUSTOMER_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_PRODUCT FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_SIZE FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_STATION FOREIGN KEY (ENTRYWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID);
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_SOILCOUNT FOREIGN KEY (SOILCOUNT_ID)
  REFERENCES SOILCOUNT (SOILCOUNT_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_DELVAR FOREIGN KEY (DELIVERYVARIATION_ID)
  REFERENCES DELIVERYVARIATIONS (DELIVERYVARIATIONS_ID);
  
--------------- BUSINESSUNIT ---------------   
ALTER TABLE BUSINESSUNIT ADD (CLEANROOMBATCHNUMBERPREFIX VARCHAR2(6),
                              CLEANROOMBATCHNUMBERLENGTH NUMBER);
  

--------------- DELIVERYVARIATIONS ---------------   
ALTER TABLE DELIVERYVARIATIONS ADD (CLEANROOMBATCH_ID NUMBER);

CREATE INDEX XIF14DELIVERYVARIATIONS ON DELIVERYVARIATIONS (CLEANROOMBATCH_ID ASC);

ALTER TABLE DELIVERYVARIATIONS
  ADD CONSTRAINT DELVAR_CLEANROOMBATCH FOREIGN KEY (CLEANROOMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID) ON DELETE SET NULL;


--------------- REASONCODEDIALOGS ---------------   
INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'CleanroomBatchStatusControl';
COMMIT;

INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'CleanroomBatch';
COMMIT;
