SQL Query to Identify Terminated Employees in Oracle HCM

SELECT  
    PER_PERIODS_OF_SERVICE.LAST_UPDATE_DATE AS LAST_UPDATE_DATE,
    PER_PERIODS_OF_SERVICE.LAST_UPDATED_BY AS LAST_UPDATED_BY,
    PER_ALL_PEOPLE_F.PERSON_NUMBER AS PERSON_NUMBER
FROM 
    FUSION.PER_ALL_PEOPLE_F PER_ALL_PEOPLE_F,
    FUSION.PER_PERIODS_OF_SERVICE PER_PERIODS_OF_SERVICE
WHERE 
    PER_PERIODS_OF_SERVICE.PERSON_ID = PER_ALL_PEOPLE_F.PERSON_ID
ORDER BY 
    PER_PERIODS_OF_SERVICE.LAST_UPDATE_DATE DESC;

Optional Enhancement – Filter by Actual Termination Date:

SELECT  
    PER_PERIODS_OF_SERVICE.LAST_UPDATE_DATE,
    PER_PERIODS_OF_SERVICE.LAST_UPDATED_BY,
    PER_ALL_PEOPLE_F.PERSON_NUMBER,
    PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE
FROM 
    FUSION.PER_ALL_PEOPLE_F PER_ALL_PEOPLE_F,
    FUSION.PER_PERIODS_OF_SERVICE PER_PERIODS_OF_SERVICE
WHERE 
    PER_PERIODS_OF_SERVICE.PERSON_ID = PER_ALL_PEOPLE_F.PERSON_ID
    AND PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE IS NOT NULL
ORDER BY 
    PER_PERIODS_OF_SERVICE.LAST_UPDATE_DATE DESC;

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.