Overview
Bill of materials (BOM) is a centralized source of information used to manufacture a product. It is a list of the items needed to create a product as well as the instructions on how to assemble that product. At times one of the BOM will be an end product itself. So it comes to second level hierarchy.
Technologies and Tools Used
The following technology has been used to achieve this in oracle.
Use Case
It can be used in all ERP applications. BOMs are displayed in Screen Dashboards, Item Details for Production Planning.
Create a Product Table
Creating a product table with product_id, product_name.
Create table Product(Product_ID NUMBER,
Product_Name VARCHAR2(100));
Create a BOM Table
Creating a BOM Table to list the items against the product.
Create table BOM(Item_ID NUMBER,
Item_Name VARCHAR2(200),
Quantity NUMBER,
Parent_Item_ID NUMBER);
Populate data to two tables
We have created two tables. Now we are going to add data into it.
insert into Product values (1,’COMPUTER’) ;
insert into Product values (1,’CPU’) ;
insert into BOM values (1,’COMPUTER’,1,NULL);
insert into BOM values (2,’CPU’,1,1);
insert into BOM values (3,’MONITOR’,1,1);
insert into BOM values (4,’KEYBOARD’,1,1);
insert into BOM values (5,’MOUSE’,1,1);
insert into BOM values (6,’SPEAKER’,2,1);
insert into BOM values (7,’MOTHERBOARD’,1,2);
insert into BOM values (8,’GRAPHICS CARD’,1,2);
insert into BOM values (9,’OUTER CASE’,1,2);
Build the query.
SELECT item_name, quantity, parent_item_id, level
FROM bom
START WITH parent_item_id IS NULL
CONNECT BY PRIOR item_id = parent_item_id
ORDER BY level;
Level is the pseudocolumn to show parent and child rows.
CONNECT BY clause to define the relationship between Item ID and Parent Item ID.
The result of the query will be as follows:
