I have MAST which has BOM and Material(component) relationship which needs to be traversed to STPO with BOM to identify the compenent (raw materials) and each component may be split into sub material which needs to be lookup for BOM and it goes on and i need to get the below output for BOM1 i.e 1001 BOM splits into 3 levels as output.Kindly let me know how to achieve this and i have tried with the below query not able to achieve.
Select * FROM (
select MAST.MATERIAL MAT,MAST.BOM PBOM,STPO.BOM CPOM,STPO.COMPONENT COMP from MAST , STPO
WHERE MAST.BOM = STPO.BOM )
--START WITH MAT = '1001'
CONNECT BY PRIOR MAT = COMP;
| MAST |
BOM | Material |
BOM1 | 1001 |
BOM2 | 2001 |
BOM3 | 3001 |
BOM4 | 4001 |
BOM5 | 5001 |
BOM6 | 6001 |
BOM7 | 7001 |
BOM8 | 8001 |
BOM9 | 1002 |
BOM10 | 1009 |
| |
"STPO" |
BOM | Component |
BOM1 | 1002 |
BOM1 | 1003 |
BOM1 | 1004 |
BOM1 | 1005 |
BOM2 | 2002 |
BOM2 | 2003 |
BOM2 | 2004 |
BOM2 | 2005 |
BOM2 | 2006 |
BOM9 | 1009 |
BOM9 | 1010 |
BOM10 | 1011 |
BOM10 | 1012 |
| |
Output |
PMAT | LEVEL | COMPONENT |
1001 | 1 | 1002 |
1001 | 1 | 1003 |
1001 | 1 | 1004 |
1001 | 1 | 1005 |
1002 | 2 | 1009 |
1002 | 2 | 1010 |
1009 | 3 | 1011 |
1009 | 3 | 1012 |