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.
