To get list of active employees in HRMS Oracle R12
When querying an employee number in per all people f table 2 records will be shown one is terminated record with end date which is less than sysdate and another record is upto 4712 year.
so this record wffective end date upto 4712 year will be shown active record. To eliminate and to find the active employee we should be query assignment table as below. The active record only will be shown.
Query:
select ppf.first_name,ppf.last_name,ppf.full_name,ppf.employee_number,ppf.person_id
from per_all_assignments_f paaf,per_assignment_status_types past, per_all_people_f ppf
where paaf.assignment_status_type_id = past.assignment_status_type_id
and ppf.person_id=paaf.person_id
and past.user_status = 'Active Assignment'
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
When querying an employee number in per all people f table 2 records will be shown one is terminated record with end date which is less than sysdate and another record is upto 4712 year.
so this record wffective end date upto 4712 year will be shown active record. To eliminate and to find the active employee we should be query assignment table as below. The active record only will be shown.
Query:
select ppf.first_name,ppf.last_name,ppf.full_name,ppf.employee_number,ppf.person_id
from per_all_assignments_f paaf,per_assignment_status_types past, per_all_people_f ppf
where paaf.assignment_status_type_id = past.assignment_status_type_id
and ppf.person_id=paaf.person_id
and past.user_status = 'Active Assignment'
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
