The purpose of this query is to retrieve the current active and inactive employee details from Oracle Fusion HCM using SQL.
SELECT papf.PERSON_NUMBER AS EMPLOYEE_NUMBER
,ppnf.DISPLAY_NAME AS EMPLOYEE_NAME
,paam.ASSIGNMENT_NUMBER AS ASSIGNMENT_NUMBER
FROM per_all_assignments_m paam
,per_person_names_f ppnf
,per_all_people_f papf
WHERE 1=1
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and paam.assignment_type=’E’
and paam.effective_latest_change = ‘Y’
and paam.assignment_status_type in(‘ACTIVE’,’INACTIVE’)
and paam.primary_flag = ‘Y’
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and paam.person_id = papf.person_id
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and ppnf.name_type = ‘GLOBAL’
and paam.person_id = ppnf.person_id