--ABS-71814 Delivery frequency compliancy control at Contract Level/ABS-103232
---- CONTRACTATTRIBUTE--------
ALTER TABLE CONTRACTATTRIBUTE DROP CONSTRAINT GROUPNUMBER_VALID1_1;

ALTER TABLE CONTRACTATTRIBUTE ADD CONSTRAINT GROUPNUMBER_VALID1_1 CHECK (GROUPNUMBER BETWEEN 1 AND 18);

COMMENT ON COLUMN CONTRACTATTRIBUTE.GROUPNUMBER IS '1 = Miscellaneous
2 = Garments - Settings
3 = Garments – Locker Management
4 = Garments - Charges
5 = Garments – Wearer Functions
6 = Garments – Emblem Templates
7 = Invoicing
8 = Payment
9 = Residential
10 = Contract Product – Billing
11 = Contract Product – Pricing
12 = Contract Product – Logistics
13 = Contract Product – Miscellaneous
14 = Contract Product - Budget Protection
15 = Contract Product – ImageCare
16 = Emblem Template
17 = Wearer Functions
18 = Delivery';


--------------------------------CONTRACTPRODUCTDELFREQ----------------


CREATE TABLE CONTRACTPRODUCTDELFREQ
(
	CONTRACTPRODUCTDELFREQ_ID              NUMBER NOT NULL,
	CONTRACTPRODUCT_ID NUMBER NOT NULL, 
	DELIVERYFREQUENCY NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTRPROD_DELFREQ CHECK (DELIVERYFREQUENCY BETWEEN 1 AND 53),
	SYSTEMUSER_ID            NUMBER NOT NULL,
	TIMESTAMP                DATE NOT NULL 
);


ALTER TABLE CONTRACTPRODUCTDELFREQ ADD CONSTRAINT XPKCONTRACTPRODUCTDELFREQ PRIMARY KEY (CONTRACTPRODUCTDELFREQ_ID);

CREATE UNIQUE INDEX XAK1CONTRACTPRODUCTDELFREQ ON CONTRACTPRODUCTDELFREQ
(CONTRACTPRODUCT_ID   ASC,DELIVERYFREQUENCY   ASC);

CREATE INDEX XIF1CONTRACTPRODUCTDELFREQ ON CONTRACTPRODUCTDELFREQ (CONTRACTPRODUCT_ID ASC);

ALTER TABLE CONTRACTPRODUCTDELFREQ ADD CONSTRAINT FK_CONTRPRODDELFREQ_CONTRPROD FOREIGN KEY (CONTRACTPRODUCT_ID)
REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;


--------------------------------CONTRACTPRODUCTDELFREQCOMPL----------------


CREATE TABLE CONTRACTPRODUCTDELFREQCOMPL
(
	CONTRACTPRODUCT_ID NUMBER NOT NULL, 
	COMPLIANCYRULE       NUMBER NULL CONSTRAINT CHK_CONTRPRODDELFREQCOMP_COMP CHECK (COMPLIANCYRULE IN (0, 1, 2)),
	SYSTEMUSER_ID            NUMBER NOT NULL,
	TIMESTAMP                DATE NOT NULL 
);


COMMENT ON COLUMN CONTRACTPRODUCTDELFREQCOMPL.COMPLIANCYRULE IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced
No default (Null = no compliancy rule)
';
CREATE INDEX XIF1CONTRACTPRODUCTDELFREQCOMP ON CONTRACTPRODUCTDELFREQCOMPL(CONTRACTPRODUCT_ID ASC);

ALTER TABLE CONTRACTPRODUCTDELFREQCOMPL ADD CONSTRAINT FK_CONTRPRODDELFREQCOMP_CONTPR FOREIGN KEY (CONTRACTPRODUCT_ID)
REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;
