-- ABS-36994/ ABS-67494

-- CUSTOMERUSERINSTRUCTIONEXCLPD TABLE ---
CREATE TABLE CUSTOMERUSERINSTRUCTIONEXCLPD
(
  CUSTOMERUSERINSTRUCTION_ID NUMBER NOT NULL,
  PLANTDEPARTMENT_ID         NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

ALTER TABLE CUSTOMERUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT XPKCUSTOMERUSERINSTREXCLPD PRIMARY KEY (CUSTOMERUSERINSTRUCTION_ID, PLANTDEPARTMENT_ID);

CREATE INDEX XIF1CUSTOMERUSERINSTREXCLPD ON CUSTOMERUSERINSTRUCTIONEXCLPD (CUSTOMERUSERINSTRUCTION_ID ASC);
CREATE INDEX XIF2CUSTOMERUSERINSTREXCLPD ON CUSTOMERUSERINSTRUCTIONEXCLPD (PLANTDEPARTMENT_ID ASC);

ALTER TABLE CUSTOMERUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT CUSTOMERUSERINSTREXCLPD_CUI FOREIGN KEY (CUSTOMERUSERINSTRUCTION_ID)
  REFERENCES CUSTOMERUSERINSTRUCTION (CUSTOMERUSERINSTRUCTION_ID) ON DELETE CASCADE;
  
ALTER TABLE CUSTOMERUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT CUSTOMERUSERINSTREXCLPD_PD FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE CASCADE;

-- GENERALUSERINSTRUCTIONEXCLPD TABLE ---
CREATE TABLE GENERALUSERINSTRUCTIONEXCLPD
(
  GENERALUSERINSTRUCTION_ID NUMBER NOT NULL,
  PLANTDEPARTMENT_ID         NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

ALTER TABLE GENERALUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT XPKGENERALUSERINSTREXCLPD PRIMARY KEY (GENERALUSERINSTRUCTION_ID, PLANTDEPARTMENT_ID);

CREATE INDEX XIF1GENERALUSERINSTREXCLPD ON GENERALUSERINSTRUCTIONEXCLPD (GENERALUSERINSTRUCTION_ID ASC);
CREATE INDEX XIF2GENERALUSERINSTREXCLPD ON GENERALUSERINSTRUCTIONEXCLPD (PLANTDEPARTMENT_ID ASC);

ALTER TABLE GENERALUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT GENERALUSERINSTREXCLPD_GUI FOREIGN KEY (GENERALUSERINSTRUCTION_ID)
  REFERENCES GENERALUSERINSTRUCTION (GENERALUSERINSTRUCTION_ID) ON DELETE CASCADE;
  
ALTER TABLE GENERALUSERINSTRUCTIONEXCLPD
  ADD CONSTRAINT GENERALUSERINSTREXCLPD_PD FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE CASCADE;