iDempiere Open Source ERP Production Ordered and Reserved to Storage

Hi Everyone,

The purpose of this post is to teach you how to include iDempiere Production quantities on the Product Info window.

Why You Care

If you manufacture or assemble goods, you might use iDempiere’s Production window to issue raw goods, labor and overhead to create/receive a finished good. One challenge with the Production window is that it does not tell iDempiere’s storage system that the raw goods are on reserve and the finished good is on order. As a result, order entry and planning departments are left in the dark unless they dig through open Production records.

The Solution

Updating the below view fixes this issue. By unioning in non-processed Production Lines into the M_Storage view, the system now knows about production reserved and ordered quantities.

The View

Create or Replace View M_Storage as
SELECT s.m_product_id, s.m_locator_id, s.ad_client_id, s.ad_org_id, s.isactive, s.created, s.createdby, s.updated, s.updatedby, s.qtyonhand, 0 AS qtyreserved, 0 AS qtyordered, s.datelastinventory, s.m_attributesetinstance_id, s.m_storageonhand_uu AS m_storage_uu, s.datematerialpolicy
FROM m_storageonhand s
UNION ALL 
SELECT sr.m_product_id, w.m_reservelocator_id AS m_locator_id, sr.ad_client_id, sr.ad_org_id, sr.isactive, sr.created, sr.createdby, sr.updated, sr.updatedby, 
	0 AS qtyonhand, sr.qty AS qtyreserved, 0 AS qtyordered, sr.datelastinventory, sr.m_attributesetinstance_id, sr.m_storagereservation_uu AS m_storage_uu, 
	NULL::timestamp without time zone AS datematerialpolicy
FROM m_storagereservation sr
	JOIN m_warehouse w ON sr.m_warehouse_id = w.m_warehouse_id
WHERE sr.issotrx = 'Y'::bpchar AND sr.qty <> 0::numeric
UNION ALL 
SELECT so.m_product_id, w.m_reservelocator_id AS m_locator_id, so.ad_client_id, so.ad_org_id, 
	so.isactive, so.created, so.createdby, so.updated, so.updatedby, 
	0 AS qtyonhand, 0 AS qtyreserved, so.qty AS qtyordered, 
	so.datelastinventory, so.m_attributesetinstance_id, so.m_storagereservation_uu AS m_storage_uu, 
	NULL::timestamp without time zone AS datematerialpolicy
FROM m_storagereservation so
	JOIN m_warehouse w ON so.m_warehouse_id = w.m_warehouse_id
WHERE so.issotrx = 'N'::bpchar AND so.qty <> 0::numeric
UNION ALL
Select pl.m_product_id, pl.m_locator_id, pl.ad_client_id, pl.ad_org_id, 
	'Y'::character(1) as isactive, pl.created, pl.createdby, pl.updated, pl.updatedby,
	0::numeric as Qtyonhand, 
	case when pl.isendproduct = 'N' then pl.qtyused else 0 end as qtyreserved,
	case when pl.isendproduct = 'Y' then pl.plannedqty else 0 end as qtyordered,
	null::timestamp without time zone as datelastinventory, pl.m_attributesetinstance_id, pl.m_productionline_uu as m_storage_uu, 
	NULL::timestamp without time zone AS datematerialpolicy
from M_ProductionLine pl
join m_production p on (pl.m_production_id = p.m_production_id)
where p.iscreated = 'Y' and p.processed = 'N'

What Changed

The M_Storage view already exists in iDempiere. The above statement modifies M_Storage by adding lines 19 through 29 (the last union all). This change brings production into the qty ordered and qty reserved totals.

Assumptions

This change assumes that when you create your Production Lines, you are ready to place all products on order and/or on reserve. They will stay this way until you complete/process the production order.

What about ADempiere

Unfortunately, this fix does not work on ADempiere. ADempiere’s M_Strorage is a table (not a view). iDempiere changed how storage works by separating M_Storage into M_StroageOnHand and M_StorageReservation. The iDempiere M_Storage view was created to preserve existing functionality.

What about Product Info Available to Promise Tab

Most of the Product Info’s tabs and fields will be updated as a result of this change. However, Production orders will not show up in the Available to Promise tab. If you need to include Production order here, you need to modify the InfoProductPanel.initAtpTab method to union in Production details.

Is this a good Solution

Not really. There are places in the code where it uses M_StorageReservation directly instead of using the M_Storage view. Because of this situation, M_StorageReservation has no knowledge of Production orders.

Having said the above, it is much better than no insight at all.

A Better Solution

If I were capable of re-writing the M_StorageReservation code, I would change the architecture by forcing each record that creates a reserved or ordered qty to have its own record in M_StorageReservation. The M_StorageReservation record would be identified by its AD_Table_ID and Record_ID.

For example, a prepared Sales Order would create a new record in M_StorageReservation. Only that sales order could modify that record. When the Sales Order is closed, the order deletes its records.

The benefits of this change include:

• Accuracy and audit – you can now easily audit the accuracy of M_StorageReservation. Anyone who makes heavy use of a replenish process knows how incredibly important it is to have accurate reserved and ordered qty.
• Smarter – you can make more advanced decisions when running replenish because you can itemize the individual dates and qty of your orders and reservations. More specifically, The system could more accurately set you Purchase Order and Production order Date Promised fields.
• Faster – M_StorageReservation would perform better because it would only include the current reservations.
• Easier to add new functionality – enhancements like the one mentioned above would be easier because you have less risk of contaminating qty ordered and qty reserved. The current architecture shows a combined total for a given unique combination. If you miscalculate (create a bug), you can easily grow your qty ordered and qty reserved because you are always writing differential amounts.

iDempiere does not show the ‘Prepare It’ Doc Action on some documents (like Inventory Move). To force the system to show the Prepare It action, follow this tutorial in the ERP Academy.