-- ABS-71967 / ABS-82193
--- PLPCCHARGEPERCENTAGE ---
CREATE TABLE PLPCCHARGEPERCENTAGE
(
PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
LOSSCHARGEPERCENTAGE NUMBER DEFAULT 100 NOT NULL
  CONSTRAINT CHK_PLPCCHARGEPERCENTAGE_LOSS CHECK (LOSSCHARGEPERCENTAGE BETWEEN 0.00 AND 999.99),
SRCHARGEPERCENTAGE NUMBER DEFAULT 100 NOT NULL
  CONSTRAINT CHK_PLPCCHARGEPERCENTAGE_SRC CHECK (SRCHARGEPERCENTAGE BETWEEN 0.00 AND 999.99),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE PLPCCHARGEPERCENTAGE ADD CONSTRAINT XPKPLPCCHARGEPERCENTAGE PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID);

ALTER TABLE PLPCCHARGEPERCENTAGE ADD CONSTRAINT FK_PLPCCHARGEPERCENTAGE_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

--- GENERALREPLACEMENTPRICE ---
ALTER TABLE GENERALREPLACEMENTPRICE ADD (LOCKEDFORINTERFACE NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT CHK_GENERALREPLPRICE_LOCKED CHECK (LOCKEDFORINTERFACE IN (0,1)));

COMMENT ON COLUMN GENERALREPLACEMENTPRICE.LOCKEDFORINTERFACE IS '0 = No
1 = Yes';

--- CONTRACTPRODUCTPRICING ---
CREATE TABLE CONTRACTPRODUCTPRICING
(
CONTRACTPRODUCT_ID NUMBER NOT NULL,
LOSSCHARGEPERCENTAGE NUMBER DEFAULT 100 NOT NULL
	CONSTRAINT CHK_CONTRACTPRODPRICE_LOSS CHECK (LOSSCHARGEPERCENTAGE BETWEEN 0.00 AND 999.99),
SRCHARGEPERCENTAGE NUMBER DEFAULT 100 NOT NULL
	CONSTRAINT CHK_CONTRACTPRODPRICE_SRC CHECK (SRCHARGEPERCENTAGE BETWEEN 0.00 AND 999.99),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTPRODUCTPRICING ADD CONSTRAINT XPKCONTRACTPRODUCTPRICING PRIMARY KEY (CONTRACTPRODUCT_ID);

ALTER TABLE CONTRACTPRODUCTPRICING ADD CONSTRAINT FK_CONTRACTPRODPRICE_CONPRO FOREIGN KEY (CONTRACTPRODUCT_ID)
REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

