🚀 go-pugleaf

Bill Todd [TeamB] wrote:
> If someone asked you to improve the performance of code you have never
> seen could you do it?

LOL!! sorry but I was asking for head lines I mean what in general I 
should do, and I did not want to trouble you.

I use 3 triggers (after insert , after update and after delete) to 
execute a procedure

Trigger for after update :
   execute procedure PROC_AVGCOST_QTYBAL new.item_code, new.inv_code,
     old.item_code, old.inv_code, 'update';


procedure:
================================= Start

if (:p_operation = 'update') then begin
     if ((:p_new_inv_code <> :p_old_inv_code) OR (:p_new_item_code <> 
:p_old_item_code)) then BEGIN

 
/*---------------------------Purchase--------------------------------------*/
       select sum(QTY), sum(QTY * COST_PRICE) from purchase_inv_item
         left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
         where (purchase_inv.purch_return <> 1)
           and (PURCHASE_INV_ITEM.item_code = :p_old_item_code)
           and (PURCHASE_INV_ITEM.inv_code = :p_old_inv_code)
           into :pur_sum_qty, :pur_sum_cost;

       select sum(QTY) from purchase_inv_item
         left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
         where (purchase_inv.purch_return = 1)
           and (PURCHASE_INV_ITEM.item_code = :p_old_item_code)
           and (PURCHASE_INV_ITEM.inv_code = :p_old_inv_code)
           into :pur_ret_sum_qty;

 
/*---------------------------SALES--------------------------------------*/

       select sum(qty) from invoice_item
         left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
         where (invoice.sales_return <> 1)
           and (invoice_item.item_code = :p_old_item_code)
           and (invoice_item.inv_code = :p_old_inv_code)
           into :sale_sum_qty;

       select sum(qty) from invoice_item
         left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
         where (invoice.sales_return = 1)
           and (invoice_item.item_code = :p_old_item_code)
           and (invoice_item.inv_code = :p_old_inv_code)
           into :sale_ret_sum_qty;

       /*--------- >>>>>> Daily Sales */
       select sum(qty) from daily_sales
         where (daily_sales.SALES_RETURN <> 1)
           and (daily_sales.item_code = :p_old_item_code)
           and (daily_sales.inv_code = :p_old_inv_code)
           into :daily_sale_sum_qty;

       select sum(qty) from daily_sales
         where (daily_sales.SALES_RETURN = 1)
           and (daily_sales.item_code = :p_old_item_code)
           and (daily_sales.inv_code = :p_old_inv_code)
           into :daily_sale_ret_sum_qty;

       /*-------------------Update inventory------------------------*/
       select ob_qty from inventory_items
         where (inventory_items.item_code = :p_old_item_code)
         and (inventory_items.inv_code = :p_old_inv_code) into :ob_bal_qty;

       if (ob_bal_qty is null) then ob_bal_qty = 0;
       if (pur_sum_qty is null) then pur_sum_qty = 0;
       if (pur_ret_sum_qty is null) then pur_ret_sum_qty = 0;
       if (sale_ret_sum_qty is null) then sale_ret_sum_qty = 0;
       if (sale_sum_qty is null) then sale_sum_qty = 0;
       if (daily_sale_sum_qty is null) then daily_sale_sum_qty = 0;
       if (daily_sale_ret_sum_qty is null) then daily_sale_ret_sum_qty = 0;

       cur_bal_qty = :ob_bal_qty + :pur_sum_qty + :sale_ret_sum_qty
         - :pur_ret_sum_qty - :sale_sum_qty - daily_sale_sum_qty + 
daily_sale_ret_sum_qty;


       if (cur_bal_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;

       update inventory_items set
         UNIT_TOTAL_QTY = (inventory_items.OB_QTY + :pur_sum_qty),
         UNIT_TOTAL_COST = :pur_sum_cost, cur_qty = :cur_bal_qty
         where (inventory_items.item_code = :p_old_item_code)
           and (inventory_items.inv_code = :p_old_inv_code);

     END /* new <> old */
   END /*if ((:p_operation = 'update') then begin*/
   /*  ----------------------- NEW ITEM --------------------------*/
   /* --------------- purchase for new record ---------------------*/
   select sum(QTY), sum(QTY * COST_PRICE) from purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return <> 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_sum_qty, :pur_sum_cost;

   select sum(QTY) from purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return = 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_ret_sum_qty;
 
/*----------------------------------------------------------------------------*/
   /* ************** sales for item current qty ***********************/
   select sum(qty) from invoice_item
     left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
     where (invoice.sales_return <> 1)
       and (invoice_item.item_code = :p_new_item_code)
       and (invoice_item.inv_code = :p_new_inv_code)
       into :sale_sum_qty;

   select sum(qty) from invoice_item
     left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
     where (invoice.sales_return = 1)
       and (invoice_item.item_code = :p_new_item_code)
       and (invoice_item.inv_code = :p_new_inv_code)
       into :sale_ret_sum_qty;

   /*--------- >>>>>> Daily Sales */
   select sum(qty) from daily_sales
     where (daily_sales.SALES_RETURN <> 1)
       and (daily_sales.item_code = :p_old_item_code)
       and (daily_sales.inv_code = :p_old_inv_code)
       into :daily_sale_sum_qty;

   select sum(qty) from daily_sales
     where (daily_sales.SALES_RETURN = 1)
       and (daily_sales.item_code = :p_old_item_code)
       and (daily_sales.inv_code = :p_old_inv_code)
       into :daily_sale_ret_sum_qty;

   /*--------------Inventory update------------------------*/
   select OB_QTY from inventory_items
     where (inventory_items.item_code = :p_new_item_code)
     and (inventory_items.inv_code = :p_new_inv_code)
     into :ob_bal_qty;

   if (ob_bal_qty is null) then ob_bal_qty = 0;
   if (pur_sum_qty is null) then pur_sum_qty = 0;
   if (pur_ret_sum_qty is null) then pur_ret_sum_qty = 0;
   if (sale_ret_sum_qty is null) then sale_ret_sum_qty = 0;
   if (sale_sum_qty is null) then sale_sum_qty = 0;
   if (daily_sale_sum_qty is null) then daily_sale_sum_qty = 0;
   if (daily_sale_ret_sum_qty is null) then daily_sale_ret_sum_qty = 0;

   cur_bal_qty = :ob_bal_qty + :pur_sum_qty + :sale_ret_sum_qty
     - :pur_ret_sum_qty - :sale_sum_qty - daily_sale_sum_qty + 
daily_sale_ret_sum_qty;

   if (cur_bal_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;

   update inventory_items set
     UNIT_TOTAL_QTY = (inventory_items.OB_QTY + :pur_sum_qty),
     UNIT_TOTAL_COST = :pur_sum_cost,
     CUR_QTY = :cur_bal_qty
   where (inventory_items.item_code = :p_new_item_code)
     and (inventory_items.inv_code = :p_new_inv_code);

end

================================== End
Thanks