Oracle Apex tree child node returning multiple times

I have an apex tree with 3 layers: (Apex version 22.1)

BU_NAME ORG_UNIT_NAME DEPT_NAME

Source Table:

DEPT_BU_LIST

DEPT_ID BU_NAME ORG_UNIT_NAME   DEPT_NAME
1        A_BU   AX_ORG          A1_DEPT
2        B_BU   BX_ORG          A2_DEPT
3        C_BU   CX_ORG          A3_DEPT
4        C_BU   CX_ORG          A4_DEPT


CREATE TABLE DEPT_BU_LIST 
( DEPT_ID NOT NULL ENABLE,
BU_NAME VARCHAR2(100), ORG_UNIT_NAME VARCHAR2(100), DEPT_NAME VARCHAR2(100));


INSERT INTO DEPT_BU_LIST VALUES
(1,'A_BU','AX_ORG','A1_DEPT');
INSERT INTO DEPT_BU_LIST VALUES
(1,'B_BU','AY_ORG','A1_DEPT');
INSERT INTO DEPT_BU_LIST VALUES
(1,'C_BU','CX_ORG','A3_DEPT');
INSERT INTO DEPT_BU_LIST VALUES
(1,'C_BU','CX_ORG','A4_DEPT');

When i execute below query in apex, the first 2 levels are showing data correctly, but dept_name value is repeating.

EX:

A_BU
AX_ORG
A1_DEPT
A2_DEPT

B_BU
AY_ORG
A1_DEPT
A2_DEPT

Expected result:

A_BU AX_ORG A1_DEPT

B_BU
 AY_ORG
  A2_DEPT

