SQL Query to get rehire employees in oracle HRMS : Query to get the rehire employees in oracle apps

SQL Query to get rehire employees in oracle HRMS : Query to get the rehire employees in oracle apps

In this post , we will be discuss about the SQL query to get the rehire employees in oracle apps. This is one of the use full SQL query , if we want to know the number of employees who got rehired in oracle apps. This helps to extract the list of employees where terminated in past and then got rehired again. Here below , i will share the important tables and SQL query which helps to fetch the  rehire employee records in oracle apps.

Important Tables uses in the Query to get the rehire employees in oracle apps

1.per_all_people_f

2.per_periods_of_service

Detail SQL query for extracting the rehire employees in oracle apps

select pap.employee_number "Employee Number",

pap.title "Title",

pap.first_name "First Name",

pap.last_name "Last Name",

pap.date_of_birth,

pap.email_address "E-mail",

pap.Original_Date_of_hire "Original Date of hire",

(select max(pas.date_start) FROM per_periods_of_service pas where person_id=pap.person_id) "New Date of Hire"

from apps.per_all_people_f pap

where 1=1--person_id=66430

and effective_end_date>sysdate

and Original_Date_of_hire<>(select max(pas.date_start) FROM per_periods_of_service pas where person_id=pap.person_id)