--ABS-85753 / ABS-93024

---------------- PLANTDEPTPERCUSTACT ----------

CREATE SEQUENCE SEQ_PLANTDEPTPERCUSTACT
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTDEPTPERCUSTACT (PLANTDEPTPERCUSTACT_ID NUMBER NOT NULL,
                                  CUSTOMER_ID NUMBER NOT NULL,
                                  ACTIVITY_ID NUMBER NOT NULL,
                                  PROCESSBUSINESSUNIT_ID NUMBER NOT NULL,
                                  INTERNALPROCESSINGBU_ID NUMBER NOT NULL,
                                  PLANTDEPARTMENT_ID NUMBER NOT NULL,
                                  SYSTEMUSER_ID NUMBER NOT NULL,
                                  TIMESTAMP DATE NOT NULL);
ALTER TABLE PLANTDEPTPERCUSTACT
ADD CONSTRAINT XPKPLANTDEPTPERCUSTACT PRIMARY KEY (PLANTDEPTPERCUSTACT_ID);

CREATE UNIQUE INDEX XAK1PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (CUSTOMER_ID ASC, ACTIVITY_ID ASC, PROCESSBUSINESSUNIT_ID ASC, INTERNALPROCESSINGBU_ID ASC);

CREATE INDEX XIF1PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (CUSTOMER_ID ASC);
CREATE INDEX XIF2PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (ACTIVITY_ID ASC);
CREATE INDEX XIF3PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (PROCESSBUSINESSUNIT_ID ASC);
CREATE INDEX XIF4PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (INTERNALPROCESSINGBU_ID ASC);
CREATE INDEX XIF5PLANTDEPTPERCUSTACT ON PLANTDEPTPERCUSTACT (PLANTDEPARTMENT_ID ASC);

ALTER TABLE PLANTDEPTPERCUSTACT
  ADD CONSTRAINT FK_PLANTDEPTPERCUSTACT_CUST_ID FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTDEPTPERCUSTACT
  ADD CONSTRAINT FK_PLANTDEPTPERCUSTACT_ACTIVIT FOREIGN KEY (ACTIVITY_ID)
  REFERENCES ACTIVITY (ACTIVITY_ID) ON DELETE CASCADE;

ALTER TABLE PLANTDEPTPERCUSTACT
  ADD CONSTRAINT FK_PLANTDEPTPERCUSTACT_PROCBU FOREIGN KEY (PROCESSBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

ALTER TABLE PLANTDEPTPERCUSTACT
  ADD CONSTRAINT FK_PLANTDEPTPERCUSTACT_INTPRBU FOREIGN KEY (INTERNALPROCESSINGBU_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

ALTER TABLE PLANTDEPTPERCUSTACT
  ADD CONSTRAINT FK_PLANTDEPTPERCUSTACT_PLDEPT FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE CASCADE;
  
  
---------------- PLANTDEPTPERCUSTACTDEPT ---------------

CREATE SEQUENCE SEQ_PLANTDEPTPERCUSTACTDEPT
START WITH 1
INCREMENT BY 1;

CREATE TABLE PLANTDEPTPERCUSTACTDEPT (PLANTDEPTPERCUSTACTDEPT_ID NUMBER NOT NULL,
                                      PLANTDEPTPERCUSTACT_ID NUMBER NOT NULL,
                                      DEPARTMENT_ID NUMBER NOT NULL,
                                      PLANTDEPARTMENT_ID NUMBER NOT NULL,
                                      SYSTEMUSER_ID NUMBER NOT NULL,
                                      TIMESTAMP DATE NOT NULL);
ALTER TABLE PLANTDEPTPERCUSTACTDEPT
ADD CONSTRAINT XPKPLANTDEPTPERCUSTACTDEPT PRIMARY KEY (PLANTDEPTPERCUSTACTDEPT_ID);

CREATE UNIQUE INDEX XAK1PLANTDEPTPERCUSTACTDEPT ON PLANTDEPTPERCUSTACTDEPT (PLANTDEPTPERCUSTACT_ID ASC, DEPARTMENT_ID ASC);

CREATE INDEX XIF1PLANTDEPTPERCUSTACTDEPT ON PLANTDEPTPERCUSTACTDEPT (PLANTDEPTPERCUSTACT_ID ASC);
CREATE INDEX XIF2PLANTDEPTPERCUSTACTDEPT ON PLANTDEPTPERCUSTACTDEPT (DEPARTMENT_ID ASC);
CREATE INDEX XIF3PLANTDEPTPERCUSTACTDEPT ON PLANTDEPTPERCUSTACTDEPT (PLANTDEPARTMENT_ID ASC);

ALTER TABLE PLANTDEPTPERCUSTACTDEPT
  ADD CONSTRAINT FK_PLDEPPERCUSTACTDEP_PLDEPCAC FOREIGN KEY (PLANTDEPTPERCUSTACT_ID)
  REFERENCES PLANTDEPTPERCUSTACT (PLANTDEPTPERCUSTACT_ID) ON DELETE CASCADE;
  
ALTER TABLE PLANTDEPTPERCUSTACTDEPT
  ADD CONSTRAINT FK_PLDEPPERCUSTACTDEP_DEPART FOREIGN KEY (DEPARTMENT_ID)
  REFERENCES DEPARTMENT (DEPARTMENT_ID) ON DELETE CASCADE;

ALTER TABLE PLANTDEPTPERCUSTACTDEPT
  ADD CONSTRAINT FK_PLDEPPERCUSTACTDEP_PLANTDEP FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE CASCADE;