Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
CREATE TABLE order_items (object_id, bpi_id) AS
SELECT LEVEL, CHR(64+LEVEL) FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected

CREATE TABLE nc_objects (object_id, object_type_id, parent_id) AS
SELECT LEVEL, 100, LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT LEVEL+10, 200, LEVEL + 10 - 1 FROM DUAL CONNECT BY LEVEL <= 3;
6 rows affected

select so.object_id,
       bpi_id,
       SYS_CONNECT_BY_PATH(order_items.object_id||':'||so.object_id || ':' || bpi_id, ',') AS path
from   order_items,
       nc_objects so
where  so.object_type_id = 100/* Sales Order */
start with so.object_id = order_items.object_id
connect by prior so.parent_id = so.object_id


OBJECT_ID
BPI_ID
PATH


1
A
,1:1:A

2
B
,2:2:B

1
A
,2:2:B,1:1:A

1
C
,2:2:B,3:1:C

1
B
,2:2:B,2:1:B

3
C
,3:3:C

2
A
,3:3:C,1:2:A

1
A
,3:3:C,1:2:A,1:1:A

1
C
,3:3:C,1:2:A,3:1:C

1
B
,3:3:C,1:2:A,2:1:B

2
C
,3:3:C,3:2:C

1
A
,3:3:C,3:2:C,1:1:A

1
C
,3:3:C,3:2:C,3:1:C

1
B
,3:3:C,3:2:C,2:1:B

2
B
,3:3:C,2:2:B

1
A
,3:3:C,2:2:B,1:1:A

1
C
,3:3:C,2:2:B,3:1:C

1
B
,3:3:C,2:2:B,2:1:B


WITH data (oi_id, so_id, bpi_id, parent_id, object_type_id) AS (
  SELECT oi.object_id,
         so.object_id,
         oi.bpi_id,
         so.parent_id,
         so.object_type_id
  FROM   order_items oi
         CROSS JOIN nc_objects so
),
hierarchy (so_id, bpi_id, parent_id, object_type_id, path) AS (
  SELECT so_id,
         bpi_id,
         parent_id,
         object_type_id,
         ','||oi_id||':'||so_id||':'||bpi_id
  FROM   data
  WHERE  so_id = oi_id
UNION ALL
  SELECT d.so_id,
         d.bpi_id,
         d.parent_id,
         d.object_type_id,
         h.path || ','||d.oi_id||':'||d.so_id||':'||d.bpi_id
  FROM   data d
         INNER JOIN hierarchy h
         ON (h.parent_id = d.so_id)
) SEARCH DEPTH FIRST BY so_id SET order_id
select so_id AS object_id,
       bpi_id,
       path
from   hierarchy
where  object_type_id = 100


OBJECT_ID
BPI_ID
PATH


1
A
,1:1:A

2
B
,2:2:B

1
A
,2:2:B,1:1:A

1
B
,2:2:B,2:1:B

1
C
,2:2:B,3:1:C

3
C
,3:3:C

2
A
,3:3:C,1:2:A

1
A
,3:3:C,1:2:A,1:1:A

1
B
,3:3:C,1:2:A,2:1:B

1
C
,3:3:C,1:2:A,3:1:C

2
B
,3:3:C,2:2:B

1
A
,3:3:C,2:2:B,1:1:A

1
B
,3:3:C,2:2:B,2:1:B

1
C
,3:3:C,2:2:B,3:1:C

2
C
,3:3:C,3:2:C

1
A
,3:3:C,3:2:C,1:1:A

1
B
,3:3:C,3:2:C,2:1:B

1
C
,3:3:C,3:2:C,3:1:C


select so.object_id,
       bpi_id,
       SYS_CONNECT_BY_PATH(order_items.object_id||':'||so.object_id || ':' || bpi_id, ',') AS path
from   order_items
       INNER JOIN nc_objects so
       ON so.object_id = order_items.object_id
where  so.object_type_id = 100/* Sales Order */
connect by prior so.parent_id = so.object_id


OBJECT_ID
BPI_ID
PATH


1
A
,1:1:A

2
B
,2:2:B

1
A
,2:2:B,1:1:A

3
C
,3:3:C

2
B
,3:3:C,2:2:B

1
A
,3:3:C,2:2:B,1:1:A

OBJECT_ID	BPI_ID	PATH
1	A	,1:1:A
2	B	,2:2:B
1	A	,2:2:B,1:1:A
1	C	,2:2:B,3:1:C
1	B	,2:2:B,2:1:B
3	C	,3:3:C
2	A	,3:3:C,1:2:A
1	A	,3:3:C,1:2:A,1:1:A
1	C	,3:3:C,1:2:A,3:1:C
1	B	,3:3:C,1:2:A,2:1:B
2	C	,3:3:C,3:2:C
1	A	,3:3:C,3:2:C,1:1:A
1	C	,3:3:C,3:2:C,3:1:C
1	B	,3:3:C,3:2:C,2:1:B
2	B	,3:3:C,2:2:B
1	A	,3:3:C,2:2:B,1:1:A
1	C	,3:3:C,2:2:B,3:1:C
1	B	,3:3:C,2:2:B,2:1:B