details: https://code.openbravo.com/erp/devel/pi/rev/f88e6d657403 changeset: 18400:f88e6d657403 user: Javier Etxarri <javier.echarri <at> openbravo.com> date: Wed Oct 31 19:48:02 2012 +0100 summary: Backout issue 21831: Trial Balance shows different Final Balance depending the check 'Opening Entry Amount to Initial Balance'
details: https://code.openbravo.com/erp/devel/pi/rev/2da392a94c87 changeset: 18401:2da392a94c87 user: Javier Etxarri <javier.echarri <at> openbravo.com> date: Wed Oct 31 19:50:18 2012 +0100 summary: Backout issue 22086: M_INOUT_POST are using an OR in a CURSOR so the query is doing a Full Table Scan which has a very big impact in performance diffstat: src-db/database/model/functions/M_INOUT_POST.xml | 77 +++------ src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java | 8 +- 2 files changed, 31 insertions(+), 54 deletions(-) diffs (132 lines): diff -r 220de5542b31 -r 2da392a94c87 src-db/database/model/functions/M_INOUT_POST.xml --- a/src-db/database/model/functions/M_INOUT_POST.xml Wed Oct 31 18:07:20 2012 +0100 +++ b/src-db/database/model/functions/M_INOUT_POST.xml Wed Oct 31 19:50:18 2012 +0100 @@ -56,12 +56,12 @@ v_is_tr_allow AD_OrgType.IsTransactionsAllowed%TYPE; v_DateAcct DATE; v_isacctle AD_OrgType.IsAcctLegalEntity%TYPE; - V_Org_Bule_Id Ad_Org.Ad_Org_Id%Type; - Cur_InOut M_INOUT%ROWTYPE; + v_org_bule_id AD_Org.AD_Org_ID%TYPE; -- Parameter TYPE RECORD IS REF CURSOR; Cur_Parameter RECORD; -- + Cur_InOut RECORD; Cur_InOutLine RECORD; Cur_Order RECORD; -- @@ -90,17 +90,8 @@ v_OrderID_old VARCHAR2(32); Cur_MILines RECORD; FINISH_PROCESS BOOLEAN:=false; - Next_InOut BOOLEAN:=FALSE; v_Aux NUMBER; v_ProductName M_Product.name%TYPE; - - CURSOR Cur_InOutAll IS - SELECT * FROM M_INOUT - WHERE DocAction = 'CO' and IsActive = 'Y' FOR UPDATE; - CURSOR Cur_InOutOne (InOut_ID VARCHAR) IS - SELECT * FROM M_INOUT - WHERE M_InOut_ID = InOut_ID and IsActive = 'Y' FOR UPDATE; - BEGIN IF(p_PInstance_ID IS NOT NULL) THEN -- Update AD_PInstance @@ -282,45 +273,30 @@ RAISE_APPLICATION_ERROR(-20000, '@PeriodNotAvailable@'); END IF; END IF; - - IF (v_Record_ID IS NOT NULL) THEN - OPEN Cur_InOutOne(v_Record_ID); - ELSE - OPEN Cur_InOutAll; - END IF; - - Loop - - IF v_Record_ID IS NOT NULL THEN - FETCH Cur_InOutOne INTO Cur_InOut; - IF (Cur_InOutOne%NOTFOUND) THEN -- mandatory - CLOSE Cur_InOutOne; - DBMS_OUTPUT.PUT_LINE('- No M_InOut -') ; - EXIT; - END IF; - ELSE - FETCH Cur_InOutAll INTO Cur_InOut; - IF (Cur_InOutAll%NOTFOUND) THEN -- mandatory - CLOSE Cur_InOutAll; - DBMS_OUTPUT.PUT_LINE('- No M_InOut -') ; - Exit; - END IF; - END IF; - - DBMS_OUTPUT.PUT_LINE('Shipment_ID=' || Cur_InOut.M_InOut_ID || ', Doc=' || Cur_InOut.DocumentNo || ', Status=' || Cur_InOut.DocStatus || ', Action=' || Cur_InOut.DocAction) ; - v_ResultStr:='HeaderLoop'; - /** - * Shipment not processed - */ - IF(Cur_InOut.Processed='N' AND Cur_InOut.DocStatus='DR' AND Cur_InOut.DocAction='CO') THEN - -- For all active shipment lines - v_ResultStr:='HeaderLoop-1'; - SELECT COUNT(*) INTO v_Aux - FROM M_InOutLine - WHERE M_InOut_ID = v_Record_ID; - IF v_Aux=0 THEN - RAISE_APPLICATION_ERROR(-20000, '@ReceiptWithoutLines@'); - END IF; + + FOR Cur_InOut IN + (SELECT * + FROM M_INOUT + WHERE(M_InOut_ID=v_Record_ID + OR(v_Record_ID IS NULL + AND DocAction='CO')) + AND IsActive='Y' FOR UPDATE + ) + LOOP + DBMS_OUTPUT.PUT_LINE('Shipment_ID=' || Cur_InOut.M_InOut_ID || ', Doc=' || Cur_InOut.DocumentNo || ', Status=' || Cur_InOut.DocStatus || ', Action=' || Cur_InOut.DocAction) ; + v_ResultStr:='HeaderLoop'; + /** + * Shipment not processed + */ + IF(Cur_InOut.Processed='N' AND Cur_InOut.DocStatus='DR' AND Cur_InOut.DocAction='CO') THEN + -- For all active shipment lines + v_ResultStr:='HeaderLoop-1'; + SELECT COUNT(*) INTO v_Aux + FROM M_InOutLine + WHERE M_InOut_ID = v_Record_ID; + IF v_Aux=0 THEN + RAISE_APPLICATION_ERROR(-20000, '@ReceiptWithoutLines@'); + END IF; FOR Cur_InOutLine IN (SELECT * FROM M_INOUTLINE @@ -753,6 +729,7 @@ WHERE ad_ep_instance_id = v_ep_instance; END; END IF; + END LOOP; -- InOut Header /** * Transaction End diff -r 220de5542b31 -r 2da392a94c87 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Oct 31 18:07:20 2012 +0100 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Oct 31 19:50:18 2012 +0100 @@ -743,10 +743,10 @@ data[i].saldoInicial = (new BigDecimal(dataIB[k].saldoInicial).add(parcialInicial)) .toPlainString(); } else { - data[i].amtacctdr = (new BigDecimal(dataIB[k].amtacctdr).add(parcialDR) - .add(new BigDecimal(data[i].amtacctdr))).toPlainString(); - data[i].amtacctcr = (new BigDecimal(dataIB[k].amtacctcr).add(parcialCR) - .add(new BigDecimal(data[i].amtacctcr))).toPlainString(); + data[i].amtacctdr = (new BigDecimal(dataIB[k].amtacctdr).add(parcialDR)) + .toPlainString(); + data[i].amtacctcr = (new BigDecimal(dataIB[k].amtacctcr).add(parcialCR)) + .toPlainString(); } data[i].saldoFinal = (new BigDecimal(dataIB[k].saldoInicial).add(parcialDR) .subtract(parcialCR)).toPlainString(); ------------------------------------------------------------------------------ Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
