Purpose of the Query
The purpose of this query is to retrieve the current active and inactive employee details from Oracle Fusion HCM using SQL.
Sql query
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
Tables Used
PER_ALL_ASSIGNMENTS_M (PAAM)
This table stores employee assignment-related information but we need to use certain condition to get the perfect output like
trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
SYSDATE returns the current date and time from the database server.
The TRUNC() function removes the time portion and keeps only the date.
BETWEEN checks whether a value falls within a range (inclusive of both the start and end values).
Use effective_latest_change = ‘Y’ indicates that this row represents the latest change in the day.
primary_flag = ‘Y’ represents the primary assignment associated to the primary Work Relationship and primary set of Employment.
assignment_type=’E’ Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms.
assignment_status_type in(‘ACTIVE’,’INACTIVE’) indicates the current status of an employee’s assignment
PER_ALL_PEOPLE_F (PAPF)
This table stores employee (person) information. Like (PERSON_ID, PERSON_NUMBER)
PER_PERSON_NAMES_F (PPNF)
This table stores employee names. Like (Fast_name,last_name,display_name)
Use NAME_TYPE = ‘GLOBAL’ for consistent employee names in reports.
Output
