-----ABS-77956-----

---CONTRACTSPERCUSTOMER---
ALTER TABLE CONTRACTSPERCUSTOMER 
ADD (PRICEINDEX_ID NUMBER NULL, 
     PRICEUPDATEMETHOD NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTRACTSPERCUSTOMER_PRICEUPDATEMETHOD CHECK (PRICEUPDATEMETHOD IN (0,1,2) ));

COMMENT ON COLUMN CONTRACTSPERCUSTOMER.PRICEUPDATEMETHOD IS '0 = Use contract percentages 1 = Use Consumer Price Index 2 = Use Price Index';

CREATE INDEX XIF9CONTRACTSPERCUSTOMER ON CONTRACTSPERCUSTOMER (PRICEINDEX_ID);

UPDATE CONTRACTSPERCUSTOMER SET PRICEUPDATEMETHOD = 1 WHERE CPIBASED = 'Y';
COMMIT;

ALTER TABLE CONTRACTSPERCUSTOMER
ADD CONSTRAINT FK_CONTRACTSPERCUSTOMER_PRICEINDEXID FOREIGN KEY(PRICEINDEX_ID)
REFERENCES PRICEINDEX (PRICEINDEX_ID);

ALTER TABLE CONTRACTSPERCUSTOMER
ADD CONSTRAINT CHK_CONTRACTSPERCUSTOMER_CPIBASED_PUM
CHECK ( (CPIBASED IN ('Y')  AND PRICEUPDATEMETHOD IN (1) ) OR ( CPIBASED IN ('N') AND PRICEUPDATEMETHOD <> 1) OR ( PRICEUPDATEMETHOD IN (2) AND PRICEINDEX_ID IS NOT NULL ));


----PRICEINDEX----

ALTER TABLE PRICEINDEXHISTORY
ADD (PERCWEARER NUMBER(8,4) DEFAULT 0.0000 NOT NULL,
     PERCWEARERINV NUMBER(8,4) DEFAULT 0.0000 NOT NULL, 
     PERCSPECIALSIZECHARGE NUMBER(8,4) DEFAULT 0.0000 NOT NULL);
