Query to find terminated employees in oracle apps

Query to find terminated employees in oracle apps

In this post , We will be discuss about sql query to find the terminated employees in oracle apps. Terminated employee is which whose period of service is end dated or whose employment has been ended in the system. To find the terminated employees in oracle apps system , We need to use one important 'per_periods_of_service' table which helps of store the Employment track in oracle apps.Here below I will share the Tables and the sql Query to find terminated employees in oracle apps.

2 Basic Tables of sql Query of Employee Termination

1. per_all_people_f
2.per_periods_of_service

Complete SQL Query to find terminated employees in oracle apps

select papf.employee_number "Employee Number",
papf.title "Title",
papf.first_name "First Name",
papf.last_name "Last Name",
papf.national_identifier,
papf.email_address "E-mail",
TO_CHAR(papf.effective_start_date, 'DD-MON-RRRR') "Start Date",
TO_CHAR(papf.effective_end_date, 'DD-MON-RRRR') "End Date",
TO_CHAR(papf.original_date_of_hire, 'DD-MON-RRRR') "Hire Date"
from apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE 1=1
AND papf.person_id = ppos.person_id
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id
FROM per_periods_of_service
WHERE person_id = papf.person_id)

AND ppos.actual_termination_date < TRUNC(SYSDATE)