Introduction:
This SQL query is used to fetching the data for Employees who are Revoked means cancelled the termination those employees will fetch based on last update date.
SQL Query:
SELECT DISTINCT
papf.person_number,
CASE
WHEN pao.action_date > sysdate THEN
to_char(pao.last_update_date, ‘DD-MM-YYYY’)
ELSE
to_char(pao.action_date, ‘DD-MM-YYYY’)
END Revoked_date,
TO_CHAR(pao.last_update_date, ‘DD-MM-YYYY HH24:MI:SS’) last_update_date,
pao.last_updated_by Revoked_by
FROM
per_action_occurrences pao,
per_all_people_f papf,
per_actions_vl pav
WHERE
pao.action_type_code = ‘ORA_EMPL_REV_TERMINATION’
AND pao.parent_entity_type = ‘PERSON’
AND pao.parent_entity_key_id = papf.person_id
AND NOT EXISTS (
SELECT
1
FROM
per_all_assignments_m
WHERE
person_id = pao.parent_entity_key_id
AND action_occurrence_id = pao.action_occurrence_id
)
AND trunc(pao.last_update_date) >= trunc(to_date(:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’))
AND pao.last_update_date = (
SELECT
MAX(last_update_date)
FROM
per_action_occurrences
WHERE
parent_entity_key_id = pao.parent_entity_key_id
AND parent_entity_type = ‘PERSON’
AND action_type_code = ‘ORA_EMPL_REV_TERMINATION’
)
AND pao.action_id = pav.action_id
