details: https://code.openbravo.com/erp/devel/pi/rev/a2ce02e621d9 changeset: 24102:a2ce02e621d9 user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> date: Tue Jul 22 10:21:40 2014 +0200 summary: Fixed bug 27140: Useless usage of hex_to_int()
details: https://code.openbravo.com/erp/devel/pi/rev/796652fcb518 changeset: 24103:796652fcb518 user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> date: Tue Jul 22 10:22:19 2014 +0200 summary: Fixed bug 27141: Minor performance fix in C_ORDERLINETAX_TRG details: https://code.openbravo.com/erp/devel/pi/rev/085ea1fce739 changeset: 24104:085ea1fce739 user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> date: Tue Jul 22 10:23:09 2014 +0200 summary: Fixed bug 27023 Fixed bug 27024: Performance improvements on C_ORDERLINE_TRG2 diffstat: src-db/database/model/functions/C_ORDER_POST1.xml | 8 +- src-db/database/model/triggers/C_ORDERLINETAX_TRG.xml | 4 +- src-db/database/model/triggers/C_ORDERLINE_TRG2.xml | 93 +++++++++--------- 3 files changed, 52 insertions(+), 53 deletions(-) diffs (188 lines): diff -r e624a8506d55 -r 085ea1fce739 src-db/database/model/functions/C_ORDER_POST1.xml --- a/src-db/database/model/functions/C_ORDER_POST1.xml Tue Jul 22 17:55:48 2014 +0530 +++ b/src-db/database/model/functions/C_ORDER_POST1.xml Tue Jul 22 10:23:09 2014 +0200 @@ -1348,8 +1348,8 @@ */ BEGIN v_ResultStr:='OrderCompleteCheck'; - SELECT SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyDelivered*hex_to_int(C_OrderLine_ID)), - SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyInvoiced*hex_to_int(C_OrderLine_ID)) + SELECT SUM(QtyOrdered) -SUM(QtyDelivered), + SUM(QtyOrdered) -SUM(QtyInvoiced) INTO ToDeliver, ToInvoice FROM C_ORDERLINE @@ -1498,8 +1498,8 @@ /** * Final Completeness check */ - SELECT SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyDelivered*hex_to_int(C_OrderLine_ID)), - SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) -SUM(QtyInvoiced*hex_to_int(C_OrderLine_ID)) + SELECT SUM(QtyOrdered) -SUM(QtyDelivered), + SUM(QtyOrdered) -SUM(QtyInvoiced) INTO ToDeliver, ToInvoice FROM C_ORDERLINE diff -r e624a8506d55 -r 085ea1fce739 src-db/database/model/triggers/C_ORDERLINETAX_TRG.xml --- a/src-db/database/model/triggers/C_ORDERLINETAX_TRG.xml Tue Jul 22 17:55:48 2014 +0530 +++ b/src-db/database/model/triggers/C_ORDERLINETAX_TRG.xml Tue Jul 22 10:23:09 2014 +0200 @@ -17,7 +17,7 @@ * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SLU - * Contributions are Copyright (C) 2010-2013 Openbravo, S.L.U. + * Contributions are Copyright (C) 2010-2014 Openbravo, S.L.U. * * Specifically, this derivative work is based upon the following Compiere * file and version. @@ -70,7 +70,7 @@ END IF; IF (INSERTING OR UPDATING) THEN - SELECT COUNT(C_TAX_ID) INTO v_Count + SELECT COUNT(1) INTO v_Count FROM C_ORDERTAX WHERE C_ORDER_ID = :NEW.C_Order_ID AND C_TAX_ID = :NEW.C_TAX_ID; diff -r e624a8506d55 -r 085ea1fce739 src-db/database/model/triggers/C_ORDERLINE_TRG2.xml --- a/src-db/database/model/triggers/C_ORDERLINE_TRG2.xml Tue Jul 22 17:55:48 2014 +0530 +++ b/src-db/database/model/triggers/C_ORDERLINE_TRG2.xml Tue Jul 22 10:23:09 2014 +0200 @@ -19,25 +19,12 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2001-2012 Openbravo SLU + * All portions are Copyright (C) 2001-2014 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ - TYPE RECORD IS REF CURSOR; - Cur_Offer RECORD; - - v_OrderLine_Offer_ID VARCHAR2(32); - v_lineno NUMBER:=0; - v_Price NUMBER; - v_PriceNew NUMBER; - v_Amt NUMBER; - v_BPartner_ID VARCHAR2(32); - v_Order_ID VARCHAR2(32); - v_Insert BOOLEAN :=FALSE; v_istaxincluded CHAR(1) ; - v_Precision NUMBER; - v_PriceList_ID VARCHAR2(32); v_Processed C_ORDER.PROCESSED%TYPE; v_ID VARCHAR2(32); v_oldLine NUMBER; @@ -49,8 +36,9 @@ v_grandtotal NUMBER:=0; v_taxAmt NUMBER; v_Prec C_CURRENCY.STDPRECISION%TYPE; - v_BaseAmount C_TAX.BASEAMOUNT%TYPE; v_AttrSetValueType M_Product.AttrSetValueType%TYPE; + v_oldLineAlternate NUMBER; + v_create CHAR(1):='Y'; BEGIN @@ -73,49 +61,61 @@ JOIN c_currency ON c_order.c_currency_id = c_currency.c_currency_id JOIN m_pricelist ON c_order.m_pricelist_id = m_pricelist.m_pricelist_id WHERE C_Order_ID=v_ID; - v_oldLine:=0; - v_newLineNetAmt:=0; - IF (v_istaxincluded = 'Y') THEN - IF (DELETING) THEN - v_oldgrossamt := :old.line_gross_amount; - ELSIF (UPDATING) THEN - v_oldgrossamt := :old.line_gross_amount; - v_newgrossamt := :new.line_gross_amount; - ELSE - v_newgrossamt := :new.line_gross_amount; - END IF; - END IF; + IF(v_Processed='N') THEN + v_oldLine:=0; + v_newLineNetAmt:=0; + IF (v_istaxincluded = 'Y') THEN + IF (DELETING) THEN + v_oldgrossamt := :old.line_gross_amount; + ELSIF (UPDATING) THEN + v_oldgrossamt := :old.line_gross_amount; + v_newgrossamt := :new.line_gross_amount; + ELSE + v_newgrossamt := :new.line_gross_amount; + END IF; + END IF; + -- Calculate taxes IF(UPDATING OR DELETING) THEN v_oldLine:= COALESCE(:old.LineNetAmt,0) + COALESCE(:old.FreightAmt,0) + COALESCE(:old.ChargeAmt,0); END IF; IF (INSERTING OR UPDATING) THEN - DELETE FROM C_ORDERLINETAX WHERE C_ORDERLINE_ID = :new.C_OrderLine_ID; v_newLineNetAmt := COALESCE(:new.LineNetAmt,0) + COALESCE(:new.FreightAmt,0) + COALESCE(:new.ChargeAmt,0); v_newLineAlternate := COALESCE(:new.TaxBaseAmt,0) + COALESCE(:new.FreightAmt,0) + COALESCE(:new.ChargeAmt,0); - SELECT BaseAmount INTO v_BaseAmount - FROM C_TAX - WHERE C_TAX_ID = :new.C_Tax_ID; - C_ORDERLINETAX_INSERT(:new.AD_Org_ID, :new.C_Order_ID, :new.C_OrderLine_ID, :new.UpdatedBy, :new.C_Tax_ID, :new.C_Tax_ID, v_newLineNetAmt, v_newLineAlternate, v_Prec); - IF (v_istaxincluded = 'Y') THEN - C_ORDERLINETAX_ROUNDING(:new.C_OrderLine_ID, :new.line_gross_amount, v_newLineNetAmt); + + IF (UPDATING) THEN + v_oldLineAlternate := COALESCE(:old.TaxBaseAmt,0) + COALESCE(:old.FreightAmt,0) + COALESCE(:old.ChargeAmt,0); + IF (:new.AD_Org_ID <> :old.AD_Org_ID OR :new.C_Tax_ID <> :old.C_Tax_ID OR + v_newLineNetAmt <> v_oldLine OR v_newLineAlternate <> v_oldLineAlternate OR + :new.line_gross_amount <> :old.line_gross_amount) THEN + DELETE FROM C_ORDERLINETAX WHERE C_ORDERLINE_ID = :new.C_OrderLine_ID; + v_create := 'Y'; + ELSE + v_create := 'N'; + END IF; END IF; - -- Get Total Tax Amt - SELECT SUM(TaxAmt) INTO v_taxAmt - FROM C_OrderTax - WHERE C_Order_ID=:new.C_Order_ID; - -- DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt); - -- Update Header - UPDATE C_Order - SET TotalLines = TotalLines - v_oldLine + v_newLineNetAmt, - GrandTotal = CASE v_istaxincluded + + IF (v_create = 'Y') THEN + C_ORDERLINETAX_INSERT(:new.AD_Org_ID, :new.C_Order_ID, :new.C_OrderLine_ID, :new.UpdatedBy, :new.C_Tax_ID, :new.C_Tax_ID, v_newLineNetAmt, v_newLineAlternate, v_Prec); + IF (v_istaxincluded = 'Y') THEN + C_ORDERLINETAX_ROUNDING(:new.C_OrderLine_ID, :new.line_gross_amount, v_newLineNetAmt); + END IF; + -- Get Total Tax Amt + SELECT SUM(TaxAmt) INTO v_taxAmt + FROM C_OrderTax + WHERE C_Order_ID=:new.C_Order_ID; + -- DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt); + -- Update Header + UPDATE C_Order + SET TotalLines = TotalLines - v_oldLine + v_newLineNetAmt, + GrandTotal = CASE v_istaxincluded WHEN 'Y' THEN grandtotal - v_oldgrossamt + v_newgrossamt ELSE TotalLines - v_oldLine + v_newLineNetAmt + COALESCE(v_taxAmt, 0) - END - -- Updated = SysDate -- Don't update as otherwise it does not save changes - WHERE C_Order_ID = :new.C_Order_ID; + END + WHERE C_Order_ID = :new.C_Order_ID; + END IF; ELSE -- DELETING SELECT SUM(TaxAmt) INTO v_taxAmt FROM C_OrderTax @@ -128,7 +128,6 @@ WHEN 'Y' THEN grandtotal - v_oldgrossamt ELSE TotalLines - v_oldLine + v_newLineNetAmt + COALESCE(v_taxAmt, 0) END - -- Updated = SysDate -- Don't update as otherwise it does not save changes WHERE C_Order_ID=:old.C_Order_ID; END IF; IF (v_istaxincluded = 'Y') THEN ------------------------------------------------------------------------------ Want fast and easy access to all the code in your enterprise? Index and search up to 200,000 lines of code with a free copy of Black Duck Code Sight - the same software that powers the world's largest code search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
