Replace ‘MY-PART-NUMBER’ at the bottom of this query with your part number.  BOM Notes is the NOTES column.

SELECT
f.ITEM_ID,
f.item,
f.component,
f.root_item_id,
f.CYCLE,
f.LVL,
f.PATH,
( select item_number from AGILE.ITEM g where g.id = f.item ) as PARENT_ITEM_NUMBER,
f.latest_released_eco AS PARENT_LATEST_REL_ECO_ID,
( select h.change_number from agile.change h where h.id = f.latest_released_eco ) AS PARENT_ECO_NUMBER,
( select o.rev_number from agile.rev o where o.item = f.item and o.change = ( select o.change from agile.rev o where o.item = f.item and o.latest_flag = 1 and o.released = 1 ) and o.latest_flag = 1 and o.released = 1 ) as parent_latest_rev,
( select o.release_date from agile.rev o where o.item = f.item and o.change = ( select o.change from agile.rev o where o.item = f.item and o.latest_flag = 1 and o.released = 1 ) and o.latest_flag = 1 and o.released = 1 ) as parent_latest_rel_dt,
( select description from AGILE.ITEM g where g.id = f.item ) as PARENT_DESCRIPTION,
f.ITEM_NUMBER,
f.FIND_NUMBER,
f.QUANTITY,
f.NOTES,
( select o.change from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rel_eco_id,
( select h.change_number from agile.change h where h.id = ( select o.change from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) ) AS comp_latest_eco,
( select o.rev_number from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rev,
( select o.release_date from agile.rev o where o.item = f.component and o.latest_flag = 1 and o.released = 1 ) as comp_latest_rel_dt,
( select description from agile.nodetable where id = ( select release_type from agile.rev where item = f.component and latest_flag = 1 and released = 1 ) and parentid = 1514 ) as comp_lifecycle_phase,
( select class from agile.item where id = f.component ) as COMP_CLASS_ID,
( select subclass from agile.item where id = f.component ) as COMP_SUBCLASS_ID,
( select description from agile.nodetable where id = ( select subclass from agile.item where id = f.component ) ) as COMP_SUBCLASS,
( select category from agile.item where id = f.component ) as COMP_CATEGORY_ID,
( decode(
(select class from agile.item where id = f.component),
10000,(select entryvalue from agile.listentry where parentid = 311 and entryid = ( select category from agile.item where id = f.component ) ),
9000,(select entryvalue from agile.listentry where parentid = 321 and entryid = ( select category from agile.item where id = f.component ) )
) ) as COMP_CATEGORY,
( select description from AGILE.ITEM g where g.id = f.component ) as COMPONENT_DESCRIPTION
FROM
( SELECT
e.ID AS ITEM_ID,
e.ITEM,
e.ITEM_NUMBER,
e.FIND_NUMBER,
e.QUANTITY,
e.COMPONENT,
e.NOTES,
connect_by_root e.COMPONENT as root_item_id,
CONNECT_BY_ISCYCLE AS CYCLE, -- "Cycle",
level AS LVL,
SYS_CONNECT_BY_PATH(e.COMPONENT, '/') AS PATH, -- "PATH"
e.latest_released_eco
FROM
( SELECT
b.rowid bom_rowid
, c1.rowid c1_rowid
, c2.rowid c2_rowid
, i.rowid i_rowid
, b.ID
, b.ITEM
, b.ITEM_NUMBER
, b.FIND_NUMBER
, b.QUANTITY
, b.DESCRIPTION
, b.CHANGE_IN
, b.CHANGE_OUT
, b.COMPONENT
, b.NOTES
, b.CREATED
, b.LAST_UPD
, i.latest_released_eco
FROM AGILE.BOM b
, AGILE.CHANGE c1
, AGILE.CHANGE c2
, AGILE.ITEM i
WHERE b.change_in = c1.id(+)
AND b.change_out = c2.id(+)
AND (b.change_in=0 OR c1.release_date IS NOT NULL)
AND (b.change_out=0 OR c2.release_date IS NULL)
AND i.id = b.item
AND i.latest_released_eco != 0 ) e
CONNECT BY NOCYCLE PRIOR e.COMPONENT = e.ITEM and level <= 7
START WITH e.ITEM = (select x.id from agile.item x where x.item_number = 'MY-PART-NUMBER')
) f