details: https://code.openbravo.com/erp/devel/pi/rev/6b10d4a9385d changeset: 25533:6b10d4a9385d user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Tue Dec 09 18:55:38 2014 +0100 summary: Fixed bug 28360 c_order_post creates unnecesary contentions
Avoiding the join with m_pricelist the m_pricelist contention is solved For update sentence is deleted in c_orderline selects because the c_orderline is bloqued with the main select and is redundant and causes unnecessary contentions. details: https://code.openbravo.com/erp/devel/pi/rev/a73f9b345a42 changeset: 25534:a73f9b345a42 user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Tue Dec 09 18:59:11 2014 +0100 summary: Fixed bug 28362 c_invoice_post creates unnecesary contentions Avoiding the join with m_pricelist and c_doctype contentions are solved diffstat: src-db/database/model/functions/C_INVOICE_POST.xml | 24 ++++++++++++++------- src-db/database/model/functions/C_ORDER_POST1.xml | 21 +++++++++++-------- 2 files changed, 28 insertions(+), 17 deletions(-) diffs (103 lines): diff -r 9b1b8a376543 -r a73f9b345a42 src-db/database/model/functions/C_INVOICE_POST.xml --- a/src-db/database/model/functions/C_INVOICE_POST.xml Tue Dec 09 11:16:40 2014 +0100 +++ b/src-db/database/model/functions/C_INVOICE_POST.xml Tue Dec 09 18:59:11 2014 +0100 @@ -174,7 +174,8 @@ v_iscashvat C_Invoice.IsCashVat%TYPE; v_invoiceline_qtysum NUMBER; - + v_PriceList_ID varchar2(32); + BEGIN IF (p_PInstance_ID IS NOT NULL) THEN -- Update AD_PInstance @@ -250,26 +251,33 @@ * Read Invoice */ v_ResultStr:='ReadingInvoice'; - SELECT i.Processing, i.Processed, i.DocAction, i.DocStatus, + SELECT i.Processing, i.Processed, i.DocAction, i.DocStatus, i.C_DocType_ID, i.C_DocTypeTarget_ID, i.PaymentRule, i.C_PaymentTerm_ID, i.DateAcct, i.DateInvoiced, i.AD_Client_ID, i.AD_Org_ID, i.UpdatedBy, i.DocumentNo, i.C_Order_ID, i.IsSOTrx, i.C_BPartner_ID, i.AD_User_ID, i.C_Currency_ID, i.POReference, i.Posted, - i.c_Project_Id, i.C_WithHolding_ID, dt.isreturn, - pl.istaxincluded, i.IsCashVat + i.c_Project_Id, i.C_WithHolding_ID, i.IsCashVat, i.M_PriceList_ID INTO v_Processing, v_Processed, v_DocAction, v_DocStatus, v_DocType_ID, v_DocTypeTarget_ID, v_PaymentRule, v_PaymentTerm, v_DateAcct, v_DateInvoiced, v_Client_ID, v_Org_ID, v_UpdatedBy, v_DocumentNo, v_Order_ID, v_IsSOTrx, v_BPartner_ID, v_BPartner_User_ID, v_Currency_ID, v_POReference, v_Posted, - v_C_Project_Id, cWithHoldID, v_isreturndoctype, - v_istaxincluded, v_iscashvat + v_C_Project_Id, cWithHoldID, v_iscashvat, v_PriceList_ID FROM C_INVOICE i - JOIN c_doctype dt ON i.c_doctypetarget_id = dt.c_doctype_id - JOIN m_pricelist pl ON i.m_pricelist_id = pl.m_pricelist_id WHERE i.C_Invoice_ID=v_Record_ID FOR UPDATE; + + SELECT dt.isreturn + INTO v_isreturndoctype + FROM c_doctype dt + WHERE dt.c_doctype_id= v_DocTypeTarget_ID; + + SELECT pl.istaxincluded + INTO v_istaxincluded + FROM m_pricelist pl + WHERE pl.m_pricelist_id= v_PriceList_ID; + DBMS_OUTPUT.PUT_LINE('Invoice_ID=' || v_Record_ID ||', DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus || ', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID) ; /** * Invoice Voided, Closed, or Reversed - No Action diff -r 9b1b8a376543 -r a73f9b345a42 src-db/database/model/functions/C_ORDER_POST1.xml --- a/src-db/database/model/functions/C_ORDER_POST1.xml Tue Dec 09 11:16:40 2014 +0100 +++ b/src-db/database/model/functions/C_ORDER_POST1.xml Tue Dec 09 18:59:11 2014 +0100 @@ -198,16 +198,20 @@ C_DocType_ID, C_DocTypeTarget_ID, c_order.AD_Client_ID, c_order.AD_Org_ID, c_order.UpdatedBy, M_Warehouse_ID, TRUNC(DateOrdered), Issotrx, c_Bpartner_Id, c_order.c_currency_id, C_PROJECT_ID, - C_BPartner_ID, c_order.M_PriceList_ID, invoicerule, m_pricelist.istaxincluded, - c_order.IsCashVat + C_BPartner_ID, M_PriceList_ID, invoicerule, c_order.IsCashVat INTO v_IsProcessing, v_IsProcessed, v_DocAction, v_DocStatus, v_DocType_ID, v_DocTypeTarget_ID, v_Client_ID, v_Org_ID, v_UpdatedBy, v_M_Warehouse_ID, v_Date, v_isSoTrx, v_c_Bpartner_Id, v_c_currency_id, v_C_PROJECT_ID, - v_CBPartner_ID, v_PriceList_ID, v_invoicerule, v_istaxincluded, - v_iscashvat - FROM C_ORDER JOIN m_pricelist ON c_order.m_pricelist_id = m_pricelist.m_pricelist_id - WHERE C_Order_ID=v_Record_ID FOR UPDATE; + v_CBPartner_ID, v_PriceList_ID, v_invoicerule, v_iscashvat + FROM C_ORDER + WHERE C_Order_ID=v_Record_ID FOR UPDATE ; + + SELECT m_pricelist.istaxincluded + INTO v_istaxincluded + FROM m_pricelist + WHERE m_pricelist_id= v_PriceList_ID; + -- Get current DocSubTypeSO SELECT DocSubTypeSO INTO v_DocSubTypeSO @@ -518,7 +522,7 @@ WHERE l.C_Order_ID=v_Record_ID -- Reserve Products (not: services, null products) AND l.M_Product_ID=p.M_Product_ID AND p.IsStocked='Y' - AND p.ProductType='I' FOR UPDATE + AND p.ProductType='I' ) LOOP -- Qty corrected for SO/PO @@ -1118,8 +1122,7 @@ AND p.IsStocked='Y' AND p.ProductType='I' -- Target Level = 0 if DirectShip='Y' or Binding='N' AND (CASE l.DirectShip WHEN 'Y' THEN 0 ELSE (CASE v_IsBinding WHEN 'N' THEN 0 ELSE l.QtyOrdered END)END) - -l.QtyReserved-l.QtyDelivered <> 0 - FOR UPDATE) + -l.QtyReserved-l.QtyDelivered <> 0) LOOP -- Qty corrected for SO/PO ------------------------------------------------------------------------------ Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server from Actuate! Instantly Supercharge Your Business Reports and Dashboards with Interactivity, Sharing, Native Excel Exports, App Integration & more Get technology previously reserved for billion-dollar corporations, FREE http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
