SQL query to fetch BOM data till nth level along with lifecycle phase, revision and Manufacturer details

Below query is working fine(excluding manufacturer data). But if I try to add nodetable.description, result data order is getting changed. I need help in fixing the result data order. It should be same as the BOM tab. Also joining manufacturer table

 

SELECT childItem, findNumber, bomLevel, i.item_number as PARENT_ITEM, c.change_number,r.rev_number,c.id,r.change,n.id — NVL (n.DESCRIPTION, ‘Preliminary’)
FROM (
SELECT A.item_number As childItem, A.find_number as findNumber, a.item as item, LEVEL as bomLevel
FROM 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 = ‘EC2630QI’)
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 nocycle 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)) BOM
INNER JOIN Item i ON BOM.item=i.id
INNER JOIN Rev r on r.item = (select id from item where item_number =childItem) and r.latest_flag=1
INNER JOIN Nodetable n on r.release_type=n.id
INNER JOIN Change c on c.id=r.change

Agile User Asked on February 17, 2021 in Agile PLM (v9).
Add Comment
1 Answer(s)

This select has worked for me, but I don’t think it gets the Mfr info.

v_id NUMBER(10,0) := NULL;
v_class NUMBER(10,0) := NULL;
v_parent VARCHAR2(300 BYTE) := NULL;
v_compo VARCHAR2(300 BYTE) := NULL;
x_id NUMBER(10,0) := NULL;
x_class NUMBER(10,0) := NULL;

— v_id := the id from the item table
— v_class := the class number from the item table

— at the bottom of this select, you will see the nth level is set to 7

SELECT
f.ITEM_ID AS BOM_ITEM_ID
, f.item AS PARENT_ID
, f.component AS COMPONENT_ID
— 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
— , ( select entryvalue from listentry where parentid = 2190144 and langid = 0 and entryid = ( select number from agile_flex where id = f.item and class in ( 9000, 10000 ) and attid = 2198312 )) as XIQ_COMPAT
— 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 AS COMPONENT_NUMBER
, f.FIND_NUMBER
, f.QUANTITY
— ( 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
, f.notes
, f.text05
, f.minimum_number
, f.maximum_number
, f.is_mutually_exclusive
, f.is_optional
— , ( select entryvalue from agile.listentry where parentid = 2196903 and entryid = f.list01 ) as prim_comp_of_bndl
, f.text01 — revenue natural acct
, f.text02 — revenue split percentage
, f.latest_released_eco
FROM
( SELECT
e.id AS ITEM_ID
, e.item
, e.ITEM_NUMBER
, e.FIND_NUMBER
, e.QUANTITY
, e.component
, 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.notes
, e.text05
, e.minimum_number
, e.maximum_number
, e.is_mutually_exclusive
, e.is_optional
, e.list01
, e.text01
, e.text02
, 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.CREATED
, b.LAST_UPD
, b.notes
, b.text05
, b.minimum_number
, b.maximum_number
, b.is_mutually_exclusive
, b.is_optional
, b.list01
, b.text01
, b.text02
, 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
—- Change level <= 1 if you just want the top level
CONNECT BY NOCYCLE PRIOR e.COMPONENT = e.ITEM and level <= 7
START WITH e.ITEM = (select x.id from agile.item x where x.id = v_id AND x.class = v_class)
) f;

Agile Talent Answered on February 19, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.