-- ABS-58152/ ABS-59222

-- CONTRACTATTRIBUTE table --
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';

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

INSERT INTO CONTRACTATTRIBUTE
  (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, NOVALUERULE)
VALUES
  (174, 15, 0, 'ImageCare', 'ContractProductImageCare', GETMINSYSTEMUSER(), sysdate, 0);
COMMIT;

INSERT INTO CONTRACTATTRIBUTE
  (CONTRACTATTRIBUTE_ID, GROUPNUMBER, SEQUENCENUMBER, FACTORYNAME, INTERNALNAME, SYSTEMUSER_ID, TIMESTAMP, NOVALUERULE)
VALUES
  (175, 15, 1, 'PLPCImageCare', 'plpcImageCareField', GETMINSYSTEMUSER(), sysdate, 0);
COMMIT;

-- CONTRACTPRODUCTADDBILLING table --
ALTER TABLE CONTRACTPRODUCTADDBILLING DROP COLUMN IMAGECARE;

-- CONTRACTPRODUCTIMAGECARE table --
CREATE TABLE CONTRACTPRODUCTIMAGECARE
(
  CONTRACTPRODUCT_ID NUMBER NOT NULL,
  COMPLIANCYRULE     NUMBER NULL CONSTRAINT COMPLIANCYRULE_VALID2_1
                     CHECK (COMPLIANCYRULE IN (0, 1, 2)),
  IMAGECARE          NUMBER NULL CONSTRAINT IMAGECARE_VALID1_2
                     CHECK (IMAGECARE BETWEEN 0 AND 7),
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
)
;

COMMENT ON COLUMN CONTRACTPRODUCTIMAGECARE.COMPLIANCYRULE
  IS '0 = Contract suggested, can be overruled 
1 = Contract enforced, can only be overruled by maintenance user
2 = Contract enforced
(Null = no compliancy rule)';

COMMENT ON COLUMN CONTRACTPRODUCTIMAGECARE.IMAGECARE
  IS '0 = Not charged
1 = For repairs
2 = For Loss Charges
3 = For Repairs and Loss Charges
4 = For Residual Value
5 = For Repair and Residual Value
6 = For Residual Value and Loss Charge
7 = For Repairs and Loss Charges and Residual Value';

ALTER TABLE CONTRACTPRODUCTIMAGECARE
  ADD CONSTRAINT XPKCONTRACTPRODUCTIMAGECARE PRIMARY KEY (CONTRACTPRODUCT_ID);

ALTER TABLE CONTRACTPRODUCTIMAGECARE
  ADD CONSTRAINT CONTRACTPRODUCTIMAGECARE_CP FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;