details: https://code.openbravo.com/erp/devel/pi/rev/061968db96b6 changeset: 17923:061968db96b6 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Mon Sep 10 17:10:50 2012 +0200 summary: Related to issue 21551.Format mrp_run_initialize procedure.
details: https://code.openbravo.com/erp/devel/pi/rev/61377c542f43 changeset: 17924:61377c542f43 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Mon Sep 10 17:29:34 2012 +0200 summary: Fixed issue 21551.Consider only stock and orders of warehouses in the child org tree. diffstat: src-db/database/model/functions/MRP_RUN_INITIALIZE.xml | 529 ++++++++-------- 1 files changed, 273 insertions(+), 256 deletions(-) diffs (truncated from 555 to 300 lines): diff -r d6111f597f9c -r 61377c542f43 src-db/database/model/functions/MRP_RUN_INITIALIZE.xml --- a/src-db/database/model/functions/MRP_RUN_INITIALIZE.xml Tue Sep 11 17:07:03 2012 +0200 +++ b/src-db/database/model/functions/MRP_RUN_INITIALIZE.xml Mon Sep 10 17:29:34 2012 +0200 @@ -52,7 +52,7 @@ * 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-2006 Openbravo SLU +* All portions are Copyright (C) 2001-2012 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -77,270 +77,287 @@ Cur_RequisitionLine RECORD; Cur_Phase_Dependants RECORD; BEGIN - BEGIN --BODY - -- Get Parameters - FOR Cur_Product IN (SELECT p.M_Product_ID, - COALESCE(po.STOCKMIN, p.STOCKMIN, 0) AS STOCKMIN, - COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID) AS MRP_PlanningMethod_ID - FROM M_PRODUCT p LEFT JOIN M_PRODUCT_ORG po ON p.M_PRODUCT_ID = po.M_PRODUCT_ID - AND po.AD_ORG_ID = p_Org_ID - WHERE (p_product_ID IS NULL OR p.M_PRODUCT_ID = p_Product_ID) - AND (p_Product_Category_ID IS NULL OR p.M_PRODUCT_CATEGORY_ID = p_Product_Category_ID) - AND (p_Planner_ID IS NULL OR COALESCE(po.MRP_PLANNER_ID, p.MRP_Planner_ID) = p_Planner_ID) - AND Ad_Isorgincluded(p_Org_ID, p.AD_ORG_ID, p_Client_ID) > -1 - AND p.AD_Client_ID = p_Client_ID - AND ((p_Production = 'Y' AND p.ISPURCHASED = 'N') OR (p_Production = 'N' AND p.ISPURCHASED = 'Y')) - AND P.ISACTIVE = 'Y' - AND (p_Production = 'Y' OR - (p_Vendor_ID IS NULL - OR EXISTS (SELECT 1 - FROM M_PRODUCT_PO - WHERE M_PRODUCT_PO.M_PRODUCT_ID = p.M_PRODUCT_ID - AND M_PRODUCT_PO.C_BPARTNER_ID = p_Vendor_ID - AND M_PRODUCT_PO.ISCURRENTVENDOR = 'Y' - AND M_PRODUCT_PO.ISACTIVE = 'Y' - AND M_PRODUCT_PO.DISCONTINUED = 'N' - ))) - AND (p_BPartner_ID IS NULL - OR EXISTS (SELECT 1 - FROM C_ORDER o, C_ORDERLINE ol - WHERE o.C_ORDER_ID = ol.C_ORDER_ID - AND o.C_BPARTNER_ID = p_BPartner_ID - AND o.IsSOTrx = 'Y' - --AND C_Order_Status(o.C_ORDER_ID) IN (1, 2, 3) - AND o.PROCESSED = 'Y' - AND ol.QTYORDERED <> ol.QTYDELIVERED - AND ol.m_product_id = p.m_product_id - AND ol.DatePromised IS NOT NULL - AND Mrp_Check_Planningmethod( - COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID), - 'SO', TO_NUMBER(COALESCE(ol.DatePromised, ol.DateOrdered, o.DateOrdered) - p_PlanningDate), p_TimeHorizon) <> -1 - AND Ad_Isorgincluded(o.AD_ORG_ID, p_Org_ID, p_Client_ID) > -1) - OR EXISTS (SELECT 1 - FROM MRP_SALESFORECAST sf, MRP_SALESFORECASTLINE sfl - WHERE sf.MRP_SALESFORECAST_ID = sfl.MRP_SALESFORECAST_ID - AND sf.IsActive = 'Y' - AND sf.C_BPARTNER_ID = p_BPartner_ID - AND sfl.m_product_id = p.m_product_id - AND Mrp_Check_Planningmethod( - COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID), - 'SF', TO_NUMBER(sfl.DatePlanned - p_PlanningDate), p_TimeHorizon) <> -1 - AND Ad_Isorgincluded(sf.AD_ORG_ID, p_Org_ID, p_Client_ID) > -1) - ) - AND (p_BP_Group_ID IS NULL - OR EXISTS(SELECT 1 - FROM C_ORDER o, C_ORDERLINE ol, C_BPARTNER bp - WHERE o.C_ORDER_ID = ol.C_ORDER_ID - AND o.C_BPartner_ID = bp.C_BPartner_ID - AND o.IsSOTrx = 'Y' - AND bp.C_BP_Group_ID = p_BP_Group_ID - --AND C_Order_Status(o.C_ORDER_ID) IN (1, 2, 3) - AND o.PROCESSED = 'Y' - AND ol.QTYORDERED <> ol.QTYDELIVERED - AND ol.m_product_id = p.m_product_id - AND ol.DatePromised IS NOT NULL - AND Mrp_Check_Planningmethod( - COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID), - 'SO', TO_NUMBER(COALESCE(ol.DatePromised, ol.DateOrdered, o.DateOrdered) - p_PlanningDate), p_TimeHorizon) <> -1 - AND Ad_Isorgincluded(o.AD_ORG_ID, p_Org_ID, p_Client_ID) > -1) - OR EXISTS (SELECT 1 - FROM MRP_SALESFORECAST sf, MRP_SALESFORECASTLINE sfl, C_BPARTNER bp - WHERE sf.MRP_SALESFORECAST_ID = sfl.MRP_SALESFORECAST_ID - AND sf.IsActive = 'Y' - AND sf.C_BPartner_ID = bp.C_BPartner_ID - AND sfl.m_product_id = p.m_product_id - AND bp.C_BP_Group_ID = p_BP_Group_ID - AND Mrp_Check_Planningmethod( - COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID), - 'SF', TO_NUMBER(sfl.DatePlanned - p_PlanningDate), p_TimeHorizon) <> -1 - AND Ad_Isorgincluded(sf.AD_ORG_ID, p_Org_ID, p_Client_ID) > -1) - ) - ) LOOP - IF (p_Production = 'Y') THEN - SELECT COUNT(*) INTO v_Count - FROM MRP_RUN_PRODUCTIONLINE - WHERE M_PRODUCT_ID = Cur_Product.M_Product_ID - AND MRP_RUN_PRODUCTION_ID = p_Run - AND inouttrxtype = 'MS'; - ELSE - SELECT COUNT(*) INTO v_Count - FROM MRP_RUN_PURCHASELINE - WHERE M_PRODUCT_ID = Cur_Product.M_Product_ID - AND MRP_RUN_PURCHASE_ID = p_Run - AND inouttrxtype = 'MS'; - END IF; +BEGIN --BODY + -- Get Parameters + FOR Cur_Product IN (SELECT p.m_product_id, + COALESCE(po.stockmin, p.stockmin, 0) AS stockmin, + COALESCE(po.mrp_planningmethod_id, p.mrp_planningmethod_id) AS mrp_planningmethod_id + FROM m_product p LEFT JOIN m_product_org po ON p.m_product_id = po.m_product_id + AND po.ad_org_id = p_org_id + WHERE (p_product_id IS NULL OR p.m_product_id = p_product_id) + AND (p_product_category_id IS NULL OR p.m_product_category_id = p_product_category_id) + AND (p_planner_id IS NULL OR COALESCE(po.mrp_planner_id, p.mrp_planner_id) = p_planner_id) + AND AD_ISORGINCLUDED(p_org_id, p.ad_org_id, p_client_id) > -1 + AND p.ad_client_id = p_client_id + AND ((p_production = 'Y' AND p.ispurchased = 'N') OR (p_production = 'N' AND p.ispurchased = 'Y')) + AND p.isactive = 'Y' + AND (p_production = 'Y' OR + (p_vendor_id IS NULL + OR EXISTS (SELECT 1 + FROM m_product_po + WHERE m_product_po.m_product_id = p.m_product_id + AND m_product_po.c_bpartner_id = p_vendor_id + AND m_product_po.iscurrentvendor = 'Y' + AND m_product_po.isactive = 'Y' + AND m_product_po.discontinued = 'N' + ))) + AND (p_bpartner_id IS NULL + OR EXISTS (SELECT 1 + FROM c_order o, c_orderline ol + WHERE o.c_order_id = ol.c_order_id + AND o.c_bpartner_id = p_bpartner_id + AND o.issotrx = 'Y' + --AND C_Order_Status(o.C_ORDER_ID) IN (1, 2, 3) + AND o.processed = 'Y' + AND ol.qtyordered <> ol.qtydelivered + AND ol.m_product_id = p.m_product_id + AND ol.datepromised IS NOT NULL + AND MRP_CHECK_PLANNINGMETHOD( + COALESCE(po.mrp_planningmethod_id, p.mrp_planningmethod_id), + 'SO', TO_NUMBER(COALESCE(ol.datepromised, ol.dateordered, o.dateordered) - p_planningdate), p_timehorizon) <> -1 + AND AD_ISORGINCLUDED(o.ad_org_id, p_org_id, p_client_id) > -1) + OR EXISTS (SELECT 1 + FROM mrp_salesforecast sf, mrp_salesforecastline sfl + WHERE sf.mrp_salesforecast_id = sfl.mrp_salesforecast_id + AND sf.isactive = 'Y' + AND sf.c_bpartner_id = p_bpartner_id + AND sfl.m_product_id = p.m_product_id + AND MRP_CHECK_PLANNINGMETHOD( + COALESCE(po.mrp_planningmethod_id, p.mrp_planningmethod_id), + 'SF', TO_NUMBER(sfl.dateplanned - p_planningdate), p_timehorizon) <> -1 + AND AD_ISORGINCLUDED(sf.ad_org_id, p_org_id, p_client_id) > -1) + ) + AND (p_bp_group_id IS NULL + OR EXISTS(SELECT 1 + FROM c_order o, c_orderline ol, c_bpartner bp + WHERE o.c_order_id = ol.c_order_id + AND o.c_bpartner_id = bp.c_bpartner_id + AND o.issotrx = 'Y' + AND bp.c_bp_group_id = p_bp_group_id + --AND C_Order_Status(o.C_ORDER_ID) IN (1, 2, 3) + AND o.processed = 'Y' + AND ol.qtyordered <> ol.qtydelivered + AND ol.m_product_id = p.m_product_id + AND ol.datepromised IS NOT NULL + AND MRP_CHECK_PLANNINGMETHOD( + COALESCE(po.mrp_planningmethod_id, p.mrp_planningmethod_id), + 'SO', TO_NUMBER(COALESCE(ol.datepromised, ol.dateordered, o.dateordered) - p_planningdate), p_timehorizon) <> -1 + AND AD_ISORGINCLUDED(o.ad_org_id, p_org_id, p_client_id) > -1) + OR EXISTS (SELECT 1 + FROM mrp_salesforecast sf, mrp_salesforecastline sfl, c_bpartner bp + WHERE sf.mrp_salesforecast_id = sfl.mrp_salesforecast_id + AND sf.isactive = 'Y' + AND sf.c_bpartner_id = bp.c_bpartner_id + AND sfl.m_product_id = p.m_product_id + AND bp.c_bp_group_id = p_bp_group_id + AND MRP_CHECK_PLANNINGMETHOD( + COALESCE(po.mrp_planningmethod_id, p.mrp_planningmethod_id), + 'SF', TO_NUMBER(sfl.dateplanned - p_planningdate), p_timehorizon) <> -1 + AND AD_ISORGINCLUDED(sf.ad_org_id, p_org_id, p_client_id) > -1) + ) + ) LOOP + IF (p_Production = 'Y') THEN + SELECT COUNT(*) INTO v_count + FROM mrp_run_productionline + WHERE m_product_id = cur_product.m_product_id + AND mrp_run_production_id = p_run + AND inouttrxtype = 'MS'; + ELSE + SELECT COUNT(*) INTO v_count + FROM mrp_run_purchaseline + WHERE m_product_id = cur_product.m_product_id + AND mrp_run_purchase_id = p_run + AND inouttrxtype = 'MS'; + END IF; - -- Call M_GET_STOCK - DECLARE - v_pinstance_result AD_PInstance.result%TYPE; - v_pinstance_msg AD_PInstance.errormsg%TYPE; - BEGIN - v_AD_Pinstance_ID := GET_UUID(); - INSERT INTO AD_PINSTANCE ( - AD_PINSTANCE_ID, AD_PROCESS_ID, RECORD_ID, ISACTIVE, - AD_USER_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, - UPDATED, UPDATEDBY - ) VALUES ( - v_AD_Pinstance_ID,'FF80818132C964E30132C9747257002E',p_Run,'Y', - p_user_id, p_client_id, p_org_id, now(), p_user_id, - now(), p_user_id - ); + IF (v_Count > 0) THEN -- Product already initialized + CONTINUE; + END IF; - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '10', 'AD_Client_ID', p_client_id, null, null, null, null, null); - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '20', 'AD_Org_ID', p_org_id, null, null, null, null, null); - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '30', 'M_Product_ID', Cur_Product.M_Product_ID, null, null, null, null, null); - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '60', 'AuxID', p_Run, null, null, null, null, null); - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '70', 'TableId', '800219', null, null, null, null, null); - IF (p_production = 'Y') THEN - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '80', 'ProcessID', '800162', null, null, null, null, null); - ELSE - AD_PINSTANCE_PARA_INSERT(v_AD_Pinstance_ID, '90', 'ProcessID', '800164', null, null, null, null, null); - END IF; + -- Call M_GET_STOCK + DECLARE + v_pinstance_result AD_PInstance.result%TYPE; + v_pinstance_msg AD_PInstance.errormsg%TYPE; + BEGIN + v_ad_pinstance_id := GET_UUID(); + INSERT INTO ad_pinstance ( + ad_pinstance_id, ad_process_id, record_id, isactive, + ad_user_id, ad_client_id, ad_org_id, created, createdby, + updated, updatedby + ) VALUES ( + v_ad_pinstance_id,'FF80818132C964E30132C9747257002E',p_run,'Y', + p_user_id, p_client_id, p_org_id, now(), p_user_id, + now(), p_user_id + ); - M_GET_STOCK(v_AD_Pinstance_ID, 'N'); - -- Check result - SELECT result, errormsg - INTO v_pinstance_result, v_pinstance_msg - FROM ad_pinstance - WHERE ad_pinstance_id = v_ad_pinstance_id; - IF (v_pinstance_result = 0) THEN - -- Error on m_get_stock - RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg); - END IF; - END; -- End Call M_GET_STOCK + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '10', 'AD_Client_ID', p_client_id, null, null, null, null, null); + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '20', 'AD_Org_ID', p_org_id, null, null, null, null, null); + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '30', 'M_Product_ID', Cur_Product.M_Product_ID, null, null, null, null, null); + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '60', 'AuxID', p_Run, null, null, null, null, null); + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '70', 'TableId', '800219', null, null, null, null, null); + IF (p_production = 'Y') THEN + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '80', 'ProcessID', '800162', null, null, null, null, null); + ELSE + AD_PINSTANCE_PARA_INSERT(v_ad_pinstance_id, '90', 'ProcessID', '800164', null, null, null, null, null); + END IF; - SELECT COALESCE(SUM(quantity),0) INTO v_QtyOnHand - FROM M_STOCK_PROPOSED - WHERE AD_PINSTANCE_ID = v_AD_Pinstance_ID; + M_GET_STOCK(v_ad_pinstance_id, 'N'); + -- Check result + SELECT result, errormsg + INTO v_pinstance_result, v_pinstance_msg + FROM ad_pinstance + WHERE ad_pinstance_id = v_ad_pinstance_id; + IF (v_pinstance_result = 0) THEN + -- Error on m_get_stock + RAISE_APPLICATION_ERROR(-20000, v_pinstance_msg); + END IF; + END; -- End Call M_GET_STOCK - IF (v_Count = 0) THEN -- First time on this product - v_ResultStr := 'Inserting stock lines product: ' || Cur_Product.M_Product_ID; + SELECT COALESCE(SUM(quantity),0) INTO v_QtyOnHand + FROM m_stock_proposed sp + JOIN m_storage_detail sd ON sp.m_storage_detail_id = sd.m_storage_detail_id + JOIN m_locator l ON sd.m_locator_id = l.m_locator_id + JOIN m_warehouse w ON l.m_warehouse_id = w.m_warehouse_id + WHERE ad_pinstance_id = v_ad_pinstance_id + -- Only stock of warehouses in p_org_id and its childs. + AND AD_ISORGINCLUDED(w.ad_org_id, p_org_id, w.ad_client_id) <> -1; - Mrp_Run_Insertlines(p_Client_ID, p_Org_ID, p_User_ID, p_Run, Cur_Product.M_Product_ID, (-1 * Cur_Product.STOCKMIN), 0, NULL, 'MS', 'Y', NULL, NULL, NULL, NULL, NULL, p_PlanningDate, NULL, p_Production, (CASE WHEN Cur_Product.STOCKMIN > 0 THEN 'N' ELSE 'Y' END), NULL, v_Aux_ID); - Mrp_Run_Insertlines(p_Client_ID, p_Org_ID, p_User_ID, p_Run, Cur_Product.M_Product_ID, v_QtyOnHand, 0, NULL, 'ST', 'Y', NULL, NULL, NULL, NULL, NULL, p_PlanningDate, NULL, p_Production, 'Y', NULL, v_Aux_ID); + v_ResultStr := 'Inserting stock lines product: ' || Cur_Product.M_Product_ID; + MRP_RUN_INSERTLINES(p_client_id, p_org_id, p_user_id, p_run, cur_product.m_product_id, (-1 * cur_product.stockmin), 0, NULL, 'MS', 'Y', NULL, NULL, NULL, NULL, NULL, p_planningdate, NULL, p_production, (CASE WHEN cur_product.stockmin > 0 THEN 'N' ELSE 'Y' END), NULL, v_aux_id); + MRP_RUN_INSERTLINES(p_client_id, p_org_id, p_user_id, p_run, cur_product.m_product_id, v_qtyonhand, 0, NULL, 'ST', 'Y', NULL, NULL, NULL, NULL, NULL, p_planningdate, NULL, p_production, 'Y', NULL, v_aux_id); ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
