--ABS-140136 Rework items - AUS Cleanroom epic

--CleanRoomBatchWashing

CREATE SEQUENCE SEQ_CLEANROOMBATCHWASHING
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHWASHING(
                                   CLEANROOMBATCHWASHING_ID NUMBER NOT NULL,
                                   CLEANROOMBATCH_ID NUMBER NOT NULL,
                                   MACHINE_ID NUMBER NULL,
                                   WASHINGPROCESS_ID NUMBER NULL,
                                   WASHUSER_ID NUMBER NOT NULL,
                                   WASHEDDATE DATE NOT NULL,
                                   SYSTEMUSER_ID NUMBER NOT NULL,
                                   TIMESTAMP DATE NOT NULL);
                                   
ALTER TABLE CLEANROOMBATCHWASHING
ADD CONSTRAINT XPKCLEANROOMBATCHWASHING PRIMARY KEY(CLEANROOMBATCHWASHING_ID);

CREATE INDEX XIF1CLEANROOMBATCHWASHING ON CLEANROOMBATCHWASHING(CLEANROOMBATCH_ID ASC);

CREATE INDEX XIF2CLEANROOMBATCHWASHING ON CLEANROOMBATCHWASHING(MACHINE_ID ASC);

CREATE INDEX XIF3CLEANROOMBATCHWASHING ON CLEANROOMBATCHWASHING(WASHINGPROCESS_ID ASC);

CREATE INDEX XIF4CLEANROOMBATCHWASHING ON CLEANROOMBATCHWASHING(WASHUSER_ID ASC);

ALTER TABLE CLEANROOMBATCHWASHING
ADD CONSTRAINT CLEANROOMBATCHWASHING_CRB FOREIGN KEY(CLEANROOMBATCH_ID) REFERENCES CLEANROOMBATCH(CLEANROOMBATCH_ID) ON DELETE CASCADE;

ALTER TABLE CLEANROOMBATCHWASHING
ADD CONSTRAINT CLEANROOMBATCHWASHING_MACHINE FOREIGN KEY(MACHINE_ID) REFERENCES MACHINE(MACHINE_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBATCHWASHING
ADD CONSTRAINT CLEANROOMBATCHWASHING_WASHPROCESS FOREIGN KEY(WASHINGPROCESS_ID) REFERENCES WASHINGPROCESS(WASHINGPROCESS_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBATCHWASHING
ADD CONSTRAINT CLEANROOMBATCHWASHING_WASHUSER FOREIGN KEY(WASHUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID);

--ExternalSterilizationLot

ALTER TABLE EXTERNALSTERILIZATIONLOT
ADD (STERILIZATIONLOTTYPE VARCHAR2(2) NOT NULL CONSTRAINT CHK_EXTERNALSTERILIZATIONLOT_LOTTYPE CHECK (STERILIZATIONLOTTYPE IN('YN','NY', 'YY','NN')));

ALTER TABLE EXTERNALSTERILIZATIONLOT
DROP CONSTRAINT CHK_EXTERNALSTERILIZATIONLOT_STATUS;

ALTER TABLE EXTERNALSTERILIZATIONLOT
ADD CONSTRAINT CHK_EXTERNALSTERILIZATIONLOT_STATUS CHECK (STATUS IN(0, 1, 2));

COMMENT ON COLUMN EXTERNALSTERILIZATIONLOT.STERILIZATIONLOTTYPE IS 'YN = 6 Months
                                                                    NY = 12 Months
                                                                    YY = 6 and 12 Months
                                                                    NN = Not Applicable
                                                                    YN characters allowed. First char is for 6 Months, second is for 12 Months.';

COMMENT ON COLUMN EXTERNALSTERILIZATIONLOT.STATUS IS '0 = Open
                                                      1 = Closed
                                                      2 = Active';