SOME COMMONLY USED QUERIES IN ORACLE HCM CLOUD

 

SOME COMMONLY USED QUERIES IN ORACLE Fusion HCM CLOUD


Introduction

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:

  1. Employee’s Legal Employer

  2. Employee’s Business Unit

  3. Employee’s Department

  4. Employee’s Job

  5. Employee’s Grade

  6. Employee’s Position

  7. Employee’s Location

  8. Employee’s Assignment Status

  9. Employee’s User Name

  10. 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.

No comments:

Post a Comment

Subscribe to: Posts (Atom)