-- ABS-48110
---------   CLIENTPRODFINISHINGMETHOD   ---------
CREATE TABLE CLIENTPRODFINISHINGMETHOD
(
CLIENT_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
FINISHINGMETHOD_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CLIENTPRODFINISHINGMETHOD ADD CONSTRAINT XPKCLIENTPRODFINISHINGMETHOD PRIMARY KEY (CLIENT_ID, PRODUCT_ID);

CREATE INDEX XIF1CLIENTPRODFINISHINGMETHOD ON CLIENTPRODFINISHINGMETHOD (CLIENT_ID ASC);
CREATE INDEX XIF2CLIENTPRODFINISHINGMETHOD ON CLIENTPRODFINISHINGMETHOD (PRODUCT_ID ASC);
CREATE INDEX XIF3CLIENTPRODFINISHINGMETHOD ON CLIENTPRODFINISHINGMETHOD (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIE1CLIENTPRODFINISHINGMETHOD ON CLIENTPRODFINISHINGMETHOD (PRODUCT_ID ASC, FINISHINGMETHOD_ID ASC);

ALTER TABLE CLIENTPRODFINISHINGMETHOD ADD CONSTRAINT CLIENTPRODFINISHINGMETHOD_CID FOREIGN KEY (CLIENT_ID)
REFERENCES CLIENT (CLIENT_ID) ON DELETE CASCADE;

ALTER TABLE CLIENTPRODFINISHINGMETHOD ADD CONSTRAINT CLIENTPRODFINISHINGMETHOD_PID FOREIGN KEY (PRODUCT_ID, FINISHINGMETHOD_ID)
REFERENCES PRODUCTFINISHINGMETHOD (PRODUCT_ID,FINISHINGMETHOD_ID);

---------   CUSTOMERRESIDENTIALSETTINGS   ---------
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS ADD (CLIENTFILTERASSIGNPRODUCT NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT CLIENTFILTERASSIGNPRO_VALID1_1 CHECK (CLIENTFILTERASSIGNPRODUCT IN (0,1,2)));
						
COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.CLIENTFILTERASSIGNPRODUCT IS '0 = No filter
1 = Filter on Finishing Method
2 = Filter on Finishing Method per Product';

UPDATE CUSTOMERRESIDENTIALSETTINGS CRS
SET CRS.CLIENTFILTERASSIGNPRODUCT = 1
WHERE EXISTS (SELECT 1 FROM CLIENT C, CLIENTFINISHINGMETHOD CFM
             WHERE C.CUSTOMER_ID=CRS.CUSTOMER_ID
             AND CFM.CLIENT_ID=C.CLIENT_ID);
COMMIT;

---------   PRODUCTLISTPERCUSTOMER   ---------
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (CLIENTFILTERSTANDARD NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT CLIENTFILTERSTANDARD_VALID1_1 CHECK (CLIENTFILTERSTANDARD IN (0,1)));

COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.CLIENTFILTERSTANDARD IS '0 = Non-Standard Product must be defined in Client Products per FM Filter
1 = Standard Product';

UPDATE PRODUCTLISTPERCUSTOMER PLPC
   SET PLPC.CLIENTFILTERSTANDARD = 1
 WHERE (SELECT COUNT(1)
          FROM PRODUCTLISTPERCUSTOMER PLPC2
         WHERE PLPC2.CUSTOMER_ID = PLPC.CUSTOMER_ID
           AND PLPC2.PRODUCT_ID = PLPC.PRODUCT_ID
	   AND PLPC2.SIZEDEFINITION_ID = PLPC.SIZEDEFINITION_ID
	   AND PLPC2.DATEACTIVE <= SYSDATE
   	   AND PLPC2.DATEINACTIVE > SYSDATE) = 1
   AND PLPC.DATEACTIVE <= SYSDATE
   AND PLPC.DATEINACTIVE > SYSDATE
   AND PLPC.PRODUCT_ID IN
       (SELECT P.PRODUCT_ID FROM PRODUCT P WHERE P.TYPEOFMERCHANDISE = 5);
COMMIT;