Even if A_BU has A_DEPT and B_BU has B1_DEPT assigned in the straightforard table. It shows data as above.

    SELECT case WHEN connect_by_isleaf = 1 THEN 0 
                WHEN level = 1 THEN 1 
    ELSE -1 
    END               status,
     level                 "level",
    display                name,
    null as icon, 
     id                     id,
    tooltip                tooltip,
    link                   link
     FROM (
     SELECT bu_name
     display,
    , null               pid
    , bu_name               id
    , 1                  "level"
     , 'javascript:$s(''P1_SELECTED_NODE'', 
     '''||bu_name||'||||'||''')'                          
    link
    , null               tooltip 
    FROM (SELECT bu_name             bu_name   
    , org_unit_name               org_unit_name
    , dept_name         dept_name
     , count(1)                 error_count
     FROM dept_bu_list
     GROUP BY
    bu_name
    , org_unit_name
    ,dept_name
     ) e
    GROUP BY 
    bu_name
     UNION ALL       
    SELECT org_unit_name
    display,
    , bu_name               pid
     , org_unit_name            id
    , 2                  "level"
     , 'javascript:$s(''P1_SELECTED_NODE'', '''||bu_name||'|'||org_unit_name||'|||'||''')'                          
    link
    , null               tooltip 
    FROM (SELECT bu_name             bu_name   
    , org_unit_name               org_unit_name
     , dept_name         dept_name
    , count(1)                 error_count
    FROM dept_bu_list
    GROUP BY
    bu_name
    , org_unit_name
    ,dept_name
     ) e
     GROUP BY 
    bu_name
    , org_unit_name
     UNION ALL       
    SELECT dept_name 
    display,
   , org_unit_name                 pid
     , dept_name     id
    , 3                  "level"
     , 'javascript:$s(''P1_SELECTED_NODE'', '''||bu_name||'|'||org_unit_name||'|'||dept_name||'||'||''')'                          
    link
    , null               tooltip 
    FROM (SELECT bu_name             bu_name   
    , org_unit_name               org_unit_name
    , dept_name         dept_name
    , count(1)                 error_count
     FROM dept_bu_list
     GROUP BY
    bu_name
    , org_unit_name
    ,dept_name
     ) e
    GROUP BY 
    bu_name
    , org_unit_name
    
          , dept_name
   )
   START WITH pid IS NULL
  CONNECT BY PRIOR id = pid
   ORDER SIBLINGS BY display

Please help with this.. I guess i am missing something at level 3 which is returning all dept_name values

It is unclear what output you are expecting for your sample data but you appear to want to display the hierarchy. To do that, you can UNPIVOT your data and remove duplicates and then use a hierarchical query to reconstruct it as your tree structure:

select CASE
       WHEN connect_by_isleaf = 1
       THEN 0
       WHEN level = 1
       THEN 1
       ELSE -1
       END AS status,
       LEVEL,
       child AS title,
       NULL AS icon,
       NULL as tooltip,
       'javascript:$s("P1_SELECTED_NODE","'
       || CASE LEVEL
          WHEN 1 THEN child
          WHEN 2 THEN parent || '|' || child
          WHEN 3 THEN CONNECT_BY_ROOT child || '|' || parent || '|' || child
          END 
       || '")' AS link 
FROM   (
  SELECT DISTINCT bu_name, parent, child, depth
  FROM   (SELECT d.*, NULL AS root FROM DEPT_BU_LIST d)
         UNPIVOT (
           (bu_name, parent, child) FOR depth IN (
             (bu_name, root, bu_name) AS 1,
             (bu_name, bu_name, org_unit_name) AS 2,
             (bu_name, org_unit_name, dept_name) AS 3
           )
         )
)
START WITH DEPTH = 1
CONNECT BY PRIOR child = parent
AND        PRIOR depth + 1 = depth
AND        PRIOR bu_name = bu_name
ORDER SIBLINGS BY child

or, without a hierarchical query:

SELECT DECODE(depth, 1, 1, 2, -1, 3, 0) AS status,
       depth AS "LEVEL",
       title,
       NULL AS icon,
       NULL AS tooltip,
       'javascript:$s("P1_SELECTED_NODE","' || link || '")' AS link
FROM   (
  SELECT DISTINCT h.*
  FROM   DEPT_BU_LIST d
         CROSS JOIN LATERAL(
           SELECT d.bu_name,
                  NULL AS org_unit_name,
                  NULL AS dept_name,
                  1 AS depth,
                  d.bu_name AS title,
                  d.bu_name AS link
           FROM   DUAL
         UNION ALL
           SELECT d.bu_name,
                  d.org_unit_name,
                  NULL AS dept_name,
                  2 AS depth,
                  d.org_unit_name AS title,
                  d.bu_name || '|' || d.org_unit_name AS link
           FROM   DUAL
         UNION ALL
           SELECT d.bu_name,
                  d.org_unit_name,
                  d.dept_name,
                  3 AS depth,
                  d.dept_name AS title,
                  d.bu_name || '|' || d.org_unit_name || '|' || d.dept_name AS link
           FROM   DUAL
         ) h
  ORDER BY h.bu_name,
           h.org_unit_name NULLS FIRST,
           h.dept_name NULLS FIRST
)

Which, for the sample data:

CREATE TABLE DEPT_BU_LIST (
  DEPT_ID       NUMBER PRIMARY KEY NOT NULL ENABLE,
  BU_NAME       VARCHAR2(100),
  ORG_UNIT_NAME VARCHAR2(100),
  DEPT_NAME     VARCHAR2(100)
);

BEGIN
INSERT INTO DEPT_BU_LIST VALUES (1,'A_BU','AX_ORG','A1_DEPT');
INSERT INTO DEPT_BU_LIST VALUES (2,'B_BU','AY_ORG','A2_DEPT');
INSERT INTO DEPT_BU_LIST VALUES (3,'C_BU','CX_ORG','A3_DEPT');
INSERT INTO DEPT_BU_LIST VALUES (4,'C_BU','CX_ORG','A4_DEPT');
INSERT INTO DEPT_BU_LIST VALUES (5,'D_BU','AY_ORG','A5_DEPT');
END;
/

Both output:

fiddle