-- ABS-75637 / ABS-103247
--- CONTRACTATTRIBUTE ---
ALTER TABLE CONTRACTATTRIBUTE DROP CONSTRAINT GROUPNUMBER_VALID1_1;

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
19 = Contract Special Charge';

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


--- CONTRACTSPECIALCHARGE ---
CREATE SEQUENCE SEQ_CONTRACTSPECIALCHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTSPECIALCHARGE
(
CONTRACTSPECIALCHARGE_ID NUMBER NOT NULL,
CONTRACT_ID NUMBER NOT NULL,
SPECIALCHARGE_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTSPECIALCHARGE ADD CONSTRAINT XPKCONTRACTSPECIALCHARHE PRIMARY KEY (CONTRACTSPECIALCHARGE_ID);

CREATE UNIQUE INDEX XAK1CONTRACTSPECIALCHARGE ON CONTRACTSPECIALCHARGE (CONTRACT_ID ASC, SPECIALCHARGE_ID ASC);
CREATE INDEX XIF1CONTRACTSPECIALCHARGE ON CONTRACTSPECIALCHARGE (CONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTSPECIALCHARGE ON CONTRACTSPECIALCHARGE (SPECIALCHARGE_ID ASC);

ALTER TABLE CONTRACTSPECIALCHARGE ADD CONSTRAINT FK_CONTRACTSPECCHARGE_CONTRACT FOREIGN KEY (CONTRACT_ID)
REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTSPECIALCHARGE ADD CONSTRAINT FK_CONTRACTSPECCHARGE_SCHARGE FOREIGN KEY (SPECIALCHARGE_ID)
REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID);

--- CONTRACTSPECIALCHARGECUST ---
CREATE TABLE CONTRACTSPECIALCHARGECUST
(
CONTRACTSPECIALCHARGE_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
SPECIALCHARGE_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTSPECIALCHARGECUST ADD CONSTRAINT XPKCONTRACTSPECIALCHARGECUST PRIMARY KEY (CONTRACTSPECIALCHARGE_ID, CUSTOMER_ID, SPECIALCHARGE_ID);

CREATE INDEX XIF1CONTRACTSPECIALCHARGECUST ON CONTRACTSPECIALCHARGECUST (CONTRACTSPECIALCHARGE_ID ASC);
CREATE INDEX XIF2CONTRACTSPECIALCHARGECUST ON CONTRACTSPECIALCHARGECUST (CUSTOMER_ID ASC);
CREATE INDEX XIF3CONTRACTSPECIALCHARGECUST ON CONTRACTSPECIALCHARGECUST (SPECIALCHARGE_ID ASC);

ALTER TABLE CONTRACTSPECIALCHARGECUST ADD CONSTRAINT FK_CONTRSPECCHARCUST_CONSPCHAR FOREIGN KEY (CONTRACTSPECIALCHARGE_ID) REFERENCES CONTRACTSPECIALCHARGE (CONTRACTSPECIALCHARGE_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTSPECIALCHARGECUST ADD CONSTRAINT FK_CONTRSPECCHARCUST_CUSTSPCHA FOREIGN KEY (CUSTOMER_ID, SPECIALCHARGE_ID) REFERENCES CUSTOMERSPECIALCHARGE (CUSTOMER_ID, SPECIALCHARGE_ID) ON DELETE CASCADE;

--- CONTRACT ---
ALTER TABLE CONTRACT ADD (ALLOWNONCONTRACTSPECCHRG VARCHAR2(1) DEFAULT 'Y' NOT NULL
				CONSTRAINT CHK_CONTRACT_ALLOWNONSPECCHRG CHECK (ALLOWNONCONTRACTSPECCHRG IN ('Y','N')));
