Direct and Indirect Reports for a Manager

In this query, you can get the details of Managers and their direct/indirect reports from Oracle HCM Cloud.

This query uses following tables from ORacle HCM CLoud –

PER_ASSIGNMENT_SUPERVISORS_F ,

PER_ALL_PEOPLE_F,

PER_ALL_ASSIGNMENTS_M and

PER_ASSIGNMENT_SUPERVISORS_F

tables to get the manager information of an employee.

SELECT ppnf_emp.full_name
,pmhd.person_id
,papf_emp.person_number
,pmhd.assignment_id
,pmhd.manager_id
,papf_sup.person_number manager_number
,pmhd.manager_assignment_id
,pmhd.manager_level
,pmhd.manager_type
,pmhd.effective_start_date
,pmhd.effective_end_date
,decode(pmhd.manager_level, '1', 'Direct Reportee', 'Indirect Reportee') Direct_Indirect
FROM per_manager_hrchy_dn pmhd
,per_person_names_f_v ppnf_emp
,per_all_people_f papf_emp
,per_all_people_f papf_sup
,per_person_names_f_v ppnf_sup
WHERE 1 = 1
AND pmhd.manager_type = 'LINE_MANAGER'
AND pmhd.person_id = ppnf_emp.person_id
AND ppnf_emp.person_id = papf_emp.person_id
AND papf_sup.person_id = pmhd.manager_id
AND ppnf_sup.person_id = pmhd.manager_id
AND ppnf_emp.name_type = 'GLOBAL'
AND ppnf_sup.name_type = 'GLOBAL'
AND sysdate BETWEEN papf_emp.effective_start_date
AND papf_emp.effective_end_date
AND sysdate BETWEEN papf_sup.effective_start_date
AND papf_sup.effective_end_date
AND sysdate BETWEEN ppnf_emp.effective_start_date
AND ppnf_emp.effective_end_date
AND sysdate BETWEEN ppnf_sup.effective_start_date
AND ppnf_sup.effective_end_date
AND sysdate BETWEEN pmhd.effective_start_date
AND pmhd.effective_end_date
AND papf_sup.person_number = :MANAGER_PERSON_NUMBER --Comment if you want all managers
AND pmhd.manager_level = '1' -- 1 for direct reports
ORDER BY papf_emp.person_number

And here is the output as it looks in HCM Demo environment, after running the query using SQLConnect.

Leave a Reply

Your email address will not be published. Required fields are marked *