Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0013816Openbravo ERP06. Material requirement planning (MRP)public2010-06-28 18:202010-09-07 10:18
Reporternetworkb 
Assigned Tosivaraman 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version2.50MP18 
Target Version2.50MP20Fixed in Version 
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
Summary0013816: error when processing purchasing plan indicating Business Partner and Product Category.
DescriptionWhen you define a Purchasing Plan with Business Partner 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 0014455 closed harikrishnan error when processing purchasing plan indicating Business Partner Group and Product Category. 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-06-28 18:20networkbNew Issue
2010-06-28 18:20networkbAssigned To => adrianromero
2010-07-05 13:31adrianromeroStatusnew => scheduled
2010-07-05 13:31adrianromerofix_in_branch => pi
2010-07-13 12:11sivaramanAssigned Toadrianromero => sivaraman
2010-07-13 12:29hgbotCheckin
2010-07-13 12:29hgbotNote Added: 0029253
2010-07-13 12:29hgbotStatusscheduled => resolved
2010-07-13 12:29hgbotResolutionopen => fixed
2010-07-13 12:29hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c358c1e82f105cbf3fe234df292bcb481bf81a8d [^]
2010-07-13 12:36sivaramanNote Added: 0029254
2010-07-14 04:44hudsonbotCheckin
2010-07-14 04:44hudsonbotNote Added: 0029294
2010-07-23 09:28sureshbabuNote Added: 0029494
2010-07-23 09:28sureshbabuStatusresolved => closed
2010-07-24 00:00anonymoussf_bug_id0 => 3033804
2010-09-06 12:21gorkaionNote Added: 0030638
2010-09-06 12:21gorkaionStatusclosed => new
2010-09-06 12:21gorkaionResolutionfixed => open
2010-09-07 10:18gorkaionIssue cloned0014455
2010-09-07 10:18gorkaionRelationship addedrelated to 0014455
2010-09-07 10:18gorkaionStatusnew => closed
2010-09-07 10:18gorkaionResolutionopen => fixed

Notes
(0029253)
hgbot   
2010-07-13 12:29   
Repository: erp/devel/pi
Changeset: c358c1e82f105cbf3fe234df292bcb481bf81a8d
Author: Sivaraman Rajagopal <sivaraman.rajagopal <at> openbravo.com>
Date: Tue Jul 13 15:58:25 2010 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/c358c1e82f105cbf3fe234df292bcb481bf81a8d [^]

Fixes issue 13816: error when processing purchasing plan indicating Business Partner and Product Category

Two new AND clause has been introduced in order to make sure the lines created are belongs to the selected product category

---
M src-db/database/model/functions/MRP_RUN_INITIALIZE.xml
---
(0029254)
sivaraman   
2010-07-13 12:36   
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
  bp=mcgiver
  product category=food
  Business Partner Category=null

5.Run "Process Purchase Plan" and verify if no lines created
(0029294)
hudsonbot   
2010-07-14 04:44   
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/c358c1e82f10 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/2a91111beef4 [^]
Tests: http://builds.openbravo.com/view/int/ [^]
OBX: http://builds.openbravo.com/erp/core/obx/OpenbravoERP-2.50CI.17878.obx [^]
(0029494)
sureshbabu   
2010-07-23 09:28   
verified..
(0030638)
gorkaion   
2010-09-06 12:21   
The clauses have been added to the bpartner case, but not for the bp_group not null case.