---ABS-133154---

--- USERINSTRUCTIONTYPE ---

CREATE SEQUENCE SEQ_USERINSTRUCTIONTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE USERINSTRUCTIONTYPE
( USERINSTRUCTIONTYPE_ID NUMBER NOT NULL,
  CODE VARCHAR2(12) NOT NULL,
  DESCRIPTION VARCHAR2(40) NOT NULL, 
  TIMESTAMP DATE NOT NULL, 
  SYSTEMUSER_ID NUMBER NOT NULL);

ALTER TABLE USERINSTRUCTIONTYPE
ADD CONSTRAINT XPK_USERINSTRUCTIONTYPE PRIMARY KEY (USERINSTRUCTIONTYPE_ID);

--- CUSTOMERUSERINSTRUCTION ---
ALTER TABLE CUSTOMERUSERINSTRUCTION
ADD (USERINSTRUCTIONTYPE_ID NUMBER NULL);

CREATE INDEX XIF3CUSTOMERUSERINSTRUCTION ON CUSTOMERUSERINSTRUCTION (USERINSTRUCTIONTYPE_ID ASC);

ALTER TABLE CUSTOMERUSERINSTRUCTION
ADD CONSTRAINT CUSTOMERUSERINSTRUCTION_USERINSTRUCTIONTYPE_ID FOREIGN KEY (USERINSTRUCTIONTYPE_ID)
REFERENCES USERINSTRUCTIONTYPE (USERINSTRUCTIONTYPE_ID) ON DELETE SET NULL;

--- CLEANROOMBATCHSTEP ---

CREATE SEQUENCE SEQ_CLEANROOMBATCHSTEP
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHSTEP
( CLEANROOMSTEP_ID NUMBER NOT NULL, 
  PLANT_ID NUMBER NOT NULL, 
  STEP NUMBER NOT NULL, 
  DESCRIPTION VARCHAR2(30) NOT NULL,
  USERINSTRUCTIONTYPE_ID NUMBER NULL, 
  TIMESTAMP DATE NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL);

ALTER TABLE CLEANROOMBATCHSTEP
ADD CONSTRAINT XPK_CLEANROOMSTEP_ID PRIMARY KEY (CLEANROOMSTEP_ID);

CREATE INDEX XFK1CLEANROOMBATCHSTEP ON CLEANROOMBATCHSTEP (PLANT_ID ASC);

CREATE INDEX XFK2CLEANROOMBATCHSTEP ON CLEANROOMBATCHSTEP (USERINSTRUCTIONTYPE_ID ASC);

ALTER TABLE CLEANROOMBATCHSTEP
ADD CONSTRAINT CLEANROOMBATCHSTEP_PLANT_ID FOREIGN KEY (PLANT_ID) 
REFERENCES PLANT (PLANT_ID); 

ALTER TABLE CLEANROOMBATCHSTEP
ADD CONSTRAINT CLEANROOMBATCHSTEP_USERINSTRUCTIONTYPE_ID FOREIGN KEY (USERINSTRUCTIONTYPE_ID) 
REFERENCES USERINSTRUCTIONTYPE (USERINSTRUCTIONTYPE_ID) ON DELETE SET NULL;
