--Order 502895(new Lotus order 20010723.20)

      
      /*
      CHANGE REPORT for Table PLANTINVOICEFREQUENCY
          - Adding column KEEPHISTINVDATAPERIODS
      ACTION is ALTER or modify Table PLANTINVOICEFREQUENCY
      */


ALTER TABLE PLANTINVOICEFREQUENCY ADD (KEEPHISTINVDATAPERIODS NUMBER DEFAULT 99 
    NOT NULL
                                   CONSTRAINT KeepHistInvData_valid
                                          CHECK (KEEPHISTINVDATAPERIODS BETWEEN 
    0 AND 99));

COMMENT ON COLUMN PLANTINVOICEFREQUENCY.KEEPHISTINVDATAPERIODS IS
'Number of periods for which the COSTCENTERHISTINVDATA should be kept to allow future corrections for In-advance invoices. 
 After the number of periods has passed, the data will be physically removed from the database.
 The attribute will only be used when the module INVOICEPERACTIVITY is in the license of the plant, otherwise it wIll be ignored.';

      
      /*
      CHANGE REPORT for Table INVOICEHEADER
          - Adding column COSTCENTERHISTINVDATASTATUS
      ACTION is ALTER or modify Table INVOICEHEADER
      */

ALTER TABLE INVOICEHEADER ADD (COSTCENTERHISTINVDATASTATUS NUMBER DEFAULT 0 NOT 
    NULL
                                   CONSTRAINT CostCenterHist_valid
                                          CHECK (COSTCENTERHISTINVDATASTATUS IN 
    (0, 1, 2)));

COMMENT ON COLUMN INVOICEHEADER.COSTCENTERHISTINVDATASTATUS IS
'This flag indicates whether any CostCenterHistInvData records have been deleted via a scheduled process. Possible values:
0 = Not applicable (Default)
1 = Created, not deleted
2 = Deleted';

insert into process(process_id,description,active) values ('95','Clean Up Historical Invoice Data','Y');

begin
execute immediate ('delete
  from costcenterhistinvdata c
 where c.invoiceheader_id in (select ih.invoiceheader_id 
                                from invoiceheader ih, costcenterhistinvdata cchid
                               where cchid.invoiceheader_id = ih.invoiceheader_id
                                 and ih.nextperiodstartdate is null
                                 and ih.nextperiodenddate is null
                                )') ;
exception when others then null;
end;
/


begin
execute immediate ('delete
  from costcenterhistinvdata c
 where c.origin = 9') ;
exception when others then null;
end;
/


begin
execute immediate ('update invoiceheader i
set i.costcenterhistinvdatastatus = 1
where i.costcenterhistinvdatastatus = 0
and exists (select 1
           from costcenterhistinvdata cchid
           where cchid.invoiceheader_id = i.invoiceheader_id)') ;
exception when others then null;
end;
/


