Oracle Queries

Direct and Indirect Reports for a Manager

By May 22, 2020 April 29th, 2021 No Comments

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 –





tables to get the manager information of an employee.

SELECT ppnf_emp.full_name
,papf_sup.person_number manager_number
,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.