SQL Query to get the department hierarchy

WITH ORG_TREE
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT (
                               SELECT p.name
                               FROM hr_all_organization_units_vl p
                               WHERE p.organization_id = ot.parent_organization_id
                                      AND rownum = 1
                               ) Parent_Dept_Name
                       ,(
                               SELECT c.name
                               FROM hr_all_organization_units_vl c
                               WHERE c.organization_id = ot.organization_id
                                      AND rownum = 1
                               ) Dept_Name
   ,(
                               SELECT c.organization_id
                               FROM hr_all_organization_units_vl c
                               WHERE c.organization_id = ot.organization_id
                                      AND rownum = 1
                               ) Dept_id
                       ,(
                               SELECT c.attribute_number1
                               FROM hr_all_organization_units_vl c
                               WHERE c.organization_id = ot.organization_id
                                      AND rownum = 1
                               ) Dept_legacy
                       ,ot.tree_structure_code
                       ,ot.parent_organization_id parent_org_id
                       ,ot.organization_id org_id
   ,ot.depth
                       ,LEVEL levelcount
               FROM PER_DEPT_TREE_NODE_V ot 
               WHERE ot.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
                       AND ot.tree_code = 'Enter Department Tree Name' START
               WITH ot.parent_organization_id IS NULL CONNECT BY PRIOR ot.organization_id = ot.parent_organization_id
   --and LEVEL <= 1
   /*(select ot1.depth dept_depth
               FROM PER_DEPT_TREE_NODE_V ot1
               WHERE ot1.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
                       AND ot1.tree_code = 'Enter Department Tree Name' START
               WITH ot1.parent_organization_id IS NULL CONNECT BY PRIOR ot1.organization_id = ot1.parent_organization_id
   and ot1.organization_id = ot.organization_id) */
               ) 
        ORDER BY levelcount ASC
        )
        ,flattened_tree
AS (
        SELECT /*+ materialize */
                lev0.DEPT_NAME LVL0_DEPT,lev0.depth LVL0_dept_depth
   ,lev01.DEPT_NAME LVL1_DEPT,lev01.depth LVL1_dept_depth 
               ,lev02.DEPT_NAME LVL2_DEPT,lev02.depth LVL2_dept_depth 
               ,lev03.DEPT_NAME LVL3_DEPT,lev03.depth LVL3_dept_depth 
               ,lev04.DEPT_NAME LVL4_DEPT,lev04.depth LVL4_dept_depth 
               ,lev05.DEPT_NAME LVL5_DEPT,lev05.depth LVL5_dept_depth 
               ,lev06.DEPT_NAME LVL6_DEPT,lev06.depth LVL6_dept_depth 
               ,lev07.DEPT_NAME LVL7_DEPT,lev07.depth LVL7_dept_depth 
   ,lev08.DEPT_NAME LVL8_DEPT,lev08.depth LVL8_dept_depth 
   ,lev09.DEPT_NAME LVL9_DEPT,lev09.depth LVL9_dept_depth 
        FROM ORG_TREE lev0 
LEFT OUTER JOIN ORG_TREE lev01 ON lev0.org_id = lev01.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev02 ON lev01.org_id = lev02.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev03 ON lev02.org_id = lev03.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev04 ON lev03.org_id = lev04.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev05 ON lev04.org_id = lev05.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev06 ON lev05.org_id = lev06.parent_org_id and rownum=1
        LEFT OUTER JOIN ORG_TREE lev07 ON lev06.org_id = lev07.parent_org_id and rownum=1
LEFT OUTER JOIN ORG_TREE lev08 ON lev07.org_id = lev08.parent_org_id and rownum=1
LEFT OUTER JOIN ORG_TREE lev09 ON lev08.org_id = lev09.parent_org_id and rownum=1
LEFT OUTER JOIN ORG_TREE lev10 ON lev09.org_id = lev10.parent_org_id and rownum=1
        WHERE lev0.PARENT_DEPT_NAME IS NULL
        )
