This Managers direct & indirect reportee query displays reporting relationships and managerial hierarchy details for employees as of the current date, enabling analysis of organizational structure and supporting workforce management decisions.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query displays the employee with their supervisor
HAOU.NAME ORGANIZATION_NAME,
PAPF_EMP.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF_EMP.FULL_NAME EMPLOYEE_NAME,
PAPF_SUP.PERSON_NUMBER MANAGER_NUMBER,
PPNF_SUP.FULL_NAME MANAGER_NAME,
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,
PAAM.ASSIGNMENT_NUMBER
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,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE 1 = 1
AND PMHD.PERSON_ID = PPNF_EMP.PERSON_ID
AND PPNF_EMP.PERSON_ID = PAPF_EMP.PERSON_ID
AND PAAM.PERSON_ID = PAPF_EMP.PERSON_ID
AND PAPF_SUP.PERSON_ID = PMHD.MANAGER_ID
AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_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 PAAM.EFFECTIVE_START_DATE AND PAAM.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
ORDER BY PAPF_EMP.PERSON_NUMBER,
PMHD.MANAGER_LEVEL;