0023262: Wrong pending qty suggested by purchasing plan

Description

Wrong pending qty suggested by purchasing plan

Steps To Reproduce

As 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 Solution

Modify 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

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0057162) hgbot (developer) 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 (viewer) 2013-03-11 09:45	Verified

Issue History
Date Modified	Username	Field	Change
2013-03-07 18:31	VictorVillar	New Issue
2013-03-07 18:31	VictorVillar	Assigned To	=> dmiguelez
2013-03-07 18:31	VictorVillar	Modules	=> Core
2013-03-07 18:31	VictorVillar	OBNetwork customer	=> Yes
2013-03-07 18:31	VictorVillar	Support ticket	=> 19937
2013-03-07 18:31	VictorVillar	Resolution time	=> 1365631200
2013-03-07 18:31	VictorVillar	Triggers an Emergency Pack	=> No
2013-03-07 18:31	VictorVillar	Issue Monitored: networkb
2013-03-08 16:26	hgbot	Checkin
2013-03-08 16:26	hgbot	Note Added: 0057162
2013-03-08 16:26	hgbot	Status	new => resolved
2013-03-08 16:26	hgbot	Resolution	open => fixed
2013-03-08 16:26	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/6e8a00ed9b4f440371b662c6d93f3a23f1414bf3 [^]
2013-03-11 09:45	VictorVillar	Review Assigned To	=> VictorVillar
2013-03-11 09:45	VictorVillar	Note Added: 0057169
2013-03-11 09:45	VictorVillar	Status	resolved => closed