Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0014455Openbravo ERP06. Material requirement planning (MRP)public2010-09-07 10:182010-09-22 00:00
Reportergorkaion 
Assigned Toharikrishnan 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version2.50MP18 
Target VersionFixed in Version2.50MP22 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0014455: error when processing purchasing plan indicating Business Partner Group and Product Category.
DescriptionWhen you define a Purchasing Plan with Business Partner Group and Product Category, products taken into account are not properly filtered
Steps To Reproduce1.Material Requirement (MRP) || Setup || Planning Method || Header: create record. Go lines and create record:
  transaction type=pending sales order
  Weighting=1

2.Master Data Management || Product || Product >> Org Specific of Beer product and create record:
  panning method= your_planning_method
  Min. Quantity=1
  Safety Stock=1

3. Sales Management || Transactions || Sales Order || Header:create SO for Mcgiver with Scheduled Delivery Date=28/07/2010
Go to Lines and set product=hat and qty=20
Process SO.

4. Material Requirement (MRP) || Transactions || Purchasing Plan || Header:
  document date= 28-06-2010
  time horizon=60
  bp=mcgiver
  product category=food
and run "Process Purchase Plan"

Go to Lines and realize that product Beer has been taken into account when it shouldn't , because there is no pending Sales Order for any product of Food category
Proposed SolutionHaving a look at the code, the following change is required in function mrp_run_initialize:

FOR Cur_Product IN (SELECT p.M_Product_ID, COALESCE(sd.qtyonhand,0) AS qtyonhand,
                        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
                                         LEFT JOIN (SELECT M_Product_ID, SUM(qtyonhand) as qtyonhand
                                                    FROM M_STORAGE_DETAIL
                                                    GROUP BY M_Product_ID) sd ON p.M_Product_ID = sd.M_Product_ID
                        WHERE (p_product_ID IS NULL OR p.M_PRODUCT_ID = p_Product_ID)
              AND (p_product_ID IS NULL OR p.ISACTIVE = 'Y')
                          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_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 (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(ol.DatePromised - 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.DatePromised IS NOT NULL
                                           AND Mrp_Check_Planningmethod(
                                                    COALESCE(po.MRP_PlanningMethod_ID, p.MRP_PlanningMethod_ID),
                                                    'SO', TO_NUMBER(ol.DatePromised - 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 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
...
...

where

AND ol.m_product_id=p.m_product_id

and

AND sfl.m_product_id=p.m_product_id

clauses have been added
Additional Information
TagsNo tags attached.
Relationships
related to defect 00138162.50MP20 closed sivaraman error when processing purchasing plan indicating Business Partner and Product Category. 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-09-07 10:18gorkaionNew Issue
2010-09-07 10:18gorkaionAssigned To => sivaraman
2010-09-07 10:18gorkaionIssue generated from0013816
2010-09-07 10:18gorkaionRelationship addedrelated to 0013816
2010-09-09 12:10adrianromeroStatusnew => scheduled
2010-09-09 12:10adrianromerofix_in_branch => pi
2010-09-15 10:49harikrishnanAssigned Tosivaraman => harikrishnan
2010-09-15 10:51hgbotCheckin
2010-09-15 10:51hgbotNote Added: 0031073
2010-09-15 10:51hgbotStatusscheduled => resolved
2010-09-15 10:51hgbotResolutionopen => fixed
2010-09-15 10:51hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/66d4e6ca8cf6f04f5b240c32c45fc3fdb3500426 [^]
2010-09-15 10:52harikrishnanNote Added: 0031074
2010-09-15 20:55hudsonbotCheckin
2010-09-15 20:55hudsonbotNote Added: 0031127
2010-09-21 13:41psarobeNote Added: 0031277
2010-09-21 13:41psarobeStatusresolved => closed
2010-09-21 13:41psarobeFixed in Version => 2.50MP22
2010-09-22 00:00anonymoussf_bug_id0 => 3072973

Notes
(0031073)
hgbot   
2010-09-15 10:51   
Repository: erp/devel/pi
Changeset: 66d4e6ca8cf6f04f5b240c32c45fc3fdb3500426
Author: Harikrishnan Raja <harikrishnan.raja <at> openbravo.com>
Date: Wed Sep 15 14:20:26 2010 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/66d4e6ca8cf6f04f5b240c32c45fc3fdb3500426 [^]

Fixes Issue 14455: error when processing purchasing plan indicating Business Partner Group and Product Category.

---
M src-db/database/model/functions/MRP_RUN_INITIALIZE.xml
---
(0031074)
harikrishnan   
2010-09-15 10:52   
Steps to test:

1.Material Requirement (MRP) || Setup || Planning Method || Header: create record. Go lines and create record:
  transaction type=pending sales order
  Weighting=1

2.Master Data Management || Product || Product >> Org Specific of Beer product and create record:
  panning method= your_planning_method
  Min. Quantity=1
  Safety Stock=1

3.Sales Management || Transactions || Sales Order || Header:create SO for Mcgiver with Scheduled Delivery Date=28/07/2010
  Go to Lines and set product=hat and qty=20
  Process Sales Order

4.Material Requirement (MRP) || Transactions || Purchasing Plan || Header:
  document date= 28-06-2010
  time horizon=60
  select the business partner category of mcgiver.

5.Run "Process Purchase Plan" and verify if no lines created

Root Cause:
*Before this condition is not checked.

Impact:
*Their is no impact because of this issue.
(0031127)
hudsonbot   
2010-09-15 20:55   
A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated:

Changeset: http://code.openbravo.com/erp/devel/main/rev/66d4e6ca8cf6 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/80bfed94a6e8 [^]
Tests: http://builds.openbravo.com/view/int/ [^]
OBX: http://builds.openbravo.com/erp/core/obx/OpenbravoERP-2.50CI.18410.obx [^]
(0031277)
psarobe   
2010-09-21 13:41   
Tested working fine