| < 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 > |
|