Indented Bills of material query in oracle apps

Indented Bills of material query in oracle apps

In this post , We will be discuss about the Indented Bills of material query in oracle apps. Indented BOM query , means if we have BOM item , whose components itself as an BOM.  Under BOM component , it self we are using the BOM component so if we need to extract the Bill of material details for that item , then We need to extract the components at three level and this is called Indented Bills of material (BOM). Here in this post , I will help to share the sql query to extract the Indented Bills of material in oracle apps.

 

 

 

Detail Level Indented Bills of material query in oracle apps

select
bom.assembly_item_id,

(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=85) parent_item,

lpad(' ',2*(level-1),' ')||

(select msi.segment1 from mtl_system_items msi where

msi.inventory_item_id=bic.component_item_id and msi.organization_id=207) child_item,

bic.component_item_id child_item_id, ,

bic.bill_sequence_id , ,

bic.operation_seq_num ,

bic.bom_item_type ,

bic.item_num ,

level,

 

bic.component_quantity

from

bom_inventory_components bic,

(select * from bom_bill_of_materials where organization_id=85) bom

where bom.bill_sequence_id=bic.bill_sequence_id

start with bom.assembly_item_id=:P_BOM_ITEM_ID

connect by prior bic.component_item_id=bom.assembly_item_id;