Explode A BOM with Tree View

To generate a Tree View BOM (Bill Of Materials) for users easy to explore the details. The tree level can be up to 8. The statement also brings in the description, Unit of Meansure, Vendor, Vendor P/N from other tables.

Four Oracle tables -
BOM -
BOMNO : BOM P/N
COMNO: Component P/N
QPS: QTY per Assembly

IM -
ITNO: P/N
DESCRIP: item description
REV: revision
SCODE: S code

IM2
ITNO: P/N
UOM: Unit of Measure

VDPN -
ITNO: P/N
VNCRMNAM: Vendor name
VENPN: Vendor P/N

SQL Statement -

view plaincopy to clipboardprint?

1. select P1, P2, P3, P4, P5, P6, P7, P8, qpa, qta, descrip, uom, rev, scode, VC, (case when VNCRMNAM is NULL then ' ' else VNCRMNAM end) MFR, (case when VENPN is NULL then ' ' else VENPN end) VPN from  
2. (select (case when lev=1 then comno else ' ' end) P1, (case when lev=2 then comno else ' ' end) P2, (case when lev=3 then comno else ' ' end) P3,  
3. (case when lev=4 then comno else ' ' end) P4, (case when lev=5 then comno else ' ' end) P5, (case when lev=6 then comno else ' ' end) P6,  
4. (case when lev=7 then comno else ' ' end) P7, (case when lev=8 then comno else ' ' end) P8, lev, qpa, DECODE(LEV,  
5. 1, QPA,  
6. 2, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, LENGTH(QT) - INSTR(QT, '*', 1, 2))),  
7. 3, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
8. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, LENGTH(QT) - INSTR(QT, '*', 1, 3))),  
9. 4, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
10. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, LENGTH(QT) - INSTR(QT, '*', 1, 4))),  
11. 5, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
12. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *  
13. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, LENGTH(QT) - INSTR(QT, '*', 1, 5))),  
14. 6, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
15. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *  
16. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, LENGTH(QT) - INSTR(QT, '*', 1, 6))),  
17. 7, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
18. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *  
19. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *  
20. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, LENGTH(QT) - INSTR(QT, '*', 1, 7))),  
21. 8, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *  
22. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *  
23. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *  
24. TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, INSTR(QT, '*', 1, 8) - INSTR(QT, '*', 1, 7)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 8) +1, LENGTH(QT) - INSTR(QT, '*', 1, 8)))  
25. ) QTA,  
26. descr AS descrip, uom, rev, scode, (case when vcode IS NOT NULL then vcode else ' ' end) VC from  
27. (select SYS_CONNECT_BY_PATH(COMNO,'-->') TREE, SYS_CONNECT_BY_PATH(TO_CHAR(QPA),'*') QT, CONNECT_BY_ROOT(COMNO) ROOT, Level As lev, qpa, comno, LPAD( COMNO, LEVEL + LENGTH(COMNO), '^') PART  
28. from BOM start with bomno=BOM_NEED_EXPLODE connect by prior comno=bomno ), IM, IM2  
29. where comno=im.itno AND comno=im2.itno  
30. order by tree) LEFT JOIN (select itno, vncrmnam, venpn from  
31. (select itno, vncrmnam, venpn, row_number() over (partition by itno order by vncrmnam, venpn) RK  
32. from VDPN)  
33. where RK =1 AND LENGTH(TRIM(vncrmnam || venpn)) > 0)  
34. ON TRIM(P1 || P2 || P3 || P4 || P5 || P6 || P7 || P8) =TRIM(ITNO)  

select P1, P2, P3, P4, P5, P6, P7, P8, qpa, qta, descrip, uom, rev, scode, VC, (case when VNCRMNAM is NULL then ' ' else VNCRMNAM end) MFR, (case when VENPN is NULL then ' ' else VENPN end) VPN from
(select (case when lev=1 then comno else ' ' end) P1, (case when lev=2 then comno else ' ' end) P2, (case when lev=3 then comno else ' ' end) P3,
(case when lev=4 then comno else ' ' end) P4, (case when lev=5 then comno else ' ' end) P5, (case when lev=6 then comno else ' ' end) P6,
(case when lev=7 then comno else ' ' end) P7, (case when lev=8 then comno else ' ' end) P8, lev, qpa, DECODE(LEV,
1, QPA,
2, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, LENGTH(QT) - INSTR(QT, '*', 1, 2))),
3, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, LENGTH(QT) - INSTR(QT, '*', 1, 3))),
4, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, LENGTH(QT) - INSTR(QT, '*', 1, 4))),
5, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, LENGTH(QT) - INSTR(QT, '*', 1, 5))),
6, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, LENGTH(QT) - INSTR(QT, '*', 1, 6))),
7, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, LENGTH(QT) - INSTR(QT, '*', 1, 7))),
8, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *
TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, INSTR(QT, '*', 1, 8) - INSTR(QT, '*', 1, 7)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 8) +1, LENGTH(QT) - INSTR(QT, '*', 1, 8)))
) QTA,
descr AS descrip, uom, rev, scode, (case when vcode IS NOT NULL then vcode else ' ' end) VC from
(select SYS_CONNECT_BY_PATH(COMNO,'-->') TREE, SYS_CONNECT_BY_PATH(TO_CHAR(QPA),'*') QT, CONNECT_BY_ROOT(COMNO) ROOT, Level As lev, qpa, comno, LPAD( COMNO, LEVEL + LENGTH(COMNO), '^') PART
from BOM start with bomno=BOM_NEED_EXPLODE connect by prior comno=bomno ), IM, IM2
where comno=im.itno AND comno=im2.itno
order by tree) LEFT JOIN (select itno, vncrmnam, venpn from
(select itno, vncrmnam, venpn, row_number() over (partition by itno order by vncrmnam, venpn) RK
from VDPN)
where RK =1 AND LENGTH(TRIM(vncrmnam || venpn)) > 0)
ON TRIM(P1 || P2 || P3 || P4 || P5 || P6 || P7 || P8) =TRIM(ITNO)