/*ORDER 502397*/      
      /*
      CHANGE REPORT for Table PRODUCTLISTPERCUSTOMER
          - Adding column INVWASHBASEDONSOIL
      ACTION is ALTER or modify Table PRODUCTLISTPERCUSTOMER
      */

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (INVWASHBASEDONSOIL   VARCHAR2(1) 
    DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew155
                                          CHECK (INVWASHBASEDONSOIL IN ('Y', 
    'N')));

      
      /*
      CHANGE REPORT for Table SOILHISTORY
          - Adding a new relationship SOILHISTORY_INVOICEHEADER_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK SOILHISTORY_INVOICEHEADER_ID of table SOILHISTORY, which references table INVOICEHEADER).
          - Adding a new relationship SOILHISTORY_PROCESSBUSINESSUNI
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK SOILHISTORY_PROCESSBUSINESSUNI of table SOILHISTORY, which references table BUSINESSUNIT).
          - Adding column INVOICEHEADER_ID
          - Adding column INVOICEBUSINESSUNIT_ID
          - Adding index XAK1SOILHISTORY
          - Adding index XIE2SOILHISTORY
          - Adding index XIE4SOILHISTORY
          - Adding index XIF6SOILHISTORY
          - Adding index XIF7SOILHISTORY
      ACTION is ALTER or modify Table SOILHISTORY
      */

ALTER TABLE SOILHISTORY ADD (INVOICEHEADER_ID     NUMBER NULL);


ALTER TABLE SOILHISTORY ADD (INVOICEBUSINESSUNIT_ID NUMBER NULL);

CREATE INDEX XIE4SOILHISTORY ON SOILHISTORY
(
       INVOICEBUSINESSUNIT_ID         ,
       CUSTOMER_LINK_ID               ,
       PRODUCT_LINK_ID                
);

CREATE INDEX XIF6SOILHISTORY ON SOILHISTORY
(
       INVOICEHEADER_ID               ASC
);

CREATE INDEX XIF7SOILHISTORY ON SOILHISTORY
(
       INVOICEBUSINESSUNIT_ID         ASC
);


ALTER TABLE SOILHISTORY
       ADD  ( CONSTRAINT SOILHISTORY_INVOICEHEADER_ID
              FOREIGN KEY (INVOICEHEADER_ID)
                             REFERENCES INVOICEHEADER
                             ON DELETE SET NULL ) ;


ALTER TABLE SOILHISTORY
       ADD  ( CONSTRAINT SOILHISTORY_INVOICEBUSINESSUNI
              FOREIGN KEY (INVOICEBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;

----fill invoicebusinessunit_id
UPDATE SOILHISTORY SH
   SET SH.INVOICEBUSINESSUNIT_ID = NVL((SELECT CA.INVOICEBUSINESSUNIT_ID
                                         FROM CUSTOMERACTIVITIES CA,
                                              PRODUCT            P,
                                              PRODUCTGROUP       PG
                                        WHERE CA.CUSTOMER_ID =
                                              SH.CUSTOMER_LINK_ID
                                          AND CA.PROCESSBUSINESSUNIT_ID =
                                              SH.PROCESSBUSINESSUNIT_ID
                                          AND P.PRODUCT_ID =
                                              SH.PRODUCT_LINK_ID
                                          AND P.PRODUCTGROUP_ID =
                                              PG.PRODUCTGROUP_ID
                                          AND PG.ACTIVITY_ID =
                                              CA.ACTIVITY_ID),
                                       SH.PROCESSBUSINESSUNIT_ID);
                                       
ALTER TABLE SOILHISTORY MODIFY (INVOICEBUSINESSUNIT_ID NOT NULL);
