BOM SQL

SELECT m.segment1 assembly_item,
b.assembly_item_id,
b.bill_sequence_id assembly_bom_seqid,
m.segment1 component_item,
c.component_item_id,
c.bill_sequence_id component_bom_seqid
FROM bom_bill_of_materials b,
mtl_system_items m,
bom_inventory_components c,
mtl_system_items m1
WHERE b.organization_id = :org_id
AND b.assembly_item_id = m.inventory_item_id
AND c.component_item_id = m1.inventory_item_id
AND m1.organization_id = m.organization_id
and c.bill_sequence_id = b.bill_sequence_id
AND m.planning_make_buy_code = 1
AND m.enabled_flag = 'Y'
AND m.organization_id = b.organization_id
AND alternate_bom_designator IS NULL