-- ABS-113635/ ABS-114039

--------- CONTRACTPRODUCTBM6 TABLE -------------
CREATE TABLE CONTRACTPRODUCTBM6
(
  CONTRACTPRODUCT_ID    NUMBER NOT NULL,
  ONEOFFPRICEMULTIPLIER NUMBER(5,2) NULL
                        CONSTRAINT CHK_CONTRACTPRODUCTBM6_ONEOFF CHECK (ONEOFFPRICEMULTIPLIER > 0),
  INVENTORYMAINTPERC    NUMBER(5,2) NULL,
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL
)
;

ALTER TABLE CONTRACTPRODUCTBM6
  ADD CONSTRAINT XPKCONTRACTPRODUCTBM6 PRIMARY KEY (CONTRACTPRODUCT_ID);
  
ALTER TABLE CONTRACTPRODUCTBM6
  ADD CONSTRAINT FK_CONTRACTPRODUCTBM6_CP FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

---------- CONTRACTPRODUCTBM6DELDAYS TABLE ----------
CREATE TABLE CONTRACTPRODUCTBM6DELDAYS
(
  CONTRACTPRODUCT_ID      NUMBER NOT NULL,
  DELIVERIESPERWEEK       NUMBER NOT NULL CONSTRAINT CHK_CPBM6DELDAYS_DELPERWEEKS
                          CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
  CIRCINVENTORYMULTIPLIER NUMBER(5,2) NULL CONSTRAINT CHK_CPBM6DELDAYS_CIRCINVM
                          CHECK (CIRCINVENTORYMULTIPLIER > 0),
  LOADPERCENTPERDAY       NUMBER(5,2) NULL CONSTRAINT CHK_CPBM6DELDAYS_LOADPERCDAY
                          CHECK (LOADPERCENTPERDAY > 0),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;
  
ALTER TABLE CONTRACTPRODUCTBM6DELDAYS
  ADD CONSTRAINT XPKCONTRACTPRODUCTBM6DELDAYS PRIMARY KEY (CONTRACTPRODUCT_ID, DELIVERIESPERWEEK);

CREATE INDEX XIF1CONTRACTPRODUCTBM6DELDAYS ON CONTRACTPRODUCTBM6DELDAYS (CONTRACTPRODUCT_ID ASC);

ALTER TABLE CONTRACTPRODUCTBM6DELDAYS
  ADD CONSTRAINT FK_CPBM6DELDAYS_CPBM6 FOREIGN KEY (CONTRACTPRODUCT_ID)
  REFERENCES CONTRACTPRODUCTBM6 (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

----------- CONTRACTPRODUCTBILLING table -------------- 
COMMENT ON COLUMN CONTRACTPRODUCTBILLING.BILLINGMETHOD
  IS '1 = Flat rated rent 2 = Rent based on minimum 3 = Circulating Inventory 4 = Piece rate only 5 = Lease 6 = Rent Based on Usage';

ALTER TABLE CONTRACTPRODUCTBILLING
  DROP CONSTRAINT BILLINGMETHOD_VALID1_1;
  
ALTER TABLE CONTRACTPRODUCTBILLING
  ADD CONSTRAINT BILLINGMETHOD_VALID1_1
  CHECK (BILLINGMETHOD BETWEEN 1 AND 6);

----------- 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
16 = Emblem Template
17 = Wearer Functions
18 = Delivery
19 = Contract Special Charge
20 = Non-Garments – Charges
21 = Contract Product – Billing Method 6';

ALTER TABLE CONTRACTATTRIBUTE
  DROP CONSTRAINT CHK_CONTRACTATT_GROUPNB;
  
ALTER TABLE CONTRACTATTRIBUTE
  ADD CONSTRAINT CHK_CONTRACTATT_GROUPNB
  CHECK (GROUPNUMBER BETWEEN 1 AND 21);