Multi - level BOM - building model

[pawelj795]

Hi,
Currently, I'm working on a project when I need to calculate product manufacturing costs from the lowest level.
Unfortunately, I'm struggling to create a proper data model from my database.

I have some products which have 3 breakdown levels.

My goal is to create a table in my data model, which looks like below.

Example:

Product	Intermediate products (level 1)	Intermediate products (level 2)	Materials
Product A	Subproduct B	Subproduct C	Material E
Product A	Subproduct B	Subproduct D	Material F

In my data model, I have 4 tables:

• mrp_bom - > Defines bills of material for a product or a product template
• mrp_bom_line - > Contains every line for a specific BOM
• product_template -> Defines product templates
• product_product -> Contains variants of product templates

Important columns:

• code - > index number 
• parent_product_tmpl_id - > product template number of the parent level (1 level +)

I don't know how to start connecting my relationships properly.
This multi-level hierarchy scares me a little bit.
Maybe I need to create some bridge tables?
I don't expect you to fully complete my task, even some tips would be awesome.

If something is not clear to you, please ask and I will kindly clarify.

Below is my sample data.

If that helps, I'm using the Odoo ERP data model.

https://drive.google.com/file/d/1BF_V-ZMXdF7mFMdNhrqa0hIBdi2Qr-ff/view?usp=sharing

*I'm aware that some values in my model are "TRIAL" - it is only temporary

Any help will be much appreciated 😉

Thanks in advance!

[pawelj795]

.

[pawelj795]

Hi @Anonymous,

Thanks for weighing in.

I try to explain everything in the simplest way.
First text, then specific example.

• Column ID from mrp_bom are the final BOM number (every BOM)
• Column ID from mrp_bom_line are BOM line number (components).
  Every bom line has his product_id.
  Every bom line has his parent_product_tmpl_id.
• Column ID from product ID are item number.
  Every item has his product_tmpl_id .

Look at the example below, I hope this will help to better understand my problem.
This is the product: 1-2-0431-0231, bom line id: 49891.

This is the first-level component 241192. 

His product template is 8562. 

His parent product template is 9046. (1 level above, in this case, the main product)

(This component is broken down into the next 2 levels)

The next level is the second-level component with the parent product template 8562.

One of these second-level component breaks down into the third - level with the parent product template 8653.

This is the final level.

My goal is to create a table, which will look like below.

Product	I level component	II level components	III level components
A	B	C1	D1
A	B	C2
A	B	C3	D2

How to achieve this?

[Anonymous]

@pawelj795 

I have checked the pbix, not clear of what are the corresponding column to the example you gave. 

Product

Intermediate products (level 1)

Intermediate products (level 2)

Materials

I guess there just random data, but according to the current tables, I would recommend you to create relationships like the following:

Best Regards

Paul Zheng