--Order 20010903.40


-------------PRODUCTLISTPERCUSTOMER--------------

ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (SPECIFICWEIGHINGCATEGORY_ID NUMBER NULL);



ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (COSTPRICEINCLWASHSALESPRICE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CostPriceInclWashSales_valid1 CHECK (COSTPRICEINCLWASHSALESPRICE IN (0, 1)));



ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (COSTPRICEINCLRENTPRICE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CostPriceInclRentPrice_valid1 CHECK (COSTPRICEINCLRENTPRICE IN (0, 1)));



ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (COSTPRICEINCLWEIGHTPRICE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CostPrInclWeightPrice_valid1 CHECK (COSTPRICEINCLWEIGHTPRICE IN (0, 1)));



ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (COSTPRICEINCLCOSTREPORTPRICE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT CostPrInclCstRptPrice_valid1 CHECK (COSTPRICEINCLCOSTREPORTPRICE IN (0, 1)));



COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.COSTPRICEINCLWASHSALESPRICE IS '0 = No
1 = Yes';



COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.COSTPRICEINCLRENTPRICE IS '0 = No
1 = Yes';



COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.COSTPRICEINCLWEIGHTPRICE IS '0 = No
1 = Yes';



COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.COSTPRICEINCLCOSTREPORTPRICE IS '0 = No
1 = Yes';

CREATE  INDEX XIF20PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(SPECIFICWEIGHINGCATEGORY_ID   ASC) TABLESPACE INDX;

ALTER TABLE PRODUCTLISTPERCUSTOMER
ADD CONSTRAINT PRODUCTLISTPERCUSTOMER_WEIGHIN FOREIGN KEY (SPECIFICWEIGHINGCATEGORY_ID) REFERENCES WEIGHINGCATEGORY (WEIGHINGCATEGORY_ID);


-------------CUSTOMERCOSTREPORTPRICE--------------

CREATE SEQUENCE SEQ_CUSTOMERCOSTREPORTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCOSTREPORTPRICE
(
	CUSTOMERCOSTREPORTPRICE_ID NUMBER NOT NULL ,
	PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	PRICE                NUMBER(15,6) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


CREATE UNIQUE INDEX XAK1CUSTOMERCOSTREPORTPRICE ON CUSTOMERCOSTREPORTPRICE
(PRODUCTLISTPERCUSTOMER_ID   ASC,STARTDATE   ASC) TABLESPACE INDX;

CREATE  INDEX XIF1CUSTOMERCOSTREPORTPRICE ON CUSTOMERCOSTREPORTPRICE
(PRODUCTLISTPERCUSTOMER_ID   ASC) TABLESPACE INDX;

ALTER TABLE CUSTOMERCOSTREPORTPRICE
ADD CONSTRAINT  XPKCUSTOMERCOSTREPORTPRICE PRIMARY KEY (CUSTOMERCOSTREPORTPRICE_ID) USING INDEX TABLESPACE INDX;



ALTER TABLE CUSTOMERCOSTREPORTPRICE
ADD CONSTRAINT CUSTOMERCOSTREPORTPRICE_PRODUC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID) REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;


--------update of productlistpercustomer records----------

update productlistpercustomer plpc
   set plpc.costpriceinclwashsalesprice = 1
 where plpc.billingmethod in (3, 4);
commit;

update productlistpercustomer plpc
   set plpc.costpriceinclrentprice = 1
 where plpc.billingmethod = 2;
commit;

update productlistpercustomer plpc
   set plpc.costpriceinclweightprice = 1
 where plpc.maxwashes = 0
   and (select dbs.code from databasesettings dbs) = 'ANG'
   and (select max(invoicemethod)
          from weighingcategpercustomer wcpc, product p
         where wcpc.customer_id = plpc.customer_id
           and wcpc.weighingcategory_id = p.weighingcategory_id
           and p.product_id = plpc.product_id) > 1;
commit;
