--ABS-132566 Cleanroom External Sterilization Lot number management

--ExternalSterilizationLot

CREATE SEQUENCE SEQ_EXTERNALSTERILIZATIONLOT
START WITH 1
INCREMENT BY 1;

CREATE TABLE EXTERNALSTERILIZATIONLOT(EXTERNALSTERILIZATIONLOT_ID NUMBER NOT NULL,
                                      BUSINESSUNIT_ID NUMBER NOT NULL,
                                      LOTNUMBER VARCHAR2(50) NOT NULL,
                                      CREATIONDATE DATE NOT NULL,
                                      CREATIONUSER_ID NUMBER NOT NULL,
                                      STATUS NUMBER NOT NULL CONSTRAINT CHK_EXTERNALSTERILIZATIONLOT_STATUS CHECK(STATUS IN(0,1)),
                                      STERILIZATIONDATE DATE NOT NULL,
                                      SYSTEMUSER_ID NUMBER NOT NULL,
                                      TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN EXTERNALSTERILIZATIONLOT.STATUS IS '0 = Open
                                                       1 = Closed';
                                                      
ALTER TABLE EXTERNALSTERILIZATIONLOT
ADD CONSTRAINT XPKEXTERNALSTERILIZATIONLOT PRIMARY KEY(EXTERNALSTERILIZATIONLOT_ID);

CREATE UNIQUE INDEX XAK1EXTERNALSTERILIZATIONLOT ON EXTERNALSTERILIZATIONLOT
(LOTNUMBER ASC);

CREATE INDEX XIF1EXTERNALSTERILIZATIONLOT ON EXTERNALSTERILIZATIONLOT
(BUSINESSUNIT_ID ASC);

CREATE INDEX XIF2EXTERNALSTERILIZATIONLOT ON EXTERNALSTERILIZATIONLOT
(CREATIONUSER_ID ASC);

ALTER TABLE EXTERNALSTERILIZATIONLOT
ADD CONSTRAINT EXTERNALSTERILIZATIONLOT_BU FOREIGN KEY(BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE CASCADE;

ALTER TABLE EXTERNALSTERILIZATIONLOT
ADD CONSTRAINT EXTERNALSTERILIZATIONLOT_CU FOREIGN KEY(CREATIONUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID);

