By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( bom_code, rm_code, qty ) AS
SELECT 'A', 'B', 1 FROM DUAL UNION ALL
SELECT 'A', 'C', 2 FROM DUAL UNION ALL
SELECT 'A', 'D', 5 FROM DUAL UNION ALL
SELECT 'A', 'E', 3 FROM DUAL UNION ALL
SELECT 'A', 'F', 8 FROM DUAL UNION ALL
SELECT 'D', 'G', 2 FROM DUAL UNION ALL
SELECT 'D', 'H', 1 FROM DUAL UNION ALL
SELECT 'D', 'I', 1 FROM DUAL UNION ALL
SELECT 'D', 'J', 1 FROM DUAL UNION ALL
SELECT 'F', 'K', 1 FROM DUAL UNION ALL
SELECT 'F', 'L', 1 FROM DUAL UNION ALL
SELECT 'G', 'Z', 1 FROM DUAL UNION ALL
SELECT 'G', 'AA', 3 FROM DUAL UNION ALL
SELECT 'I', 'M', 4 FROM DUAL UNION ALL
SELECT 'I', 'N', 9 FROM DUAL UNION ALL
SELECT 'I', 'O', 7 FROM DUAL UNION ALL
SELECT 'N', 'P', 6 FROM DUAL UNION ALL
SELECT 'N', 'Q', 12 FROM DUAL UNION ALL
SELECT 'N', 'R', 4 FROM DUAL UNION ALL
SELECT 'N', 'S', 9 FROM DUAL UNION ALL
SELECT 'S', 'T', 3 FROM DUAL UNION ALL
SELECT 'S', 'U', 2 FROM DUAL UNION ALL
SELECT 'T', 'V', 1 FROM DUAL UNION ALL
SELECT 'T', 'W', 3 FROM DUAL UNION ALL
SELECT 'U', 'X', 5 FROM DUAL UNION ALL
SELECT 'U', 'Y', 8 FROM DUAL;
26 rows affected
SELECT t.*,
SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
LEVEL
FROM table_name t
WHERE CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;
| BOM_CODE | RM_CODE | QTY | PATH | LEVEL |
|---|---|---|---|---|
| A | B | 1 | -A-B | 1 |
| A | C | 2 | -A-C | 1 |
| G | AA | 3 | -A-D-G-AA | 3 |
| G | Z | 1 | -A-D-G-Z | 3 |
| D | H | 1 | -A-D-H | 2 |
| I | M | 4 | -A-D-I-M | 3 |
| N | P | 6 | -A-D-I-N-P | 4 |
| N | Q | 12 | -A-D-I-N-Q | 4 |
| N | R | 4 | -A-D-I-N-R | 4 |
| T | V | 1 | -A-D-I-N-S-T-V | 6 |
| T | W | 3 | -A-D-I-N-S-T-W | 6 |
| U | X | 5 | -A-D-I-N-S-U-X | 6 |
| U | Y | 8 | -A-D-I-N-S-U-Y | 6 |
| I | O | 7 | -A-D-I-O | 3 |
| D | J | 1 | -A-D-J | 2 |
| A | E | 3 | -A-E | 1 |
| F | K | 1 | -A-F-K | 2 |
| F | L | 1 | -A-F-L | 2 |
SELECT t.*,
SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
LEVEL,
( SELECT SUM( qty )
FROM table_name s
START WITH t.rm_code = s.rm_code
CONNECT BY PRIOR bom_code = rm_code ) AS total_qty
FROM table_name t
WHERE CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;
| BOM_CODE | RM_CODE | QTY | PATH | LEVEL | TOTAL_QTY |
|---|---|---|---|---|---|
| A | B | 1 | -A-B | 1 | 1 |
| A | C | 2 | -A-C | 1 | 2 |
| G | AA | 3 | -A-D-G-AA | 3 | 10 |
| G | Z | 1 | -A-D-G-Z | 3 | 8 |
| D | H | 1 | -A-D-H | 2 | 6 |
| I | M | 4 | -A-D-I-M | 3 | 10 |
| N | P | 6 | -A-D-I-N-P | 4 | 21 |
| N | Q | 12 | -A-D-I-N-Q | 4 | 27 |
| N | R | 4 | -A-D-I-N-R | 4 | 19 |
| T | V | 1 | -A-D-I-N-S-T-V | 6 | 28 |
| T | W | 3 | -A-D-I-N-S-T-W | 6 | 30 |
| U | X | 5 | -A-D-I-N-S-U-X | 6 | 31 |
| U | Y | 8 | -A-D-I-N-S-U-Y | 6 | 34 |
| I | O | 7 | -A-D-I-O | 3 | 13 |
| D | J | 1 | -A-D-J | 2 | 6 |
| A | E | 3 | -A-E | 1 | 3 |
| F | K | 1 | -A-F-K | 2 | 9 |
| F | L | 1 | -A-F-L | 2 | 9 |