Oracle 11g Release 2

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

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