SQL Query to Retrieve Terminated Employee Details with Rehire Recommendation in Oracle HCM

This query retrieves key data for employees who have been terminated, using three primary Oracle HCM tables: PER_ALL_PEOPLE_F, PER_PERSON_NAMES_F, and PER_PERIODS_OF_SERVICE. It uses a smart filter to match multiple person numbers from a single input string and limits name data to the current effective date range and global name type.

The join logic ensures that only valid, currently effective names are shown and that termination-related data is fetched correctly. The rehire recommendation field is particularly useful for workforce planning or compliance reviews.

This query is especially valuable when HR needs to quickly assess or validate termination details for a specific group of employees. By passing multiple person numbers via a parameter, it allows flexible and scalable querying without modifying the SQL.

SELECT
    papf.person_number,
    pnf.first_name,
    pnf.last_name,
    ppos.rehire_recommendation,
    TO_CHAR(ppos.actual_termination_date, 'MM/DD/YYYY') AS actual_termination_date,
    ppos.created_by AS submitted_by_username
FROM
    per_all_people_f papf
JOIN
    per_person_names_f pnf
    ON papf.person_id = pnf.person_id
    AND SYSDATE BETWEEN pnf.effective_start_date AND pnf.effective_end_date
    AND pnf.name_type = 'GLOBAL'
JOIN
    per_periods_of_service ppos
    ON papf.person_id = ppos.person_id
WHERE
    ppos.actual_termination_date IS NOT NULL
    AND ',' || :P_PERSON_NUMBERS || ',' LIKE '%,' || papf.person_number || ',%'

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