Employee Absence Case (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This Employee Absence Case query displays assignment, organizational, and absence details for active, primary employees as of today, helping users validate current workforce status and filter out non-standard worker types for reporting or HR operations.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Active employees with recorded absence cases and organizational assignment. 
  PAPF.PERSON_NUMBER, 
  PPNFV.FULL_NAME EMPLOYEE_NAME,
  PAAM.ASSIGNMENT_NUMBER,
  PAAM.ASSIGNMENT_STATUS_TYPE ASSIGNMENT_STATUS,
  HAOU.NAME ORGANIZATION_NAME,
  PAC.NAME ABSENCE_CASE_NAME
FROM
  PER_PERSON_NAMES_F_V PPNFV,
  PER_ALL_ASSIGNMENTS_M PAAM,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  PER_ALL_PEOPLE_F PAPF,
  PER_ABSENCE_CASES PAC
WHERE
  PPNFV.PERSON_ID = PAPF.PERSON_ID
  AND PAC.PERSON_ID = PPNFV.PERSON_ID
  AND PAPF.PERSON_ID = PAAM.PERSON_ID
  AND PAAM.ASSIGNMENT_TYPE NOT IN ('ET', 'CT', 'PT') 
  AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 
  AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
  AND PAAM.PRIMARY_FLAG = 'Y' 
  AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y' 
  AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE;