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.
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.
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'
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.
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.
Important ‘Prepared’ Concept
The above view makes use of iDempiere’s Document Status (DocStatus) field. Specifically, it assumes that a document whose DocStatus = ‘Prepared’ has reached high enough a level of approval where the inventory should be reserved. Said another way, setting a Production work order to a status of ‘Prepared’ will cause the work order’s finished good to show in the ‘Qty On Order’ bucket. The raw goods will show in the ‘Qty Reserved’ bucket.
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.
While I am Asking – Reservations Need a Locator
While I am wishing, iDempiere needs to bring back the option to tie a ordered or reserved qty to a locator. Here are two examples where the locator is critical:
- Webstore integration where you also have a Retail presence. It is much easier to use multiple locators (as opposed to multiple warehouses) to manage retail vs webstore vs shared stock. The reason is that most order document types (PO, SO) allow for only one warehouse per order. If you can reserve based on locator, you can protect the correct products.
- Non conformant Material – there are times when you receive non-conformant material, and you need to move them to a locator flagged or named as such. This solution gives you the ability to reserve (and therefore protect) this material from being picked in the future.
I hope this helps!
What is the best way to Learn iDempiere and ADempiere?
I teach an on-line class that covers how to learn, configure and audit open source ERP. It uses iDempiere as the reference ERP. Here are more details. I have learned much over the last ten years, and I have much to share. I look forward to seeing you there!!
Why consider Open Source ERP
Open source ERP gives you every opportunity to prove or disprove its ability to support your company’s ERP needs on a timeline that satisfies your organizational needs. With open source ERP, you do not face the same financial constraints nor do you face the same conflicts of interest as with commercial ERP. Instead, you invest in the appropriate skills and knowledge for your people and processes. Best of all – if open source ERP cannot solve your company’s needs, you can safely justify spending the additional $2K to $5K per person per year for life of your commercial ERP to help drive your organization’s success.
ADempiere vs iDempiere vs Openbravo vs Compiere
The ADempiere, iDempiere, Openbravo and Compiere environments are amazingly similar. iDempiere came from ADempiere. ADempiere and Openbravo came from Compiere. Compiere came from Jorg Janke. Jorg came from Oracle. As a result, iDempiere and ADempiere have much in common with Oracle’s ERP in terms of the financial feature set.
This is both good and bad. Good because iDempiere and ADempiere are quite capable to help a company grow beyond $500M USD. Bad because they tend to be more complex in that they account for multiple languages, accounting schemas, currencies, calendars, costing types, costing methods, etc…. If you are a growing organization, and you need a system that will grow with you, and you have the right internal talent/resources, iDempiere or ADempiere will be a big asset for you.
The biggest difference between these products is that ADempiere and iDempiere are pure open source. ADempiere and iDempiere make all feature available for free. Compiere and Openbravo hold back features behind a commercial or paid license.
iDempiere and ADempiere vs OpenERP
iDempiere/ADempiere (iD/AD) and OpenERP approach ERP from two very different directions. OpenERP comes out of the box with very simple options. If you are coming from QuickBooks, and you need a simple ERP system help you manage your business, OpenERP will look and feel comfortable.
iD/AD comes out of the box with every feature installed and configured to run a $200M+ USD business. If your business is growing rapidly, and you are willing to invest the time to learn an enterprise accounting system, then iD/AD will give you confidence.
Which one is best for you depends on your internal talent, growth and business complexity. Here is a post to help you learn more.
About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.
If you have questions, comments or concerns, let me know. I definitely want your feedback.
You can contact me by phone using 512.850.6068.
My email is firstname.lastname@example.org.
You can complete the form on this page.
Thank you for taking the time. I look forward to speaking with you.