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
2 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

The best way I have found to order an exploded BOM is to  include a SYS_CONNECT_BY_PATH statement, which is also what mrmathison did.  the difference that I construct that path based on the find number rather than the part number. This should keep the exploded order just as expected.  In my case, I’m assuming the find numbers to be numeric, but keep in mind the field is a string. To build something that sorts properly, you must pad the find the numbers with zeros to a length greater or equal to the greatest number of digits you allow for a find number. We typically only allow only up to 4-digit find numbers, so that’s what the following example includes. The BOLD test below is what I added.

SELECT childItem, ITEM_PATH
,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 DISTINCT A.item_number As childItem, A.find_number as findNumber
, SYS_CONNECT_BY_PATH(LPAD(A.FIND_NUMBER,4,’0′), ‘|’) ITEM_PATH
, 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 AND R.SITE = 0
INNER JOIN Nodetable n on r.release_type=n.id
INNER JOIN Change c on c.id=r.change
ORDER BY ITEM_PATH;

Agile Talent Answered on March 1, 2021.
Add Comment

Your Answer

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