SELECT ‘<<item number>>’ AS PARENT_ITEM,
a.item_number,
LEVEL
FROM AGILE.BOM A
START WITH (A.ITEM = (SELECT DISTINCT i.id
FROM agile.item i, agile.bom b
WHERE i.id = b.item AND i.item_number = ‘<<item number>>’)
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);