select distinct 
PAPF.PERSON_NUMBER AS Employee_Number,
PPNF.FIRST_NAME,
PPNF.LAST_NAME,
PAAM.EMPLOYEE_CATEGORY,
PAAM.EMPLOYMENT_CATEGORY,
PAAM.ASS_ATTRIBUTE1 as SCHEDULED_NON_SCHEDULED,
PAAM.ASS_ATTRIBUTE2 as DIRECT_INDIRECT_ADMIN,
PAAM.ASS_ATTRIBUTE3 as PAY_PROFILE,
--PAEIM.AEI_ATTRIBUTE1 "Project Code",
(select listagg(PAEIM.AEI_ATTRIBUTE1,', ') within group(order by PAEIM.AEI_ATTRIBUTE1) from PER_ASSIGNMENT_EXTRA_INFO_M PAEIM where 
paam.assignment_id = paeim.assignment_id (+) 
and trunc(sysdate) between PAEIM.effective_start_date and PAEIM.effective_end_date) as Project_Code,
--PAEIM.AEI_INFORMATION_NUMBER1 "Project Percentage",
PJF.Name as Job_Title,
PPTT.User_Person_Type,
dept.name Department,
(select ppnf1.full_name from per_person_names_f ppnf1
        , per_all_people_f papf1 
where papf1.person_id = pasf.manager_id 
and papf1.person_id = ppnf1.person_id 
and ppnf1.name_type = 'GLOBAL'
and trunc(sysdate) between  papf1.effective_start_date and papf1.effective_end_date
AND trunc(sysdate) between  ppnf1.effective_start_date and ppnf1.effective_end_date) as Line_Manager_Name,
(select papf1.person_number from per_all_people_f papf1 
where papf1.person_id = pasf.manager_id 
and trunc(sysdate) between  papf1.effective_start_date and papf1.effective_end_date) as Line_Manager_Number,
  (select work_phone.PHONE_NUMBER AS WorkPhoneNumber from PER_PHONES work_phone
   where pasf.manager_id = work_phone.person_id(+) 
     AND work_phone.PHONE_TYPE = 'WM') AS Manager_Phone_Number, 
