SELECT
–a.item,
LEVEL,
NVL(N1.DESCRIPTION,”) AS “Part Type / Document Type”,
I1.ITEM_NUMBER,
I1.DESCRIPTION AS “Part Description”,
NVL (N2.DESCRIPTION, ‘Preliminary’) AS “Lifecycle Phase”,
I2.ITEM_NUMBER AS “Part Number / Item Number”,
A.QUANTITY AS “QTY”,
NVL (N3.DESCRIPTION, ‘Preliminary’) AS “Lifecycle Phase1”,
I2.DESCRIPTION AS “Part Description1”
FROM AGILE.BOM A,AGILE.ITEM I1,AGILE.NODETABLE N1,AGILE.NODETABLE N2,AGILE.REV R,AGILE.ITEM I2,AGILE.NODETABLE N3,AGILE.REV R1
WHERE A.ITEM=I1.ID(+)
AND I1.SUBCLASS=N1.ID(+)
AND I1.ID = R.ITEM(+) AND I1.DEFAULT_CHANGE = R.CHANGE(+)
AND R.RELEASE_TYPE = N2.ID(+)
AND A.COMPONENT=I2.ID(+)
AND I2.ID = R1.ITEM(+) AND I2.DEFAULT_CHANGE = R1.CHANGE(+)
AND R1.RELEASE_TYPE = N3.ID(+)
START WITH (A.COMPONENT = (SELECT DISTINCT B.COMPONENT
FROM agile.item i, agile.bom b
WHERE i.item_number = b.item_number AND i.item_number = ‘Your Input Part Numbr’)
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.ITEM=A.COMPONENT
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)
ORDER BY LEVEL

Hi Raju,

Here is an alternative to what Arif offered. This one returns only one record for each path that the item takes to a different upper level assembly, as opposed to multiple records for each level in the same assembly. I hope its useful, although you did say that you may want to choose the level, so Arif’s solution may work better for you. Note that mine includes Change orders numbers, release dates, etc., but you can do away with that if not needed. See BELOW

Steve

Select Distinct
regexp_substr(Sys_Connect_By_Path(Bom.Item_Number, ‘|’),'[^|]+’,1,1) ITEM_NUMBER
,Sys_Connect_By_Path(Bom.Item_Number, ‘|’) Item_Path,
I.Item_Number TOP_ITEM,
trunc(I.CREATED) AGILE_CREATE_DATE,
I.DESCRIPTION TOP_ITEM_DESCRIPTION,
Bom.Item ,
C.Change_Number,
Trunc(C.Release_Date) Release_Date, –PROVIDES A CO RELEASE DATE WITHOUT THE TIMESTAMP
I.Created Create_Date,
BOM.ITEM_NUMBER,
CHANGE_IN ,
CHANGE_OUT ,
COMPONENT ,
LEVEL ,
BOM.SITE ,
BOM.FIND_NUMBER,
Bom.Quantity
From Agile.Bom
Inner Join Agile.Item I On I.Id = Bom.Item
Left Join Agile.Change C On Bom.Change_In = C.Id
Where Change_Out = 0 –THIS GETS MOST RECENT BOM
And Bom.Flags Like ’00_11%’ –THIS FLAG SETTING IS A WhereUsed REQUIREMENT

–NOTE THAT THE FOLLOWING LINE REFERENCES AN SITE ID THAT IS SPECIFIC TO  MY PLACE OF BUSINESS
–IN OUR CASE, ZZZ IS OUR MASTER ORG. YOU MAY UNCOMMENT THE FOLLOWING LINE AND REPLACE WITH YOUR OWN
–And Bom.Site = 250419868 –LOOK FOR BOM IN ZZZ ONLY

–THE FOLLOWING OPTIONAL LINE INSURES THE UPPER LEVEL ASSEMBLY’S SUBCLASS IS ETO ITEM. 
–REPLACE ‘ETO Item’ WITH THE NAME OF THE SUBCLASS THAT YOU CONSIDER THE TOP LEVEL
–And (Select N.Description From Agile.Nodetable N Where N.Id = I.Subclass) = ‘ETO Item‘

AND ( NOT EXISTS
(Select 1
FROM agile.BOM A
WHERE BOM.ITEM = A.COMPONENT
AND A.CHANGE_OUT = 0
AND A.FLAGS LIKE ’00_11%’
) )
CONNECT BY NOCYCLE PRIOR ITEM = COMPONENT
AND CHANGE_OUT = 0
AND BOM.FLAGS LIKE ’00_11%’
START WITH BOM.ID IN (
( Select Bom.Id
From Agile.Bom
Where Bom.Component In
(SELECT I.ID FROM AGILE.ITEM I
WHERE I.ENCODE_NAME = :ITEMNUMBER
)
AND Bom.Change_Out =0
And Bom.Flags Like ’00_11%’)
);