/*ORDER 502298*/      

      /*
      CHANGE REPORT for Table CUSTOMERACTIVITIES
          - Adding a new relationship CUSTOMERACTIVITIES_INVOICEFREQ
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK CUSTOMERACTIVITIES_INVOICEFREQ of table CUSTOMERACTIVITIES, which references table INVOICEFREQUENCY).
          - Adding column INVOICEFREQUENCY_ID
          - Adding column SEPARATEINVOICE
          - Adding column INCLUDENOTACTIVITYRELATEDCHARG
          - Adding column FROMDATEINADVANCE
          - Adding column TODATEINADVANCE
          - Adding index XIF4CUSTOMERACTIVITIES
      ACTION is ALTER or modify Table CUSTOMERACTIVITIES
      */

ALTER TABLE CUSTOMERACTIVITIES ADD (INVOICEFREQUENCY_ID  NUMBER NULL);


ALTER TABLE CUSTOMERACTIVITIES ADD (SEPARATEINVOICE      VARCHAR2(1) DEFAULT 
    'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew140
                                          CHECK (SEPARATEINVOICE IN ('Y', 'N')))
    ;


ALTER TABLE CUSTOMERACTIVITIES ADD (INCLUDENOTACTIVITYRELCHARGES VARCHAR2(1) 
    DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew141
                                          CHECK (INCLUDENOTACTIVITYRELCHARGES 
    IN ('Y', 'N')));


ALTER TABLE CUSTOMERACTIVITIES ADD (FROMDATEINADVANCE    DATE NULL);


ALTER TABLE CUSTOMERACTIVITIES ADD (TODATEINADVANCE      DATE NULL);

CREATE INDEX XIF4CUSTOMERACTIVITIES ON CUSTOMERACTIVITIES
(
       INVOICEFREQUENCY_ID            ASC
);


ALTER TABLE CUSTOMERACTIVITIES
       ADD  ( CONSTRAINT CUSTOMERACTIVITIES_INVOICEFREQ
              FOREIGN KEY (INVOICEFREQUENCY_ID)
                             REFERENCES INVOICEFREQUENCY ) ;


UPDATE CUSTOMERACTIVITIES CA 
   SET CA.INVOICEFREQUENCY_ID= (SELECT MIN(PI.INVOICEFREQUENCY_ID) FROM PLANT P, BUSINESSUNIT BU, PLANTINVOICEFREQUENCY PI, CUSTOMER C
   WHERE C.CUSTOMER_ID=CA.CUSTOMER_ID
   AND CA.INVOICEBUSINESSUNIT_ID=BU.BUSINESSUNIT_ID
   AND BU.PLANT_ID=P.PLANT_ID
   AND P.PLANT_ID=PI.PLANT_ID
   AND PI.INVOICEFREQUENCY_ID>0
   AND PI.DOCUMENTTYPE=1
   AND C.INVOICEFREQUENCY_ID in (0,200));


UPDATE CUSTOMERACTIVITIES CA 
   SET CA.INVOICEFREQUENCY_ID= (SELECT C.INVOICEFREQUENCY_ID FROM CUSTOMER C
   WHERE C.CUSTOMER_ID=CA.CUSTOMER_ID)  
   WHERE CA.INVOICEFREQUENCY_ID IS NULL;



