SQL Query to Retrieve Employee Details with Department Hierarchy and Manager Information

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

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.

SQL Queries (SQLQ)
Posted by : Mohammad