(select DISTINCT ppnf2.full_name from per_person_names_f ppnf2
        , per_all_people_f papf2 
, per_all_assignments_m paam2
, per_assignment_supervisors_f pasf1
where papf2.person_id = pasf.manager_id 
and pasf1.manager_id = ppnf2.person_id
        and pasf1.assignment_id = paam2.assignment_id
        and paam2.person_id = papf2.person_id 
and ppnf2.name_type = 'GLOBAL'
and pasf1.manager_type = 'LINE_MANAGER' 
and trunc(sysdate) between  papf2.effective_start_date and papf2.effective_end_date
AND trunc(sysdate) between  pasf1.effective_start_date and pasf1.effective_end_date
AND trunc(sysdate) between  ppnf2.effective_start_date and ppnf2.effective_end_date
AND trunc(sysdate) between  paam2.effective_start_date and paam2.effective_end_date) as SECOND_MANAGER_NAME, 
(select DISTINCT papf3.person_number
from per_person_names_f ppnf2
        , per_all_people_f papf2 
, per_all_assignments_m paam2
, per_assignment_supervisors_f pasf1
, per_all_people_f papf3
where papf2.person_id = pasf.manager_id 
and pasf1.manager_id = ppnf2.person_id
        and pasf1.assignment_id = paam2.assignment_id
        and paam2.person_id = papf2.person_id 
and ppnf2.name_type = 'GLOBAL'
and papf3.person_id = ppnf2.person_id
and pasf1.manager_type = 'LINE_MANAGER' 
and trunc(sysdate) between  papf2.effective_start_date and papf2.effective_end_date
and trunc(sysdate) between  papf3.effective_start_date and papf3.effective_end_date
AND trunc(sysdate) between  pasf1.effective_start_date and pasf1.effective_end_date
AND trunc(sysdate) between  ppnf2.effective_start_date and ppnf2.effective_end_date
AND trunc(sysdate) between  paam2.effective_start_date and paam2.effective_end_date) as SECOND_MANAGER_PERSON_NUMBER,
(select DISTINCT work_phone.PHONE_NUMBER AS WorkPhoneNumber
from per_person_names_f ppnf2
        , per_all_people_f papf2 
, per_all_assignments_m paam2
, per_assignment_supervisors_f pasf1
, per_all_people_f papf3
, PER_PHONES work_phone
where papf2.person_id = pasf.manager_id 
and pasf1.manager_id = ppnf2.person_id
        and pasf1.assignment_id = paam2.assignment_id
        and paam2.person_id = papf2.person_id 
and ppnf2.name_type = 'GLOBAL'
and papf3.person_id = ppnf2.person_id
and papf3.person_id = work_phone.person_id
AND work_phone.PHONE_TYPE = 'WM'
and pasf1.manager_type = 'LINE_MANAGER' 
and trunc(sysdate) between  papf2.effective_start_date and papf2.effective_end_date
and trunc(sysdate) between  papf3.effective_start_date and papf3.effective_end_date
AND trunc(sysdate) between  pasf1.effective_start_date and pasf1.effective_end_date
AND trunc(sysdate) between  ppnf2.effective_start_date and ppnf2.effective_end_date
AND trunc(sysdate) between  paam2.effective_start_date and paam2.effective_end_date) as SECOND_MANAGER_PHONE_NUMBER,
to_char(GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4|| '-' || GCC.SEGMENT5 || '-' || GCC.SEGMENT6 || '-' || GCC.SEGMENT7 || '-' || GCC.SEGMENT8 || '-' || GCC.SEGMENT9) "EXPENSE ACCOUNT",
(CASE when flattened_tree.LVL1_dept_depth <= dept_tree.depth then flattened_tree.LVL1_DEPT else '' end ) AS LVL1_DEPT, 
(CASE when flattened_tree.LVL2_dept_depth <= dept_tree.depth then flattened_tree.LVL2_DEPT else '' end ) AS LVL2_DEPT,
(CASE when flattened_tree.LVL3_dept_depth <= dept_tree.depth then flattened_tree.LVL3_DEPT else '' end ) AS LVL3_DEPT,
(CASE when flattened_tree.LVL4_dept_depth <= dept_tree.depth then flattened_tree.LVL4_DEPT else '' end ) AS LVL4_DEPT,
(CASE when flattened_tree.LVL5_dept_depth <= dept_tree.depth then flattened_tree.LVL5_DEPT else '' end ) AS LVL5_DEPT,
(CASE when flattened_tree.LVL6_dept_depth <= dept_tree.depth then flattened_tree.LVL6_DEPT else '' end ) AS LVL6_DEPT,
(CASE when flattened_tree.LVL7_dept_depth <= dept_tree.depth then flattened_tree.LVL7_DEPT else '' end ) AS LVL7_DEPT,
(CASE when flattened_tree.LVL8_dept_depth <= dept_tree.depth then flattened_tree.LVL8_DEPT else '' end ) AS LVL8_DEPT,
(CASE when flattened_tree.LVL9_dept_depth <= dept_tree.depth then flattened_tree.LVL9_DEPT else '' end ) AS LVL9_DEPT
--flattened_tree.LVL0_DEPT,flattened_tree.LVL0_dept_depth,
--flattened_tree.LVL1_DEPT,flattened_tree.LVL1_dept_depth,
--flattened_tree.LVL2_DEPT,flattened_tree.LVL2_dept_depth,
--flattened_tree.LVL3_DEPT,flattened_tree.LVL3_dept_depth,
--flattened_tree.LVL4_DEPT,flattened_tree.LVL4_dept_depth,
--flattened_tree.LVL5_DEPT,flattened_tree.LVL5_dept_depth,
--flattened_tree.LVL6_DEPT,flattened_tree.LVL6_dept_depth,
--flattened_tree.LVL7_DEPT,flattened_tree.LVL7_dept_depth,
--flattened_tree.LVL8_DEPT,flattened_tree.LVL8_dept_depth,
--flattened_tree.LVL9_DEPT,flattened_tree.LVL9_dept_depth,
--(CASE when flattened_tree.LVL8_DEPT <> dept.name then '' end)LVL8_DEPT,
--(CASE when flattened_tree.LVL9_DEPT <> dept.name then '' end)LVL9_DEPT
--dept_tree.depth,
--dept_tree.dept_name
from GL_CODE_COMBINATIONS GCC,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_JOBS_F_TL PJF,
PER_PERSON_TYPES_TL PPTT,
per_periods_of_service ppos,
flattened_tree,
per_assignment_supervisors_f pasf,
( SELECT hauft.organization_id,
               hauft.NAME
          FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 
               HR_ALL_ORGANIZATION_UNITS_F haouf, 
               HR_ORGANIZATION_UNITS_F_TL hauft 
         WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 
           AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 
           AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE 
           AND hauft.LANGUAGE = 'US'
           AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE 
           AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 
           AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT' 
           AND TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
           ) dept,
   (SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT (
                               SELECT p.name
                               FROM hr_all_organization_units_vl p
                               WHERE p.organization_id = ot.parent_organization_id
                                      AND rownum = 1
                               ) Parent_Dept_Name
                       ,(
                               SELECT c.name
                               FROM hr_all_organization_units_vl c
                               WHERE c.organization_id = ot.organization_id
                                      AND rownum = 1
                               ) Dept_Name
   ,(
                               SELECT c.organization_id
                               FROM hr_all_organization_units_vl c
                               WHERE c.organization_id = ot.organization_id
                                      AND rownum = 1
                               ) Dept_id
                       ,ot.tree_structure_code
                       ,ot.parent_organization_id parent_org_id
                       ,ot.organization_id org_id
   ,ot.depth
                       ,LEVEL levelcount
               FROM PER_DEPT_TREE_NODE_V ot
               WHERE ot.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
                       AND ot.tree_code = 'Enter Department Tree Name' START
               WITH ot.parent_organization_id IS NULL CONNECT BY PRIOR ot.organization_id = ot.parent_organization_id
               )
        ORDER BY levelcount ASC) dept_tree
