Organization(Department) Tree hierarchy SQL Query in Oracle Cloud HCM

Below is the query for the Organization(Department) Tree hierarchy in Oracle Cloud HCM.

 ----------------

 WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
                   ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
                AND potnv.tree_code = 'Bristlecone Department Tree'
                AND potnv.tree_version_id = ftv.tree_version_id
                AND ftv.tree_code = potnv.tree_code
                AND ftv.status = 'ACTIVE'
                AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
               START WITH potnv.parent_organization_id IS NULL
             CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name l1
             ,level2.child_org_name l2
             ,level3.child_org_name l3
             ,level4.child_org_name l4
         FROM org_tree level1
             ,org_tree level2
             ,org_tree level3
             ,org_tree level4
             ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
         AND level2.child_org_id = level3.parent_org_id
         AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
         AND haouf.organization_id = level4.child_org_id
         AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
        
 select distinct
 (
    SELECT MEANING
    FROM HR_LOOKUPS
    WHERE LOOKUP_TYPE = 'SYSTEM_PERSON_TYPE'
    AND LOOKUP_CODE = PAAM.SYSTEM_PERSON_TYPE) as "Worker_Type"
  ,papf.PERSON_NUMBER "Employee ID"
,paam.Assignment_Number as "AssignmentNumber"
,ppnf.FULL_NAME as "Full_Name"
,TO_CHAR(ppos.DATE_START, 'YYYY-Mon-DD','NLS_DATE_LANGUAGE=AMERICAN') as "DateOfJoining"
,dt.l2 as "ParentDepartment"
,dt.l3 as "SubDepartment"
,HOU.NAME as "Department"
 from  PER_ALL_PEOPLE_F papf
 ,per_all_assignments_f paam
 ,PER_PERIODS_OF_SERVICE ppos
 ,HR_ALL_ORGANIZATION_UNITS HOU
 ,dept_tree dt
 ,PER_PERSON_NAMES_F ppnf
 where 1=1
 and ppos.person_id=papf.person_id
 AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
 and papf.person_id=paam.person_id
 and paam.PRIMARY_FLAG ='Y'
 and paam.ASSIGNMENT_TYPE in ('E','C')
 AND PAAM.ASSIGNMENT_STATUS_TYPE='ACTIVE'
 and paam.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
 and papf.person_id=ppnf.person_id
 and ppnf.name_type='GLOBAL'
 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
 and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
 and TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE
 and dt.l4(+)=HOU.NAME
 ORDER BY papf.person_number