-- ABS-18381
-------------   INTERNALBUACTIVITIES   ---------------
CREATE SEQUENCE SEQ_INTERNALBUACTIVITIES
START WITH 1
INCREMENT BY 1;

CREATE TABLE INTERNALBUACTIVITIES(
  INTERNALBUACTIVITIES_ID NUMBER NOT NULL,
  BUSINESSUNIT_ID NUMBER NOT NULL,
  ACTIVITY_ID NUMBER NOT NULL,
  INTERNALPROCESSINGBU_ID NUMBER NOT NULL,
  DEFAULTVALUE VARCHAR2(1) DEFAULT 'N' NOT NULL
    CONSTRAINT YN_VALIDATIONFIELD306 CHECK (DEFAULTVALUE IN ('Y','N')),
  FIXED VARCHAR2(1) DEFAULT 'N' NOT NULL
    CONSTRAINT YN_VALIDATIONFIELD307 CHECK (FIXED IN ('Y','N')),
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL
);

ALTER TABLE INTERNALBUACTIVITIES ADD CONSTRAINT XPKINTERNALBUACTIVITIES
PRIMARY KEY (INTERNALBUACTIVITIES_ID);

CREATE UNIQUE INDEX XAK1INTERNALBUACTIVITIES ON INTERNALBUACTIVITIES
(BUSINESSUNIT_ID ASC, ACTIVITY_ID ASC, INTERNALPROCESSINGBU_ID ASC);

CREATE INDEX XIF1INTERNALBUACTIVITIES ON INTERNALBUACTIVITIES (BUSINESSUNIT_ID ASC);

CREATE INDEX XIF2INTERNALBUACTIVITIES ON INTERNALBUACTIVITIES (ACTIVITY_ID ASC);

CREATE INDEX XIF3INTERNALBUACTIVITIES ON INTERNALBUACTIVITIES (INTERNALPROCESSINGBU_ID ASC);

ALTER TABLE INTERNALBUACTIVITIES ADD CONSTRAINT INTERNALBUACTIVITIES_BUID
FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID)
ON DELETE CASCADE;

ALTER TABLE INTERNALBUACTIVITIES ADD CONSTRAINT INTERNALBUACTIVITIES_ACTIVID
FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITY (ACTIVITY_ID)
ON DELETE CASCADE;

ALTER TABLE INTERNALBUACTIVITIES ADD CONSTRAINT INTERNALBUACTIVITIES_INTBUID
FOREIGN KEY (INTERNALPROCESSINGBU_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

INSERT INTO INTERNALBUACTIVITIES
  (INTERNALBUACTIVITIES_ID,
   BUSINESSUNIT_ID,
   ACTIVITY_ID,
   INTERNALPROCESSINGBU_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
  (SELECT SEQ_INTERNALBUACTIVITIES.NEXTVAL,
          BUA.BUSINESSUNIT_ID,
          BUA.ACTIVITY_ID,
          INTBU.BUSINESSUNIT_ID,
          GETMINSYSTEMUSER(),
          SYSDATE
     FROM BUSINESSUNITACTIVITIES BUA, BUSINESSUNIT INTBU
    WHERE EXISTS
    (SELECT 1
             FROM CUSTOMERACTIVITIES CA, CUSTOMER C
            WHERE CA.ACTIVITY_ID = BUA.ACTIVITY_ID
              AND CA.CUSTOMER_ID = C.CUSTOMER_ID
              AND CA.PROCESSBUSINESSUNIT_ID = BUA.BUSINESSUNIT_ID
              AND CA.INTERNALPROCESSINGBU_ID = INTBU.BUSINESSUNIT_ID
              AND C.ACTIVE = 'Y'));
COMMIT;


