PER_MANAGER_HEADCOUNT

Details

Schema: FUSION
Object owner: PER
Object type: VIEW

Columns

Name
MANAGER_ID MANAGER_ASSIGNMENT_ID MANAGER_TYPE TOTAL_HEADCOUNT TOTAL_EMP_HEADCOUNT TOTAL_CWK_HEADCOUNT TOTAL_NWK_HEADCOUNT TOTAL_PWK_HEADCOUNT DIRECTS_HEADCOUNT DIRECTS_EMP_HEADCOUNT DIRECTS_CWK_HEADCOUNT DIRECTS_NWK_HEADCOUNT DIRECTS_PWK_HEADCOUNT

Name

MANAGER_ID

MANAGER_ASSIGNMENT_ID

MANAGER_TYPE

TOTAL_HEADCOUNT

TOTAL_EMP_HEADCOUNT

TOTAL_CWK_HEADCOUNT

TOTAL_NWK_HEADCOUNT

TOTAL_PWK_HEADCOUNT

DIRECTS_HEADCOUNT

DIRECTS_EMP_HEADCOUNT

DIRECTS_CWK_HEADCOUNT

DIRECTS_NWK_HEADCOUNT

DIRECTS_PWK_HEADCOUNT

Query

SQL_Statement
SELECT HIER.MANAGER_ID, HIER.MANAGER_ASSIGNMENT_ID, HIER.MANAGER_TYPE, COUNT(NVL(WM.VALUE, HIER.PERSON_ID)) TOTAL_HEADCOUNT, COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'E' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_EMP_HEADCOUNT, COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'C' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_CWK_HEADCOUNT, COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'N' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_NWK_HEADCOUNT, COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'P' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_PWK_HEADCOUNT, COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_HEADCOUNT, COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'E' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_EMP_HEADCOUNT, COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'C' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_CWK_HEADCOUNT, COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'N' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_NWK_HEADCOUNT, COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'P' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_PWK_HEADCOUNT FROM PER_MANAGER_HRCHY_DN HIER, PER_ALL_ASSIGNMENTS_M ASSIGN, PER_ASSIGN_WORK_MEASURES_F WM WHERE HIER.PERSON_ID = ASSIGN.PERSON_ID AND HIER.ASSIGNMENT_ID = ASSIGN.ASSIGNMENT_ID AND TRUNC(SYSDATE) BETWEEN ASSIGN.EFFECTIVE_START_DATE AND ASSIGN.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN HIER.EFFECTIVE_START_DATE AND HIER.EFFECTIVE_END_DATE AND ASSIGN.ASSIGNMENT_TYPE IN ('E', 'C', 'P', 'N') AND ASSIGN.EFFECTIVE_LATEST_CHANGE = 'Y' AND ASSIGN.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND ASSIGN.ASSIGNMENT_ID = WM.ASSIGNMENT_ID (+) AND TRUNC(SYSDATE) BETWEEN WM.EFFECTIVE_START_DATE (+) AND WM.EFFECTIVE_END_DATE (+) AND WM.UNIT (+) = 'HEAD' GROUP BY HIER.MANAGER_ID, HIER.MANAGER_ASSIGNMENT_ID, HIER.MANAGER_TYPE

SQL_Statement

SELECT HIER.MANAGER_ID,

HIER.MANAGER_ASSIGNMENT_ID,

HIER.MANAGER_TYPE,

COUNT(NVL(WM.VALUE, HIER.PERSON_ID)) TOTAL_HEADCOUNT,

COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'E' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_EMP_HEADCOUNT,

COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'C' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_CWK_HEADCOUNT,

COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'N' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_NWK_HEADCOUNT,

COUNT(CASE WHEN ASSIGN.ASSIGNMENT_TYPE = 'P' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) TOTAL_PWK_HEADCOUNT,

COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_HEADCOUNT,

COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'E' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_EMP_HEADCOUNT,

COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'C' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_CWK_HEADCOUNT,

COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'N' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_NWK_HEADCOUNT,

COUNT(CASE WHEN HIER.MANAGER_LEVEL = 1 AND ASSIGN.ASSIGNMENT_TYPE = 'P' THEN NVL(WM.VALUE, HIER.PERSON_ID) END) DIRECTS_PWK_HEADCOUNT

FROM PER_MANAGER_HRCHY_DN HIER,

PER_ALL_ASSIGNMENTS_M ASSIGN,

PER_ASSIGN_WORK_MEASURES_F WM

WHERE HIER.PERSON_ID = ASSIGN.PERSON_ID

AND HIER.ASSIGNMENT_ID = ASSIGN.ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN ASSIGN.EFFECTIVE_START_DATE AND ASSIGN.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN HIER.EFFECTIVE_START_DATE AND HIER.EFFECTIVE_END_DATE

AND ASSIGN.ASSIGNMENT_TYPE IN ('E', 'C', 'P', 'N')

AND ASSIGN.EFFECTIVE_LATEST_CHANGE = 'Y'

AND ASSIGN.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

AND ASSIGN.ASSIGNMENT_ID = WM.ASSIGNMENT_ID (+)

AND TRUNC(SYSDATE) BETWEEN WM.EFFECTIVE_START_DATE (+) AND WM.EFFECTIVE_END_DATE (+)

AND WM.UNIT (+) = 'HEAD'

GROUP BY HIER.MANAGER_ID,

HIER.MANAGER_ASSIGNMENT_ID,

HIER.MANAGER_TYPE