The purpose of this post is to give your warehouse pick, pack and shipping departments better warehouse management tools for heaving shipping days. If you have ever worked in a high-volume warehouse, you appreciate the value of receiving advanced warning to fulfill large orders.
Using ADempiere and iDempiere (open source ERP), you keep your pick, pack and shipping crew as efficient as possible by helping them predict the shipping load across multiple days and/or weeks. If you are not familiar with open source warehouse management in ADempiere and iDempiere, let me know. I am happy to demonstrate how $100M+ organizations use open source ERP to manage multiple companies, distribution centers, manufacturing centers, and currencies.
How do you predict future pick, pack and shipping activity?
Let’s start by predicting future inventory levels for a given product and warehouse. Here are the forces that drive changes in inventory levels:
- Purchase Order – increase inventory based on expected receipt date
- Sales Order – decrease inventory based on expected pick date
- Manufacturing or Production Order – increase inventory of finished goods and decrease inventory of raw goods on a scheduled date.
- Distribution Order – increase inventory for the target warehouse and decrease inventory for the source warehouse.
Here is a PostgreSQL function that (a) takes a specific product, warehouse, and target date and (b) gives you the details for estimating quantity on-hand based the above criteria.
CREATE OR REPLACE FUNCTION prod_predictqty_table(mproduct_v numeric, mwarehouse_v numeric, asofdate_v date) RETURNS table(message text, documentno character varying, line numeric, ad_client_id numeric, ad_org_id numeric, m_product_id numeric, m_warehouse_id numeric, QtyContribute numeric) AS $$ --select * from prod_predictqty_table(7024012, 1000001, '2014-07-18') select * from ( SELECT 'Storage'::text as message, 'stor'::character varying as documentno, 0::numeric as line, s.ad_client_id, 0::numeric as ad_org_id, s.m_product_id, l.m_warehouse_id, sum(s.qtyonhand) as QtyContribute from m_storage s join m_locator l on (s.m_locator_id = s.m_locator_id) where s.m_product_id = $1 and l.m_warehouse_id = $2 group by s.m_product_id, l.m_warehouse_id, s.ad_client_id
SELECT 'Sales Order-', o.documentno, ol.line, ol.ad_client_id, 0::numeric as ad_org_id, ol.m_product_id, ol.m_warehouse_id, (ol.qtyreserved)*-1 as QtyContribute FROM c_orderline ol JOIN c_order o ON ol.c_order_id = o.c_order_id WHERE ol.qtyreserved <> 0::numeric AND o.issotrx = 'Y'::bpchar and trunc(ol.DateWanted_Pick) < $3 and trunc(o.dateordered) >= current_date and ol.m_product_id = $1 and ol.m_warehouse_id = $2 --group by ol.m_product_id, ol.m_warehouse_id, ol.ad_client_id UNION ALL
SELECT 'Purchase order+', o.documentno, ol.line, ol.ad_client_id, 0::numeric as ad_org_id, ol.m_product_id, ol.m_warehouse_id, (ol.qtyreserved) as QtyContribute FROM c_orderline ol JOIN c_order o ON ol.c_order_id = o.c_order_id WHERE ol.qtyreserved <> 0::numeric AND o.issotrx = 'N'::bpchar and trunc(ol.datepromised) < $3 and trunc(o.dateordered) >= current_date and ol.m_product_id = $1 and ol.m_warehouse_id = $2 --group by ol.m_product_id, ol.m_warehouse_id, ol.ad_client_id
SELECT 'Manufacturing Order+', o.documentno, 0 as line, o.ad_client_id, 0::numeric as ad_org_id, o.m_product_id, o.m_warehouse_id, (o.qtyreserved) as QtyContribute FROM pp_order o WHERE o.qtyreserved <> 0::numeric and trunc(o.datepromised) < $3 and trunc(o.dateordered) >= current_date and o.m_product_id = $1 and o.m_warehouse_id = $2 --group by o.m_product_id, o.m_warehouse_id, o.ad_client_id
SELECT 'Manufacturing Line-', o.documentno, bl.line, bl.ad_client_id, 0::numeric as ad_org_id, bl.m_product_id, bl.m_warehouse_id, (bl.qtyreserved) as QtyContribute FROM pp_order_bomline bl JOIN pp_order_bom b ON bl.pp_order_bom_id = b.pp_order_bom_id JOIN pp_order o ON b.pp_order_id = o.pp_order_id WHERE bl.qtyreserved <> 0::numeric and trunc(o.datepromised) < $3 and trunc(o.dateordered) >= current_date and bl.m_product_id = $1 and bl.m_warehouse_id = $2 --group by bl.m_product_id, bl.m_warehouse_id, bl.ad_client_id
SELECT 'Distribution Order+', d.documentno, dl.line, dl.ad_client_id, 0::numeric as ad_org_id, dl.m_product_id, dl.m_warehouse2_id, (dl.qtyreserved)*-1 as qtyContribute FROM dd_orderline dl JOIN dd_order d ON dl.dd_order_id = d.dd_order_id WHERE dl.qtyreserved <> 0::numeric and trunc(dl.datepromised) < $3 and trunc(dl.dateordered) >= current_date and dl.m_product_id = $1 and dl.m_warehouse2_id = $2 --group by dl.m_product_id, dl.m_warehouse2_id, dl.ad_client_id
SELECT 'Distribution Order-', d.documentno, dl.line, dl.ad_client_id, 0::numeric as ad_org_id, dl.m_product_id, dl.m_warehouse_id, (dl.qtyreserved)*-1 as qtyContribute FROM dd_orderline dl JOIN dd_order d ON dl.dd_order_id = d.dd_order_id WHERE dl.qtyreserved <> 0::numeric and trunc(dl.datepromised) < $3 and trunc(dl.dateordered) >= current_date and dl.m_product_id = $1 and dl.m_warehouse_id = $2 --group by dl.m_product_id, dl.m_warehouse_id, dl.ad_client_id
) x $$ LANGUAGE SQL;
The above function is great for auditing the accuracy of your prediction, but what you really want is a nice, clear, concise quantity as of a given date. Here is the function that transforms the above detail into your simple answer:
CREATE OR REPLACE FUNCTION prod_predictqty_sum(mproduct_v numeric, mwarehouse_v numeric, asofdate_v date) RETURNS numeric AS $$ --select prod_predictqty_sum(7024012, 1000001, '2013-07-18')
select sum(qtycontribute) from prod_predictqty_table($1, $2, $3)
$$ LANGUAGE SQL;
Once you can predict future inventory levels, you can apply this knowledge to any sales order line by comparing the order line quantity with the expected quantity on hand. From here, you can predict if a given sales order will ship based on delivery rules (Ship Order Complete, Ship Line Complete, Ship as Received, etc…). Once you apply the order’s pick date, you can create a queue that tells a specific pick, pack and shipping department what to expect on a given day.
|Date||Order Count||Unit Count||Outlier Count|
As you start to refine your system’s ability to predict load, more and more insight will be pulled from your ERP. As an example, the above concepts give your planning department real-time feedback about order changes and stock levels. Your planning department can now see the same information available to distribution center manager, and vise verse.
If you would like help integrating the above tools into your ERP, or if you would like more information about using iDempiere and ADempiere, Open Source ERP, to manage your distribution or manufacturing business, contact me. I am happy to help!
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
Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.
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.