--ABS-159416

--CONTRACTCOMPONENTGRMSETTINGS TABLE

ALTER TABLE CONTRACTCOMPONENTGRMSETTINGS ADD (WEARERINVENTORYCONTROL NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CTRCTCMPNTGRMSTTNGS_WEARINVNTRCTRL CHECK (WEARERINVENTORYCONTROL IN (0,1)));

COMMENT ON COLUMN CONTRACTCOMPONENTGRMSETTINGS.WEARERINVENTORYCONTROL IS '0 = none , 1 = Inventory Controlled Only used for module WEARERINVENTORYCONTROL';

--CONTRACTATRRIBUTE TABLE 

ALTER TABLE CONTRACTATTRIBUTE DROP CONSTRAINT CHK_CONTRACTATT_GROUPNB;

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

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  22 = Wearer Inventory Control';

--CONTRACTPRODUCTWIC

CREATE TABLE CONTRACTPRODUCTWIC (
       CONTRACTPRODUCT_ID NUMBER NOT NULL,
       CONTROLMAXINVENTORY NUMBER DEFAULT 0 NOT NULL,
       CONTROLCIRCINVENTORYPERC NUMBER DEFAULT 0 NOT NULL,
       COMPLIANCYRULE NUMBER NULL CONSTRAINT CHK_CONTRACTPRODUCTWIC_COMPLIANCYRULE CHECK (COMPLIANCYRULE IN (0,1,2)),
       SYSTEMUSER_ID NUMBER NOT NULL,
       TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN CONTRACTPRODUCTWIC.CONTROLMAXINVENTORY IS 'Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN CONTRACTPRODUCTWIC.CONTROLCIRCINVENTORYPERC IS 'Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN CONTRACTPRODUCTWIC.COMPLIANCYRULE IS '0 = Contract suggested, can be overruled 1 = Contract enforced, can only be overruled by maintenance user 2 = Contract enforced';

ALTER TABLE CONTRACTPRODUCTWIC ADD CONSTRAINT XPK_CONTRACTPRODUCTWIC PRIMARY KEY (CONTRACTPRODUCT_ID);

--CUSTOMEREXTRAINFO TABLE

ALTER TABLE CUSTOMEREXTRAINFO ADD (WEARERINVENTORYCONTROL NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CUSTOMEREXTRAINFO_WEARERINVENTORYCONTROL CHECK (WEARERINVENTORYCONTROL IN (0,1)));

COMMENT ON COLUMN CUSTOMEREXTRAINFO.WEARERINVENTORYCONTROL IS '0 = none , 1 = Inventory Controlled Only used for module WEARERINVENTORYCONTROL';

--WEARERINVENTORYMOVEMENT TABLE

ALTER TABLE WEARERINVENTORYMOVEMENT ADD (
      WEEKLYRENTCHANGE NUMBER DEFAULT 0 NOT NULL,
      WEARERINVENTORYCONTROLTYPE NUMBER DEFAULT 0 NOT NULL,
      CONTROLMAXINVENTORY NUMBER DEFAULT 0 NOT NULL,
      CONTROLCIRCINVENTORYPERC NUMBER DEFAULT 0 NOT NULL,
      GARMENTSFLAGGEDFORWICRAG NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.WEEKLYRENTCHANGE IS 'Calculated value of the weekly invoice value change Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.WEARERINVENTORYCONTROLTYPE IS '0 = None 1 = Inventory Control Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.CONTROLMAXINVENTORY IS 'The value of ControlMax when this changed occured Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.CONTROLCIRCINVENTORYPERC IS 'The value of ControlCirc when this changed occured Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN WEARERINVENTORYMOVEMENT.GARMENTSFLAGGEDFORWICRAG IS 'The number of garments for this WI that are flagged with the automatic flag (44) for wearer inventory control ragging Only used for module WEARERINVENTORYCONTROL';

--PLPCEXTRAINFO TABLE

ALTER TABLE PLPCEXTRAINFO ADD (
      CONTROLMAXINVENTORY NUMBER DEFAULT 0 NOT NULL,
      CONTROLCIRCINVENTORYPERC NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PLPCEXTRAINFO.CONTROLMAXINVENTORY IS 'Only used for module WEARERINVENTORYCONTROL';
COMMENT ON COLUMN PLPCEXTRAINFO.CONTROLCIRCINVENTORYPERC IS 'Only used for module WEARERINVENTORYCONTROL';
