-- ABS-132486 / ABS-137929

----------- CLEANROOMBATCH -----------
ALTER TABLE CLEANROOMBATCH ADD (PACKEDDATE DATE NULL,
                                PACKEDUSER_ID NUMBER NULL,
                                SHIPPEDDATE DATE NULL,
                                SHIPPEDUSER_ID NUMBER NULL);

CREATE INDEX XIF18CLEANROOMBATCH ON CLEANROOMBATCH (PACKEDUSER_ID);
CREATE INDEX XIF19CLEANROOMBATCH ON CLEANROOMBATCH (SHIPPEDUSER_ID);

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_PACKEDUSER FOREIGN KEY (PACKEDUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID);
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_SHIPPEDUSER FOREIGN KEY (SHIPPEDUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID);
  
----------- CLEANROOMBATCHPACKINGNOTE -----------
CREATE SEQUENCE SEQ_CLEANROOMBATCHPACKINGNOTE
START WITH 1 
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHPACKINGNOTE
( CLEANROOMBATCHPACKINGNOTE_ID NUMBER NOT NULL,
  CLEANROOMBATCH_ID            NUMBER NOT NULL,
  PACKINGNOTE_ID               NUMBER NOT NULL,
  EXTERNALSTERILIZATIONLOT_ID  NUMBER NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL);

ALTER TABLE CLEANROOMBATCHPACKINGNOTE
  ADD CONSTRAINT XPKCLEANROOMBATCHPACKINGNOTE PRIMARY KEY (CLEANROOMBATCHPACKINGNOTE_ID);

CREATE UNIQUE INDEX XAK1CLEANROOMBATCHPACKINGNOTE ON CLEANROOMBATCHPACKINGNOTE (PACKINGNOTE_ID);
  
CREATE INDEX XIF1CLEANROOMBATCHPACKINGNOTE ON CLEANROOMBATCHPACKINGNOTE (CLEANROOMBATCH_ID);
CREATE INDEX XIF2CLEANROOMBATCHPACKINGNOTE ON CLEANROOMBATCHPACKINGNOTE (EXTERNALSTERILIZATIONLOT_ID);


ALTER TABLE CLEANROOMBATCHPACKINGNOTE
  ADD CONSTRAINT CLEANROOMBATCHPACKINGNOTE_CLEANROOMBATCH FOREIGN KEY (CLEANROOMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHPACKINGNOTE
  ADD CONSTRAINT CLEANROOMBATCHPACKINGNOTE_PACKINGNOTE FOREIGN KEY (PACKINGNOTE_ID)
  REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;
ALTER TABLE CLEANROOMBATCHPACKINGNOTE
  ADD CONSTRAINT CLEANROOMBATCHPACKINGNOTE_EXTERNALSTERILIZATIONLOT FOREIGN KEY (EXTERNALSTERILIZATIONLOT_ID)
  REFERENCES EXTERNALSTERILIZATIONLOT (EXTERNALSTERILIZATIONLOT_ID);

