details: https://code.openbravo.com/erp/devel/pi/rev/28caa1914705 changeset: 32809:28caa1914705 user: Atul Gaware <atul.gaware <at> openbravo.com> date: Sat Sep 23 00:24:41 2017 +0530 summary: Fixes issue 36881: Performance issue in 'Process Purchase Plan' process
Avoid subqueries for m_matchpo, m_reservation and m_reservation stock table while inserting order lines product in MRP_RUN_INITIALIZE db procedure details: https://code.openbravo.com/erp/devel/pi/rev/d3fd6e8223f8 changeset: 32810:d3fd6e8223f8 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Mon Sep 25 12:27:35 2017 +0200 summary: Related to issue 36881: Format having clause diffstat: src-db/database/model/functions/MRP_RUN_INITIALIZE.xml | 26 ++++++----------- 1 files changed, 9 insertions(+), 17 deletions(-) diffs (54 lines): diff -r c8c81f25f650 -r d3fd6e8223f8 src-db/database/model/functions/MRP_RUN_INITIALIZE.xml --- a/src-db/database/model/functions/MRP_RUN_INITIALIZE.xml Mon Sep 25 12:08:56 2017 +0200 +++ b/src-db/database/model/functions/MRP_RUN_INITIALIZE.xml Mon Sep 25 12:27:35 2017 +0200 @@ -201,41 +201,33 @@ SELECT MRP_CHECK_PLANNINGMETHOD(cur_product.mrp_planningmethod_id, (CASE o.issotrx WHEN 'Y' THEN 'SO' ELSE 'PO' END), TO_NUMBER(COALESCE(ol.datepromised, ol.dateordered, o.dateordered) - p_planningdate), p_timehorizon) AS weighting, - (ol.qtyordered - COALESCE(ol.qtydelivered, 0) - COALESCE(matchpo.qty,0) - COALESCE(preres.reservedqty, 0) - (COALESCE(res.reservedqty,0) - COALESCE(res.releasedqty, 0))) AS qty, + (ol.qtyordered - COALESCE(ol.qtydelivered, 0) - COALESCE(sum(matchpo.qty), 0) - COALESCE(SUM(rs.quantity - COALESCE(rs.releasedqty, 0)), 0) - (COALESCE(res.reservedqty, 0) - COALESCE(res.releasedqty, 0))) AS qty, ol.c_orderline_id, GREATEST(COALESCE(ol.datepromised, ol.dateordered, o.dateordered), p_planningdate) AS planningdate, COALESCE(ol.dateordered, o.dateordered) AS dateordered, o.issotrx FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id JOIN c_doctype dt ON o.c_doctypetarget_id = dt.c_doctype_id - LEFT JOIN (SELECT c_orderline_id, sum(qty) AS qty - FROM m_matchpo - WHERE m_inoutline_id IS NOT NULL - GROUP BY c_orderline_id) matchpo ON ol.c_orderline_id = matchpo.c_orderline_id + LEFT JOIN m_matchpo matchpo ON ol.c_orderline_id = matchpo.c_orderline_id AND matchpo.m_inoutline_id IS NOT NULL JOIN m_warehouse w ON ol.m_warehouse_id = w.m_warehouse_id -- Sales order reservation LEFT JOIN m_reservation res ON res.c_orderline_id = ol.c_orderline_id AND res.res_status NOT IN ('CL', 'DR') -- Prereserved purchase orders - LEFT JOIN ( - SELECT rs.c_orderline_id, SUM(rs.quantity - COALESCE(rs.releasedqty, 0)) AS reservedqty - FROM m_reservation r - JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id - WHERE rs.c_orderline_id IS NOT NULL - AND r.res_status NOT IN ('CL', 'DR') - AND rs.m_locator_id IS NULL - GROUP BY rs.c_orderline_id - ) preres ON preres.c_orderline_id = ol.c_orderline_id + LEFT JOIN m_reservation r ON r.c_orderline_id = ol.c_orderline_id AND r.res_status NOT IN ('CL', 'DR') + LEFT JOIN m_reservation_stock rs ON rs.m_reservation_id = r.m_reservation_id AND rs.c_orderline_id IS NOT NULL AND rs.m_locator_id IS NULL WHERE o.processed = 'Y' - AND ol.qtyordered <> (COALESCE(ol.qtydelivered,0) + COALESCE(matchpo.qty,0)) AND ol.m_product_id = cur_product.m_product_id AND MRP_CHECK_PLANNINGMETHOD(cur_product.mrp_planningmethod_id, (CASE o.issotrx WHEN 'Y' THEN 'SO' ELSE 'PO' END), to_number(COALESCE(ol.datepromised, ol.dateordered, o.dateordered) - p_planningdate), p_timehorizon) <> -1 - AND ((o.issotrx = 'N' AND ol.qtyordered - COALESCE(matchpo.qty,0) > COALESCE(preres.reservedqty, 0)) - OR (o.issotrx = 'Y' AND (dt.docsubtypeso <> 'OB') AND ol.qtyordered - COALESCE(ol.qtydelivered, 0) > COALESCE(res.reservedqty,0) - COALESCE(res.releasedqty, 0))) AND AD_ISORGINCLUDED(o.ad_org_id, p_org_id, p_client_id) > -1 -- Only orders of warehouses in p_org_id and its childs AND AD_ISORGINCLUDED(w.ad_org_id, p_org_id, p_client_id) > -1 + GROUP BY o.issotrx, ol.datepromised, ol.dateordered, o.dateordered, ol.qtyordered, + ol.qtydelivered, res.reservedqty, res.releasedqty, ol.c_orderline_id, dt.docsubtypeso + HAVING ol.qtyordered <> (COALESCE(ol.qtydelivered, 0) + COALESCE(sum(matchpo.qty), 0)) + AND ((o.issotrx = 'N' AND ol.qtyordered - COALESCE(sum(matchpo.qty), 0) > COALESCE(sum(rs.quantity - COALESCE(rs.releasedqty, 0)), 0)) + OR (o.issotrx = 'Y' AND dt.docsubtypeso <> 'OB' AND ol.qtyordered - COALESCE(ol.qtydelivered, 0) > COALESCE(res.reservedqty, 0) - COALESCE(res.releasedqty, 0))) ) LOOP MRP_RUN_INSERTLINES(p_Client_ID, p_Org_ID, p_User_ID, p_Run, Cur_Product.M_Product_ID, (CASE Cur_OrderLine.IsSOTrx WHEN 'Y' THEN (-1*Cur_OrderLine.Qty*Cur_OrderLine.Weighting) ELSE (Cur_OrderLine.Qty*Cur_OrderLine.Weighting) END), (CASE Cur_OrderLine.IsSOTrx WHEN 'Y' THEN (-1*Cur_OrderLine.Qty*Cur_OrderLine.Weighting) ELSE (Cur_OrderLine.Qty*Cur_OrderLine.Weighting) END), NULL, (CASE Cur_OrderLine.IsSOTrx WHEN 'Y' THEN 'SO' ELSE 'PO' END), 'Y', Cur_OrderLine.C_OrderLine_ID, NULL, NULL, NULL, NULL, Cur_OrderLine.planningDate, Cur_OrderLine.dateordered, p_Production, (CASE WHEN (Cur_OrderLine.IsSOTrx = 'Y' AND p_Production = 'Y') OR (p_Production = 'N' AND Cur_OrderLine.Qty < 0 ) THEN 'N' ELSE 'Y' END), NULL, v_Aux_ID); END LOOP; ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
