-- ABS-5747


-----------------------MASTERAGREEMENT-------------------------
CREATE SEQUENCE SEQ_MASTERAGREEMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE MASTERAGREEMENT
	(MASTERAGREEMENT_ID NUMBER  NOT NULL,
	CODE VARCHAR2(15) NOT NULL,
	DESCRIPTION VARCHAR2(40) NOT NULL,
	USERGROUP_ID NUMBER NULL,
	CUSTOMER_LINK_ID NUMBER NULL,
	OWNER VARCHAR2(40) NULL,
	ADDRESS_ID NUMBER NULL,
	COUNTRY_ID NUMBER NULL,
	PHONENUMBER VARCHAR2(15) NULL,
	EMAIL VARCHAR2(300) NULL,
	SIGNDATE DATE NOT NULL,
	STARTDATE DATE NOT NULL,
	ENDDATE DATE NOT NULL,
	RENEGOTIATIONDATE DATE NULL,
	DURATION NUMBER DEFAULT 48 NOT NULL CONSTRAINT DURATION_valid CHECK (DURATION BETWEEN 1 AND 99),
	TERMOFNOTICE NUMBER DEFAULT 3 NOT NULL,
	TERMINATIONDATE DATE NULL,
	AUTEXTENSIONPERIOD NUMBER DEFAULT 12 NOT NULL CONSTRAINT AUTEXTENSIONPERIOD_valid CHECK (AUTEXTENSIONPERIOD BETWEEN 0 AND 99),
	REMARK CLOB NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL);


COMMENT ON COLUMN MASTERAGREEMENT.ENDDATE IS 'valid date >= StartDate. Default Start Date + duration. Updated via a scheduled process if the contract is extended.
';

COMMENT ON COLUMN MASTERAGREEMENT.TERMOFNOTICE IS 'Number of months, between 0 and Duration. Default 3. Number of months before the end of contract that the contract cannot be terminated anymore, then it is automatically extended.
';

COMMENT ON COLUMN MASTERAGREEMENT.TERMINATIONDATE IS 'valid date >= Sign Date
';

COMMENT ON COLUMN MASTERAGREEMENT.AUTEXTENSIONPERIOD IS 'Number of months the contract is extended after it is expired. 0 means it is not automatically extended
';

ALTER TABLE MASTERAGREEMENT
ADD CONSTRAINT  XPKMASTERAGREEMENT  PRIMARY KEY (MASTERAGREEMENT_ID);

CREATE UNIQUE INDEX XAK1MASTERAGREEMENT ON MASTERAGREEMENT
(CODE ASC);

CREATE INDEX XIE1MASTERAGREEMENT ON MASTERAGREEMENT
(OWNER ASC);

CREATE INDEX XIF1MASTERAGREEMENT ON MASTERAGREEMENT
(USERGROUP_ID   ASC);

CREATE INDEX XIF2MASTERAGREEMENT ON MASTERAGREEMENT
(CUSTOMER_LINK_ID   ASC);

CREATE INDEX XIF3MASTERAGREEMENT ON MASTERAGREEMENT
(ADDRESS_ID   ASC);

CREATE INDEX XIF4MASTERAGREEMENT ON MASTERAGREEMENT
(COUNTRY_ID   ASC);

ALTER TABLE MASTERAGREEMENT
ADD CONSTRAINT MASTERAGREEMENT_USERGROUP FOREIGN KEY (USERGROUP_ID) REFERENCES USERGROUP(USERGROUP_ID);

ALTER TABLE MASTERAGREEMENT
ADD CONSTRAINT MASTERAGREEMENT_CUSTOMER_LINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID);

ALTER TABLE MASTERAGREEMENT
ADD CONSTRAINT MASTERAGREEMENT_ADDRESS FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID);

ALTER TABLE MASTERAGREEMENT
ADD CONSTRAINT MASTERAGREEMENT_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY(COUNTRY_ID);


-----------------------MASTERAGREEMENT_HISTORY-------------------------

CREATE TABLE MASTERAGREEMENT_HISTORY
	(MASTERAGREEMENT_ID NUMBER  NOT NULL,
	CODE VARCHAR2(15) NOT NULL,
	DESCRIPTION VARCHAR2(40) NOT NULL,
	USERGROUP_ID NUMBER NULL,
	CUSTOMER_LINK_ID NUMBER NULL,
	OWNER VARCHAR2(40) NULL,
	ADDRESS_ID NUMBER NULL,
	COUNTRY_ID NUMBER NULL,
	PHONENUMBER VARCHAR2(15) NULL,
	EMAIL VARCHAR2(300) NULL,
	SIGNDATE DATE NOT NULL,
	STARTDATE DATE NOT NULL,
	ENDDATE DATE NOT NULL,
	RENEGOTIATIONDATE DATE NULL,
	DURATION NUMBER DEFAULT 48 NOT NULL CONSTRAINT DURATION_valid1 CHECK (DURATION BETWEEN 1 AND 99),
	TERMOFNOTICE NUMBER DEFAULT 3 NOT NULL,
	TERMINATIONDATE DATE NULL,
	AUTEXTENSIONPERIOD NUMBER DEFAULT 12 NOT NULL CONSTRAINT AUTEXTENSIONPERIOD_valid2 CHECK (AUTEXTENSIONPERIOD BETWEEN 0 AND 99),
	REMARK CLOB NULL,
	TRANSACTIONNUMBER NUMBER NOT NULL,
	TRANSACTIONTYPE_ID NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
	);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT  XPKMASTERAGREEMENT_HISTORY  PRIMARY KEY (MASTERAGREEMENT_ID, TRANSACTIONNUMBER);

CREATE INDEX XIE1MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(MASTERAGREEMENT_ID ASC, TIMESTAMP ASC);

CREATE INDEX XIF1MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(MASTERAGREEMENT_ID   ASC);

CREATE INDEX XIF2MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(USERGROUP_ID   ASC);

CREATE INDEX XIF3MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(CUSTOMER_LINK_ID   ASC);

CREATE INDEX XIF4MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(ADDRESS_ID   ASC);

CREATE  INDEX XIF5MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(COUNTRY_ID   ASC);

CREATE INDEX XIF6MASTERAGREEMENT_HISTORY ON MASTERAGREEMENT_HISTORY
(TRANSACTIONTYPE_ID   ASC);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_USERGROUP FOREIGN KEY (USERGROUP_ID) REFERENCES USERGROUP(USERGROUP_ID);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_CUSTOMER_LINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_ADDRESS FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY(COUNTRY_ID);

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_MASTERAG FOREIGN KEY (MASTERAGREEMENT_ID) REFERENCES MASTERAGREEMENT(MASTERAGREEMENT_ID) ON DELETE CASCADE;

ALTER TABLE MASTERAGREEMENT_HISTORY
ADD CONSTRAINT MASTERAG_HIST_TRANSACTTY FOREIGN KEY (TRANSACTIONTYPE_ID) REFERENCES TRANSACTIONTYPE(TRANSACTIONTYPE_ID);
