SELECT i.item_number As “Parent Item”,
a.item_number As “Component”,
LEVEL
FROM AGILE.BOM A,agile.item i
where a.item=i.id and level=1
START WITH (A.ITEM In (SELECT DISTINCT i.id
FROM agile.item i, agile.bom b
WHERE i.id = b.item AND i.item_number in (‘500040-505’))
AND NVL (A.change_out, 0) = 0
AND (SUBSTR (A.flags, 5, 1) = 1)
AND A.id NOT IN (SELECT C.prior_bom
FROM AGILE.bom C
WHERE C.item = A.item))
CONNECT BY PRIOR A.COMPONENT = A.ITEM
AND NVL (A.change_out, 0) = 0
AND (SUBSTR (A.flags, 5, 1) = 1)
AND A.id NOT IN (SELECT B.prior_bom
FROM AGILE.bom B
WHERE B.item = A.item);