-- ABS-30104

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

CREATE INDEX XIF6CART ON CART (CLEANROOMBATCH_ID ASC);

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

----------------- CLEANROOMBATCH ----------------- 
ALTER TABLE CLEANROOMBATCH ADD (LASTUPDATEDWORKSTATION_ID NUMBER,
                                CREATIONEMPLOYEE_ID NUMBER,
                                CLOSINGEMPLOYEE_ID NUMBER);

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

CREATE INDEX XIF15CLEANROOMBATCH ON CLEANROOMBATCH (LASTUPDATEDWORKSTATION_ID ASC);
CREATE INDEX XIF16CLEANROOMBATCH ON CLEANROOMBATCH (CREATIONEMPLOYEE_ID ASC);
CREATE INDEX XIF17CLEANROOMBATCH ON CLEANROOMBATCH (CLOSINGEMPLOYEE_ID ASC);

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_LASTWORKSTATION FOREIGN KEY (LASTUPDATEDWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CREATIONEMPL FOREIGN KEY (CREATIONEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CLOSINGEMPLOYEE FOREIGN KEY (CLOSINGEMPLOYEE_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,8,10));
  

----------------- CLEANROOMBATCHCONTENT -----------------
ALTER TABLE CLEANROOMBATCHCONTENT ADD (STERILIZATIONRUN_ID NUMBER);

CREATE INDEX XIF7CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (STERILIZATIONRUN_ID ASC);

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_STERIL FOREIGN KEY (STERILIZATIONRUN_ID)
  REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID);


----------------- CLEANROOMBATCHCOUNTCONTENT -----------------
ALTER TABLE CLEANROOMBATCHCOUNTCONTENT ADD (STERILIZATIONRUN_ID NUMBER); 

CREATE INDEX XIF8CLEANROOMBATCHCOUNTCONTENT ON CLEANROOMBATCHCOUNTCONTENT (STERILIZATIONRUN_ID ASC);

ALTER TABLE CLEANROOMBATCHCOUNTCONTENT
  ADD CONSTRAINT CRBATCHCOUNTCONTENT_STERIL FOREIGN KEY (STERILIZATIONRUN_ID)
  REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID);


----------------- CLEANROOMBATCHCUSTOMER -----------------
ALTER TABLE CLEANROOMBATCHCUSTOMER MODIFY DELIVERYROUTE_ID NULL;

DROP INDEX XAKCLEANROOMBATCHCUSTOMER;
CREATE UNIQUE INDEX XAK1CLEANROOMBATCHCUSTOMER ON CLEANROOMBATCHCUSTOMER (CLEANROOMBATCH_ID, DISTRIBUTIONPOINT_LINK_ID, DELIVERYDATE, NVL(DELIVERYROUTE_ID,0));
