Multiple BOM per Product

The purpose of this page is to offer a solution to help you create multiple BOMs for a single Product. Why is this important:

There are times when you need to draft a BOM change before it is put into production.
There are time when you might want to have a preferred BOM with alternates for concepts like rework.

Goals in creating the solution:

Make the solution as flexible as possible to meet the myriad of reasons why someone might want multiple BOMs.
Make the solution play with existing iDempiere without modification (BOM Cost Rollup, Production, etc...)
Make the solution easy to extend to solve more complex scenarios. Example, specifying the BOM in the Production window to support alternative BOMs/Routes on a per-Production basis.

Solution Summary:

Make a backup of M_Product_BOM table.
Remove all dependencies on M_Product_BOM.
Drop M_Product_BOM table.
Create replacement BOM Header and Lines (allows for multiple BOMs per iDempiere Product)
Create M_Product_BOM view (based on newly created BOM tables) that is an exact duplicate of the original M_Product_BOM table.

Solution Details:

--Create temporary table
- create table delme_product_bom as select * from m_product_bom;
--update m_packagelines_avail_v view to use delme_product_bom instead of m_product_bom
- --use your favorite editor to accomplish this task
-- drop table so that we can create the replacement view
- drop table m_product_bom;
-- create replacement view from temp table
- -- note: you will update this view later to make it depend on real tables
- create view m_product_bom as select * from delme_product_bom;
-- create chuboe_product_bom and chuboe_product_bomline tables (header) and "Chuboe BOM" window
- -- link to pack in
-- create a unique index to limit having more than one preferred BOM per product
- CREATE UNIQUE INDEX chuboe_bom_preferred_idx ON chuboe_product_bom USING btree (m_product_id) WHERE ischuboebompreferred = 'Y'::bpchar
-- create the replacement M_Product_BOM view - see below
--update m_packagelines_avail_v view to use m_product_bom instead of delme_product_bom (unwinds previous edit)
- --use your favorite editor to accomplish this task
-- drop temp table
- -- Only do this if you do not need pre-existing created BOM records.
- -- DROP TABLE delme_product_bom;
-- If you wish to use the iDempiere => Universal Substitution process, you will need to update the process (by creating a plugin) to use your newly created BOM tables.
-- Everything else should magically work!!

Replacement M_Product_BOM View:

CREATE OR REPLACE VIEW adempiere.m_product_bom AS
 SELECT cbl.chuboe_product_bomline_id AS m_product_bom_id,
 cbl.ad_client_id,
 cbl.ad_org_id,
 cbl.isactive,
 cbl.created,
 cbl.createdby,
 cbl.updated,
 cbl.updatedby,
 cbl.line,
 cb.m_product_id,
 cbl.m_productbom_id,
 cbl.bomqty,
 cbl.description,
 cbl.bomtype,
 cbl.chuboe_product_bomline_uu AS m_product_bom_uu
 FROM adempiere.chuboe_product_bom cb
 JOIN adempiere.chuboe_product_bomline cbl ON cb.chuboe_product_bom_id = cbl.chuboe_product_bom_id
 WHERE cb.ischuboebompreferred='Y'
;

Other Considerations:

I added R_Status_ID to the BOM header as a generic way to indicate the BOM's status. You will need to create a specific Request Status (R_StatusCategory_ID) to limit the Status list to your specific options. The easiest way is to create dynamic validation that limits the R_Status_ID field to your specific list. Feel free to join open discussion to perform this task together.
The above script does not migrate existing BOM data.
I did not modify the existing Product window. You will need to either (a) remember to modify BOM in Chuboe BOM or (b) modify the Product window as you deem appropriate to include Chuboe BOM details. You might want to create a zoom from the existing M_Product_BOM to the Chuboe_Product_BOMLine record using ColSQL.