-- ABS-85511 / ABS-92421
--- PRODUCT ---
ALTER TABLE PRODUCT ADD (AUTOSOIL VARCHAR2(1) DEFAULT 'N' NOT NULL
			CONSTRAINT CHK_PRODUCT_AUTOSOIL CHECK (AUTOSOIL IN ('Y','N')));

COMMENT ON COLUMN PRODUCT.AUTOSOIL IS 'Defines if AutoSoil is allowed to be set on a product on PLPC or ContractProduct';
--- UPDATE PRODUCTS ----
UPDATE PRODUCT P
   SET P.AUTOSOIL = 'Y'
 WHERE P.PRODUCT_ID IN
       (SELECT DISTINCT Z.PRODUCT_ID
          FROM (SELECT PLPC.PRODUCT_ID
                  FROM PRODUCTLISTPERCUSTOMER PLPC
                 WHERE PLPC.AUTOSOIL > 0
                UNION
                SELECT CP.PRODUCT_ID
                  FROM CONTRACTPRODUCT CP
                 INNER JOIN CONTRACTPRODUCTORDERDELIVERY CPOD
                    ON CP.CONTRACTPRODUCT_ID = CPOD.CONTRACTPRODUCT_ID
                 WHERE CPOD.AUTOSOILPERCENTAGE > 0) Z);
COMMIT;