where gcc.CODE_COMBINATION_ID(+)=paam.DEFAULT_CODE_COMB_ID 
and paam.person_id=papf.person_id 
and papf.person_id = ppos.person_id (+)
and PJF.Job_id (+)= PAAM.Job_id
and PPTT.Person_Type_id = PAAM.person_type_id
and paam.assignment_type='E'
and paam.effective_start_date=(SELECT MAX (EFFECTIVE_START_DATE) FROM PER_ALL_ASSIGNMENTS_M paam2 where paam.ASSIGNMENT_ID=paam2.ASSIGNMENT_ID)
and papf.person_id=ppnf.person_id
and ppnf.name_type='GLOBAL'
and paam.assignment_id = pasf.assignment_id (+)
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 trunc(sysdate) between  pasf.effective_start_date and pasf.effective_end_date
AND paam.organization_id = dept.organization_id
AND paam.organization_id = dept_tree.dept_id
/*
and ((paam.organization_id = flattened_tree.LVL1_DEPT)
 OR (paam.organization_id = flattened_tree.LVL2_DEPT)
  OR (paam.organization_id = flattened_tree.LVL3_DEPT)
   OR (paam.organization_id = flattened_tree.LVL4_DEPT)
    OR (paam.organization_id = flattened_tree.LVL5_DEPT)
     OR (paam.organization_id = flattened_tree.LVL6_DEPT)
      OR (paam.organization_id = flattened_tree.LVL7_DEPT)
       OR (paam.organization_id = flattened_tree.LVL8_DEPT)
    OR (paam.organization_id = flattened_tree.LVL9_DEPT)
)*/
and ((dept.name = flattened_tree.LVL1_DEPT)
 OR (dept.name = flattened_tree.LVL2_DEPT)
  OR (dept.name = flattened_tree.LVL3_DEPT)
   OR (dept.name = flattened_tree.LVL4_DEPT)
    OR (dept.name = flattened_tree.LVL5_DEPT)
     OR (dept.name = flattened_tree.LVL6_DEPT)
      OR (dept.name = flattened_tree.LVL7_DEPT)
       OR (dept.name = flattened_tree.LVL8_DEPT)
    OR (dept.name = flattened_tree.LVL9_DEPT)
)
--and papf.person_number='10005'
--and rownum=1
order by 1