Downloaded 99 times
Uploaded byFeras Ahmad
Oracle Cloud SQL FTE Positions Query
This SQL query joins data from multiple tables to retrieve employee information including the person number, name, job, department, termination date, and FTE. It limits the results to active US employees by checking that the effective dates for each record being joined overlap with the current date.
Oracle Cloud SQL FTE Positions Query
- 1. select papf.person_number "PersonNumber", ppnf.Display_name "Employee Name", pjft.name "Job Name", houft.name "Department Name", ppos.actual_termination_date "Employee Termination Date", hapf.fte fte from per_person_names_f ppnf ,per_all_people_f papf ,per_all_assignments_m paam ,per_jobs_f pjf ,per_jobs_f_tl pjft ,hr_all_organization_units_f haouf ,hr_org_unit_classifications_f houcf ,hr_organization_units_f_tl houft ,per_periods_of_service ppos ,HR_ALL_POSITIONS_F hapf where 1 = 1 and paam.position_id = hapf.position_id and papf.person_id = ppnf.person_id and papf.person_id = paam.person_id and paam.job_id = pjf.job_id and pjf.job_id = pjft.job_id and paam.primary_assignment_flag = 'Y' and paam.organization_id = haouf.organization_id and haouf.organization_id = houcf.organization_id and haouf.organization_id = houft.organization_id and papf.person_id = ppos.person_id AND houft.language = 'US' and pjft.language = 'US' and ppnf.name_type = 'GLOBAL' and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_date and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date and trunc(sysdate) between haouf.effective_start_date and haouf.effective_end_date and trunc(sysdate) between houcf.effective_start_date and houcf.effective_end_date and trunc(sysdate) between houft.effective_start_date and houft.effective_end_date