BOM Explosion Query


SELECT level ,
msi1.segment1 AS "Parent",
case when msi1.PLANNING_MAKE_BUY_CODE=1 then
'Make'
when msi1.PLANNING_MAKE_BUY_CODE=2 then
'Buy'
else
null
end PLANNING_MAKE_BUY_CODE,
LPAD (' ', 10 * (LEVEL - 1)) || msi2.segment1 AS "Component",
bic.COMPONENT_QUANTITY AS "Component Quantity",
case when msi2.PLANNING_MAKE_BUY_CODE=1 then
'Make'
when msi2.PLANNING_MAKE_BUY_CODE=2 then
'Buy'
else
null
end PLANNING_MAKE_BUY_CODE,
--LEVEL + 1,
SYS_CONNECT_BY_PATH (msi1.segment1, ' / ') || ' / ' || msi2.segment1
"Full Structure"
FROM apps.bom_inventory_components bic,
(select * from apps.bom_bill_of_materials where organization_id =&Organization_id )bom,
apps.mtl_system_items msi1,
apps.mtl_system_items msi2
WHERE bom.bill_sequence_id = bic.bill_sequence_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND msi1.organization_id = bom.organization_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = bom.organization_id
START WITH bom.assembly_item_id IN
(SELECT msib.inventory_item_id
FROM apps.mtl_system_items_b msib
WHERE msib.segment1=&Item_number
AND msib.organization_id = &Organization_id )
CONNECT BY NOCYCLE PRIOR bic.component_item_id = bom.assembly_item_id

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s