Employee Rehire Info (SQL Script)

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

This Employee Rehire Info sql query displays rehire details for employees across organizations as of today, helping users to track workforce re-engagement and support HR planning.

#sqlquery

SELECT 
--SQL4FUSION (AN ORBIT ANALYTICS PROJECT)
--Fetches employee rehire details with organization info
       HAOU.NAME ORGANIZATION,
       PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
       PPNFV.FULL_NAME EMPLOYEE_NAME,
       PPOS.ORIGINAL_DATE_OF_HIRE,
       PPOS.DATE_START REHIRE_DATE,
       PPOS.REHIRE_REASON,
       PPOS.REHIRE_RECOMMENDATION,
       PPTUM.SYSTEM_PERSON_TYPE USER_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     PAPF.PERSON_ID = PAAM.PERSON_ID
      AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
      AND PPOS.PERSON_ID = PAAM.PERSON_ID
      AND PAPF.PERSON_ID = PPNFV.PERSON_ID
      AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
      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 SYSDATE BETWEEN PPTUM.EFFECTIVE_START_DATE AND PPTUM.EFFECTIVE_END_DATE
      AND PAAM.ASSIGNMENT_TYPE = 'E' --Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms.
      AND EXISTS (
            SELECT 'X'
              FROM PER_PERIODS_OF_SERVICE PPOS1
             WHERE PPOS1.PERSON_ID = PAPF.PERSON_ID
               AND PPOS1.DATE_START < PPOS.DATE_START
               AND PPOS1.ACTUAL_TERMINATION_DATE IS NOT NULL
      );