Extract multi-level bom with a query
Hello to everyone,
I need to create a query that extract a multi-level Bom based on a specified Number, Version and View (manufacturing/design).
The query that i made, take the latest version of the childs, but i need the "as stored" configuration of that bom in that specific version and view.
I have tried usign Query Builder, but it can extract only Single-level Bom.
I leave at the bottom the query i made
Can someone help me?
Thanks for your support
Best regards.
-----------------
"WITH
LastVersion
AS
(SELECT *
FROM (SELECT p.*,
ROW_NUMBER ()
OVER (
PARTITION BY p.ida3MasterReference
ORDER BY
TO_NUMBER (p.VERSIONIDA2VERSIONINFO) DESC,
TO_NUMBER (p.ITERATIONIDA2ITERATIONINFO) DESC) AS rn
FROM WTORACLE.WTPart p)
WHERE rn = 1),
BOM_Recursive (part_id,
codice,
descrizione,
ida2a2,
padre_id,
Revisione,
Iterazione,
Revisione_Completa,
Stato_Figlio,
livello,
VALUE,
GatheringPart)
AS
(-- Root specifica
SELECT p.ida2a2 AS part_id,
pm.WTPARTNUMBER AS codice,
pm.name AS descrizione,
pm.ida2a2,
NULL AS padre_id,
p.VERSIONIDA2VERSIONINFO AS Revisione,
p.ITERATIONIDA2ITERATIONINFO AS Iterazione,
p.VERSIONIDA2VERSIONINFO
|| '.'
|| p.ITERATIONIDA2ITERATIONINFO AS Revisione_Completa,
p.STATESTATE AS Stato_Figlio,
0 AS livello,
ECL.VALUE,
DECODE (pm.hidePartInStructure, 0, 'NO', 'SI') AS GatheringPart
FROM WTORACLE.WTPart p
JOIN WTORACLE.WTPartMaster pm
ON p.ida3MasterReference = pm.ida2a2
LEFT JOIN
(SELECT sv.*
FROM WTORACLE.StringValue sv
JOIN WTORACLE.StringDefinition sd
ON sd.idA2A2 = sv.ida3a6
WHERE sd.name = 'ECLASS') ECL
ON p.idA2A2 = ECL.ida3a4
WHERE pm.WTPARTNUMBER = 'C310272AD01F01'
AND p.VERSIONIDA2VERSIONINFO = '05'
AND p.ITERATIONIDA2ITERATIONINFO = '4'
UNION ALL
SELECT lv.ida2a2 AS part_id,
cm.WTPARTNUMBER AS codice,
cm.name AS descrizione,
cm.ida2a2,
br.part_id AS padre_id,
lv.VERSIONIDA2VERSIONINFO AS Revisione,
lv.ITERATIONIDA2ITERATIONINFO AS Iterazione,
lv.VERSIONIDA2VERSIONINFO
|| '.'
|| lv.ITERATIONIDA2ITERATIONINFO AS Revisione_Completa,
lv.STATESTATE AS Stato_Figlio,
br.livello + 1 AS livello,
ECL.VALUE,
DECODE (cm.hidePartInStructure, 0, 'NO', 'SI') AS GatheringPart
FROM BOM_Recursive br
JOIN WTORACLE.WTPartUsageLink u ON u.ida3a5 = br.part_id
JOIN WTORACLE.WTPartMaster cm ON cm.ida2a2 = u.ida3b5
JOIN LastVersion lv ON lv.ida3MasterReference = cm.ida2a2
LEFT JOIN
(SELECT sv.*
FROM WTORACLE.StringValue sv
JOIN WTORACLE.StringDefinition sd
ON sd.idA2A2 = sv.ida3a6
WHERE sd.name = 'ECLASS') ECL
ON lv.idA2A2 = ECL.ida3a4)
SELECT part_id AS Padre_id,
codice,
descrizione,
padre_id AS Figlio_id,
Revisione,
Iterazione,
Revisione_Completa,
Stato_Figlio,
livello,
VALUE AS eclass,
GatheringPart
FROM BOM_Recursive
ORDER BY livello, codice;"