-- ABS-6175/ ABS-25150

---CLEANROOMBATCH table---
ALTER TABLE CLEANROOMBATCH ADD (REJECTIONREASONCODE_ID NUMBER NULL,
                                CHARACTERISTICSFILE VARCHAR2(100) NULL,
                                CREATIONUSER_ID NUMBER NOT NULL,
                                CLOSINGUSER_ID NUMBER NULL,
                                CLOSINGWORKSTATION_ID NUMBER NULL,
                                COMPLETIONUSER_ID NUMBER NULL,
                                COMPLETIONDATE DATE NULL,
                                COMPLETIONWORKSTATION_ID NUMBER NULL);

CREATE INDEX XIF5CLEANROOMBATCH ON CLEANROOMBATCH (REJECTIONREASONCODE_ID ASC);
CREATE INDEX XIF6CLEANROOMBATCH ON CLEANROOMBATCH (COMPLETIONWORKSTATION_ID ASC);
CREATE INDEX XIF7CLEANROOMBATCH ON CLEANROOMBATCH (CLOSINGWORKSTATION_ID ASC);
CREATE INDEX XIE1CLEANROOMBATCH ON CLEANROOMBATCH (CREATIONDATE ASC);
CREATE INDEX XIE2CLEANROOMBATCH ON CLEANROOMBATCH (BATCHSTATUS ASC);
 
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_REJRC FOREIGN KEY (REJECTIONREASONCODE_ID)
  REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CLOSINGWS FOREIGN KEY (CLOSINGWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CLOMPLETIONWS FOREIGN KEY (COMPLETIONWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;
