Employee Termination Info (SQL Script)

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

This Employee Termination Info sql query displays termination details and person type classifications for ex-employees across organizations as of today, helping users track workforce exits for HR reporting and compliance.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
       --This query shows exit details by person type for each organization
       HAOU.NAME ORGANIZATION,
       PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
       PPNFV.FULL_NAME EMPLOYEE_NAME,
       PPOS.ORIGINAL_DATE_OF_HIRE,
       PPOS.LAST_WORKING_DATE,
       PPOS.ACTUAL_TERMINATION_DATE,
       PPTUM.SYSTEM_PERSON_TYPE
  FROM PER_ALL_PEOPLE_F PAPF,
       PER_ALL_ASSIGNMENTS_M PAAM,
       PER_PERIODS_OF_SERVICE PPOS,
       PER_PERSON_TYPES PPT,
       PER_PERSON_TYPE_USAGES_M PPTUM,
       PER_PERSON_NAMES_F_V PPNFV,
       HR_ALL_ORGANIZATION_UNITS HAOU
 WHERE     1 = 1
       AND PAPF.PERSON_ID = PAAM.PERSON_ID
       AND PPOS.PERSON_ID = PAAM.PERSON_ID
       AND PAPF.PERSON_ID = PPNFV.PERSON_ID
       AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
       AND PAAM.ASSIGNMENT_TYPE = 'E' --Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms.
       AND PPTUM.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
       AND PPTUM.PERSON_ID = PAPF.PERSON_ID
       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PPTUM.EFFECTIVE_START_DATE AND PPTUM.EFFECTIVE_END_DATE
       AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
       AND UPPER(PPTUM.SYSTEM_PERSON_TYPE) IN ('EX_EMP') -- Considering only EX-employees
       AND PPOS.LAST_WORKING_DATE IS NOT NULL;