--- ABS-20922
---------------------------------   CUSTOMERBUDGETUNIT   -----------------------------
CREATE TABLE CUSTOMERBUDGETUNIT
(
CUSTOMER_ID NUMBER NOT NULL,
BUDGETUNIT_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CUSTOMERBUDGETUNIT ADD CONSTRAINT XPKCUSTOMERBUDGETUNIT PRIMARY KEY
(CUSTOMER_ID, BUDGETUNIT_ID);

CREATE INDEX XIF1CUSTOMERBUDGETUNIT ON CUSTOMERBUDGETUNIT (CUSTOMER_ID ASC);

CREATE INDEX XIF2CUSTOMERBUDGETUNIT ON CUSTOMERBUDGETUNIT (BUDGETUNIT_ID ASC);

ALTER TABLE CUSTOMERBUDGETUNIT ADD CONSTRAINT CUSTOMERBUDGETUNIT_CUSTID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERBUDGETUNIT ADD CONSTRAINT CUSTOMERBUDGETUNIT_BUDGID FOREIGN KEY (BUDGETUNIT_ID)
REFERENCES BUDGETUNIT (BUDGETUNIT_ID);

INSERT INTO CUSTOMERBUDGETUNIT (CUSTOMER_ID, BUDGETUNIT_ID, SYSTEMUSER_ID, TIMESTAMP)
SELECT X.CUSTOMER_ID, X.BUDGETUNIT_ID, GETMINSYSTEMUSER(), SYSDATE
FROM
(SELECT DISTINCT CB.CUSTOMER_ID, CB.BUDGETUNIT_ID 
FROM CUSTOMERBUDGETDEFINITION CB
WHERE CB.BUDGETUNIT_ID IS NOT NULL) X;
COMMIT;
