Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0023262Openbravo ERP04. Warehouse managementpublic2013-03-07 18:312013-03-11 09:45
ReporterVictorVillar 
Assigned Todmiguelez 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0MP22Fixed in Version 
Merge Request Status
Review Assigned ToVictorVillar
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0023262: Wrong pending qty suggested by purchasing plan
DescriptionWrong pending qty suggested by purchasing plan
Steps To ReproduceAs group admin role,
Go to Preference window:
   Add 'Enable Stock Reservation'
   Value: 'Y'
Go to Product window
   Create one product
   Org: *
 Price Lsit tab:
    Add one for saling and one for purchasing.
 Purchasing tab:
    Select one vendor for US.

Go to Purchase order:
  Create one for US
 add the line with the product created before
 qty : 100
Complete it

Go to Sales order
  Create a new one with the prodcut created before
  qty: 20
Complete it.
Click on 'Manage Reservation'
  Select the line proposed and qty = 20
Done.

Go to Purchasing plan
 Launch the process for the product created before.
Realise that the pending qty proposed is: 80, it is OK

Go to Goods receipt
  Create one for the Purchase Order created before.
  Qty: 20
 Complete it.

Go to Purchasing plan window again:
  Remove the lines created before.
  Launch again the purchasing plan:
Realise that the pending qty proposed is: 60 Qty
It is wrong, oit should be 80


Proposed SolutionModify the mrp_run_initialize, the correct query for the cursor should be:

 FOR Cur_OrderLine IN (
        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.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
              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
              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
         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 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

Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2013-03-07 18:31VictorVillarNew Issue
2013-03-07 18:31VictorVillarAssigned To => dmiguelez
2013-03-07 18:31VictorVillarModules => Core
2013-03-07 18:31VictorVillarResolution time => 1365631200
2013-03-07 18:31VictorVillarTriggers an Emergency Pack => No
2013-03-07 18:31VictorVillarIssue Monitored: networkb
2013-03-08 16:26hgbotCheckin
2013-03-08 16:26hgbotNote Added: 0057162
2013-03-08 16:26hgbotStatusnew => resolved
2013-03-08 16:26hgbotResolutionopen => fixed
2013-03-08 16:26hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/6e8a00ed9b4f440371b662c6d93f3a23f1414bf3 [^]
2013-03-11 09:45VictorVillarReview Assigned To => VictorVillar
2013-03-11 09:45VictorVillarNote Added: 0057169
2013-03-11 09:45VictorVillarStatusresolved => closed

Notes
(0057162)
hgbot   
2013-03-08 16:26   
Repository: erp/devel/pi
Changeset: 6e8a00ed9b4f440371b662c6d93f3a23f1414bf3
Author: Unai Martirena <unai.martirena <at> openbravo.com>
Date: Fri Mar 08 16:25:24 2013 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/6e8a00ed9b4f440371b662c6d93f3a23f1414bf3 [^]

Fixes Issue 23262: Wrong pending qty suggested by purchasing plan

---
M src-db/database/model/functions/MRP_RUN_INITIALIZE.xml
---
(0057169)
VictorVillar   
2013-03-11 09:45   
Verified