Bill of Material Query in Oracles Apps using Connect by Prior Clause
SELECT (SELECT msi.segment1
FROM mtl_system_items msi
WHERE msi.inventory_item_id = bom.assembly_item_id
AND msi.organization_id = 3) parent_item,
bom.assembly_item_id,
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 = 3) child_item,
bic.component_item_id child_item_id,
--bic.component_sequence_id ,
bic.bill_sequence_id,
--parent_bill_seq_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 = 3) bom
WHERE bom.bill_sequence_id = bic.bill_sequence_id
START WITH bom.assembly_item_id = 1734
CONNECT BY PRIOR bic.component_item_id = bom.assembly_item_id
No comments:
Post a Comment