Updating Summary Fields

 < Day Day Up > 



create or replace procedure summaries as       vtime float; begin       vtime := to_number(to_char(systimestamp,'SSSSS.FF'));           —customer.balance       update customer set balance =       (             select a.amount from             (                   select customer_id, sum(t.amount                      cb.amount) as amount                   from transactions t, cashbook cb                   where t.transaction_id =                      cb.transaction_id                   group by customer_id             ) a             where a.customer_id = customer.customer_id       );       commit;           —customer.lastdate       update customer set lastdate =       (             select a.dte from             (                   select customer_id, max(dte) as dte                   from transactions                   group by customer_id             ) a             where a.customer_id = customer.customer_id       );       commit;           —supplier.balance       update supplier set balance =       (             select a.amount from             (                   select supplier_id, sum(t.amount                      cb.amount) as amount                   from transactions t, cashbook cb                   where t.transaction_id =                      cb.transaction_id                   group by supplier_id             ) a             where a.supplier_id = supplier.supplier_id       );       commit;           —supplier.lastdate       update supplier set lastdate =       (             select a.dte from             (                   select supplier_id, max(dte) as dte                   from transactions                   group by supplier_id             ) a             where a.supplier_id = supplier.supplier_id       );       commit;           —coa.lastdate       update coa set lastdate =       (             select a.dte from             (                   select coa#, max(dte) as dte                   from generalledger                   group by coa#             ) a             where a.coa# = coa.coa#       );       commit;           —periodsum       insert into tmp_periodsum(year,period,coa#,amount)             select to_number(to_char(dte,'IYYY')) as year                   ,to_number(to_char(dte,'MM')) as period                   ,coa# as coa                   ,abs(sum(dr-cr)) as amount             from generalledger             group by to_number(to_char(dte,'IYYY'))                   ,to_number(to_char(dte,'MM')), coa#;       delete from periodsum;       insert into periodsum                   select * from tmp_periodsum where period                       in(1,2);       delete from tmp_periodsum where period in(1,2);       update periodsum set year=year–1,period=period+10;       update tmp_periodsum set period=period–2;       insert into periodsum select * from tmp_periodsum;       commit;           —coa.balance       update coa set balance =       (             select amount from periodsum             where year=2003 and period=1 and coa#=coa.coa#       );       commit;           update coa set ytd =       (             select sum(amount) from periodsum             where year=2003 and coa#=coa.coa#       );       commit;           —stock.lastdate       update stock set lastdate =       (             select a.dte from             (                   select stock_id, max(dte) as dte                   from stockmovement                   group by stock_id             ) a             where a.stock_id = stock.stock_id       );       commit;           —stock.qtyonhand       update stock set qtyonhand =       (             select a.qty from             (                   select stock_id, sum(qty) as qty                   from stockmovement                   group by stock_id             ) a             where a.stock_id = stock.stock_id       );       commit;           —stock.totalvalue       update stock set totalvalue =       (             select a.amount from             (                   select stock_id, sum(qty*price) as amount                   from stockmovement                   group by stock_id             ) a             where a.stock_id = stock.stock_id       );       commit;           dbms_output.put_line('PROC: UpdateSummaries Complete '             ||to_char(getTime(vtime)));       simAudit('PROCEDURE','UpdateSummaries',getTime(vtime),          0); exception when others then       dbms_output.put_line('PROC: UpdateSummaries          '||SQLERRM(SQLCODE));       rollback; end; / alter procedure summaries compile; /



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net