-- ABS-96741 / ABS-99112
CREATE TABLE CONTRACTUSERGROUP
(
CONTRACT_ID NUMBER NOT NULL,
USERGROUP_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTUSERGROUP ADD CONSTRAINT XPKCONTRACTUSERGROUP PRIMARY KEY (CONTRACT_ID, USERGROUP_ID);

CREATE INDEX XIF1CONTRACTUSERGROUP ON CONTRACTUSERGROUP (CONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTUSERGROUP ON CONTRACTUSERGROUP (USERGROUP_ID ASC);

ALTER TABLE CONTRACTUSERGROUP ADD CONSTRAINT FK_CONTRACTUG_CONTRACTID FOREIGN KEY (CONTRACT_ID)
REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTUSERGROUP ADD CONSTRAINT FK_CONTRACTUG_USERGROUPID FOREIGN KEY (USERGROUP_ID)
REFERENCES USERGROUP (USERGROUP_ID) ON DELETE CASCADE;

INSERT INTO CONTRACTUSERGROUP
  (CONTRACT_ID, USERGROUP_ID, SYSTEMUSER_ID, TIMESTAMP)
  SELECT CONTRACT_ID, USERGROUP_ID, GETMINSYSTEMUSER(), SYSDATE
    FROM CONTRACT
   WHERE CONTRACT.USERGROUP_ID IS NOT NULL;
COMMIT;