In this article we will look into getting in the Direct and Indirect reportees of a Supervisor using SQL Query.
We assign the Supervisor on the Employment screen and they get stored in the PER_ASSIGNMENT_SUPERVISORS_F table. We will have to join the PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F tables to get the manager information of an employee.
However Fusion has a scheduled process “Refresh Manager Hierarchy” which needs to be scheduled daily to refresh data in PER_MANAGER_HRCHY_DN table. We will look into this process in detail in another post.
This table PER_MANAGER_HRCHY_DN stores the flattened manager hierarchy with the latest information retrieved from PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F tables.
Below are some of the important fields this tables fetches data:
So, we will use this supervisor hierarchy table to get the Direct and Indirect reportees now.
MANAGER_TYPE is a lookup code and we need to use the right MANAGER_TYPE to get the right hierarchy. Normally everyone uses the LINE_MANAGER hierarchy, However some clients create new manager types as per their requirement.
We can check the lookups that have been configured for Manager types using the below Query:
select * from hr_lookups where lookup_type = ‘PER_SUPERVISOR_TYPE’
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 --AND pmhd.manager_level = '1' -- Use 1 for direct reports, comment it for all reportees ORDER BY papf_emp.person_number
You can pass the Manager Person as input to the above query to get the reportees reporting to that manager.
There is one more condition which is commented above.. Manager_Level = ‘1’. Manager Level of ‘1’ indicates direct reportees to that manager and anything above ‘1’ will indicate indirect reportees.
You can also change the order by clause to order by reportees person number, name, or manager level.
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning Fusion Technical tools go through this post
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM