In any typical ERP Implementation, there are requirements to prepare multiple reports. Most of the Reports try to display the Assignment related details like Job, Grade, Location, Position, Department, BusinessUnit, Assignment Status along with other fields from different Tables.
Chances are one would have to use the below columns in one or more report:
- Employee’s Legal Employer
- Employee’s Business Unit
- Employee’s Department
- Employee’s Job
- Employee’s Grade
- Employee’s Position
- Employee’s Location
- Employee’s Assignment Status
- Employee’s User Name
- Employee’s Supervisor
This is just a small list and one may keep adding to it, but hopefully this is a good start.
In this article we would provide the SQL query for each of the above mentioned field and even have a look to confirm whether the query runs fine and provides desired results.
So without much ado we will get started.
SQL to Fetch Employee’s Legal Employer Name
Legal Employer is basically an Organization with an Org Classification as ‘HCM_LEMP’
Technically speaking, Legal Employer field is tagged with the LEGAL_ENTITY_ID field of PER_ALL_ASSIGNMENTS_M Table and is mapped to ORGANIZATION_ID column of HR_ALL_ORGANIZATION_UNITS_F Table. The Org Classification is stored in HR_ORG_UNIT_CLASSIFICATIONS_F Table and has ORGANIZATION_ID and CLASSIFICATION_CODE Database Columns present.
SQL to Fetch Employee’s Legal Employer
SELECT papf.person_number,
hauft.NAME LegalEmployer
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft,
per_all_assignments_m paam,
per_all_people_f papf
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND hauft.organization_id = paam.legal_entity_id
and paam.person_id = papf.person_id
and paam.primary_assignment_flag = 'Y'
and paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
and sysdate between paam.effective_start_date and paam.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,hauft.name asc nulls first
If we prepare a Report using the above SQL we will get an output which when formatted appropriately would appear as below:
SQL to Fetch Employee’s Business Unit
Business Unit is basically an Organization with an Org Classification as ‘FUN_BUSINESS_UNIT’
Technically speaking, Business Unit field is tagged with the BUSINESS_UNIT_ID field of PER_ALL_ASSIGNMENTS_M Table and is mapped to ORGANIZATION_ID column of HR_ALL_ORGANIZATION_UNITS_F Table. The Org Classification is stored in HR_ORG_UNIT_CLASSIFICATIONS_F Table and has ORGANIZATION_ID and CLASSIFICATION_CODE Database Columns present.
SQL to Fetch Employee’s Business Unit
SELECT papf.person_number,
hauft.NAME BusinessUnit
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft,
per_all_assignments_m paam,
per_all_people_f papf
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND hauft.organization_id = paam.business_unit_id
and paam.person_id = papf.person_id
and paam.primary_assignment_flag = 'Y'
and paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
and sysdate between paam.effective_start_date and paam.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,hauft.name asc nulls first
If we prepare a Report using the above SQL we will get an output which when formatted appropriately would appear as below:
SQL to Fetch Employee’s Department Name
The Assignment Organization associated with Employee’s Assignment Record is referred to as Department. This has a Org Classification of DEPARTMENT.
SQL to Fetch Employee’s Department Name
SELECT papf.person_number,
hauft.NAME Department
FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft,
per_all_assignments_m paam,
per_all_people_f papf
WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'
AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date
AND hauft.organization_id = paam.organization_id
and paam.person_id = papf.person_id
and paam.primary_assignment_flag = 'Y'
and paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
and sysdate between paam.effective_start_date and paam.effective_end_date
and sysdate between papf.effective_start_date and papf.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,hauft.name asc nulls first
Formatted Report Output:
SQL to Fetch Employee’s Job Name
PER_ALL_ASSIGNMENTS_M Table has a column named JOB_ID. When JOB_ID is joined with PER_JOBS_F table we get the Job Related details. For fetching the Job Name we need to use the Translation Table (PER_JOBS_F_TL) which holds NAME column
SQL to Fetch Employee’s Job Name
SELECT papf.person_number,
pjft.name jobname
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_jobs_f pjf,
per_jobs_f_tl pjft
WHERE papf.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.job_id = pjf.job_id
AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date
AND pjf.job_id = pjft.job_id
AND pjft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,pjft.name asc nulls first
Report Output:
SQL to Fetch Employee’s Grade Name
PER_ALL_ASSIGNMENTS_M Table has a column named GRADE_ID. When GRADE_ID is joined with PER_GRADES_F table we get the Grade Related details. For fetching the Grade Name we need to use the Translation Table (PER_GRADES_F_TL) which holds NAME column
SQL to Fetch Employee’s Grade Name
SELECT papf.person_number,
pgft.name gradename
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_grades_f pgf,
per_grades_f_tl pgft
WHERE papf.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
AND paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.grade_id = pgf.grade_id
AND TRUNC(SYSDATE) BETWEEN pgf.effective_start_date AND pgf.effective_end_date
AND pgf.grade_id = pgft.grade_id
AND pgft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,pgft.name asc nulls first
Report Output:
SQL to Fetch Employee’s Position Name
PER_ALL_ASSIGNMENTS_M Table has a column named GRADE_ID. When GRADE_ID is joined with PER_GRADES_F table we get the Grade Related details. For fetching the Grade Name we need to use the Translation Table (PER_GRADES_F_TL) which holds NAME column
SQL to Fetch Employee’s Position Name
SELECT papf.person_number,
hapft.name positionname
FROM per_all_people_f papf,
per_all_assignments_m paam,
hr_all_positions_f hapf,
hr_all_positions_f_tl hapft
WHERE papf.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
AND paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.position_id = hapf.position_id
AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
AND hapf.position_id = hapft.position_id
AND hapft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,hapft.name asc nulls first
Report Output:
SQL to Fetch Employee’s Location
PER_ALL_ASSIGNMENTS_M Table has a column named LOCATION_ID. When LOCATION_ID is joined with PER_LOCATION_DETAILS_F table we get the Location Related details. For fetching the Location Name we need to use the Translation Table (PER_LOCATION_DETAILS_F_TL) which has the required details.
SQL to Fetch Employee’s Location
SELECT papf.person_number,
pldft.location_name locationname
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_location_details_f pldf,
per_location_details_f_tl pldft
WHERE papf.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
AND paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.location_id = pldf.location_id
AND TRUNC(SYSDATE) BETWEEN pldf.effective_start_date AND pldf.effective_end_date
AND pldf.location_details_id = pldft.location_details_id
AND pldft.language = 'US'
AND TRUNC(SYSDATE) BETWEEN pldft.effective_start_date AND pldft.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,pldft.location_name asc nulls first
Report Output:
SQL to Fetch Employee’s Assignment Status
PER_ALL_ASSIGNMENTS_M Table has a column named ASSIGNMENT_STATUS_TYPE_ID. When ASSIGNMENT_STATUS_TYPE_ID is joined with PER_ASSIGNMENT_STATUS_TYPES table we get the Assignment Status Related details. For fetching the Assignment Status (USER_STATUS) we need to use the Translation Table (PER_ASSIGNMENT_STATUS_TYPES_TL) which has the required details.
SQL to Fetch Employee’s Assignment Status
SELECT papf.person_number,
pastt.user_status assignmentstatus
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_assignment_status_types past,
per_assignment_status_types_tl pastt
WHERE papf.person_id = paam.person_id
AND paam.assignment_status_type_id = past.assignment_status_type_id
AND past.assignment_status_type_id = pastt.assignment_status_type_id
AND pastt.source_lang = 'US'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)
AND papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,pastt.user_status asc nulls first
Report Output:
SQL to Fetch Employee’s User Name
We use the PER_USERS table which has PERSON_ID column which can be joined with PER_ALL_PEOPLE_F to fetch required details.
SQL to Fetch Employee’s User Name
select papf.person_number,
pu.username
from per_all_people_f papf,
per_users pu
WHERE papf.person_id = pu.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,pu.username asc nulls first
Report Output:
SQL to fetch Employee’s Supervisor Name
PERSON_ID column is present in PER_ALL_PEOPLE_F (Employee Table) and PER_ASSIGNMENT_SUPERVISORS_F (Assignment Supervisor Table). When we join the MANAGER_ID column with PERSON_ID column of PER_PERSON_NAMES_F (Table to fetch Supervisor Name). The Manager Type to be used is LINE_MANAGER.
SQL to Fetch Employee’s Supervisor
SELECT papf.person_number,
ppnf.full_name supervisorname
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_assignment_supervisors_f pasf,
per_person_names_f ppnf
WHERE papf.person_id = paam.person_id
AND paam.primary_assignment_flag = 'Y'
AND paam.assignment_type = 'E'
and paam.effective_latest_change = 'Y'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND papf.person_id = pasf.person_id
AND pasf.manager_type = 'LINE_MANAGER'
AND ppnf.person_id = pasf.manager_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
and papf.person_number = nvl(:personnumber,papf.person_number)
order by papf.person_number asc,ppnf.full_name asc nulls first
Report Output:
Summary
All the above Re-usable SQLs has Person Number is an Optional Input Parameter. When an input is passed only records pertaining to that Person Number is fetched else all the records would be displayed.