• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:August 4, 2021
  • Reading time:2 mins read
You are currently viewing How to fetch the flattened Supervisor Hierarchy for employees using SQL Query?
How to fetch the flattened Supervisor Hierarchy for employees using SQL Query?
- How to fetch the flattened Supervisor Hierarchy for employees using SQL Query?
Total Visits: 13

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:

image 1024x559 - How to fetch the flattened Supervisor Hierarchy for employees using SQL Query?

Hope you will find the query useful. Feel free to modify the query as per your requirement.

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.