In this article, we will look into how to query the backend tables to pull the flattened supervisor hierarchy of the employees on a single row.
Supervisor information is stored with respect to the assignment information in PER_ASSIGNMENT_SUPERVISORS_F table and is linked to PER_ALL_ASSIGNMENTS_M table by ASSIGNMENT_ID. It stores the supervisor information by MANAGER_TYPE and the most commonly used Manager Type is ‘LINE_MANAGER’.
Now, if Employee A has B as Manager, B has C as manager and C has D as manager, it is really tough to get the complete manager hierarchy B > C > D in a single query.
I have attempted to give it a try by modifying the query using various functions and was able to get this achieved. This query will pull supervisors until it reaches the top-level/CEO who would supervise all employees.
SQL Query to pull flattened supervisor hierarchy information:
SELECT PAPF.PERSON_NUMBER, PAAM.ASSIGNMENT_NUMBER, SUP.ASSIGNMENT_ID, SUP.REPORTING_PATH FROM ( SELECT ASSIGNMENT_ID, MAX(REPORTING_PATH) REPORTING_PATH FROM (SELECT SUBSTR(SYS_CONNECT_BY_PATH(FULL_NAME, ' -> '), 5) AS REPORTING_PATH, CONNECT_BY_ROOT ASSIGNMENT_ID AS ASSIGNMENT_ID FROM ( SELECT PASF.ASSIGNMENT_ID, PASF.MANAGER_ASSIGNMENT_ID, PPNF.FULL_NAME FROM PER_ASSIGNMENT_SUPERVISORS_F PASF JOIN PER_PERSON_NAMES_F PPNF ON PPNF.PERSON_ID = PASF.MANAGER_ID WHERE 1=1 AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE AND PASF.MANAGER_TYPE = 'LINE_MANAGER' AND PPNF.NAME_TYPE = 'GLOBAL' ) SUP WHERE 1=1 CONNECT BY SUP.ASSIGNMENT_ID = PRIOR SUP.MANAGER_ASSIGNMENT_ID ) GROUP BY ASSIGNMENT_ID) SUP, PER_ALL_ASSIGNMENTS_M PAAM, PER_ALL_PEOPLE_F PAPF WHERE SUP.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID AND PAAM.PERSON_ID = PAPF.PERSON_ID AND PAAM.ASSIGNMENT_TYPE = 'E' AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND PAAM.PRIMARY_FLAG = 'Y' AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y' AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
Here is how the output will look like:
Hope you will find the query useful. Feel free to modify the query as per your requirement.
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