Query to get supervisor Name in oracle Fusion

Query to get supervisor Name in oracle Fusion

Hi friends , we are going to discuss about the Query to get supervisor Name in oracle Fusion. We will share the detail sql query which helps to extract the complete employee detail in oracle fusion with supervisor information's. Using this sql query , we can able to extract the employee supervisor information in oracle fusion. In Oracle Fusion , supervisor information's stored in different DB table as compared to oracle EBS table. In Oracle apps , we can simple find the supervisor in the per_all_assignments_all table but in oracle fusion this same information is stored in the per_supervisor_f table. We will share the complete working sql query using this we can develop the custom Oracle fusion BIP report. We will also share some another important tables too which helps to keep the complete employees details with supervisor information in oracle fusion. Please find below the complete detail about the Query to get supervisor Name in oracle Fusion.

Table helps to store the supervisor name in Oracle Fusion 

Here below is the table helps to store the supervisor information for employee in oracle fusion.

1. per_assignment_supervisors_f

Query to get supervisor Name in oracle Fusion

7 important Tables helps to store the complete employee details in Oracle Fusion

Here below is the tables help to store the employee details in oracle fusion. Using these below tables we have developed the complete employee query.

1.per_all_people_f
2.per_all_assignments_m
3.per_jobs_f 
4.per_jobs_f_tl
5.PER_EMAIL_ADDRESSES
6.per_assignment_supervisors_f

7.per_person_names_f

Detail SQL Query to get supervisor Name in oracle Fusion

Here below is the detail sql query to extract the employee details with supervisor information's in oracle fusion. Please let us know if any concern on this.

SELECT papf.person_number  "Employee Number",
   pjft.name    "Employee Job Name",
PEA.EMAIL_ADDRESS "Email Address",
   ppnf.full_name "Supervisor Name",
 papf2.person_number "Supervisor Number",

hauft.NAME  "Employee BU",
PAPF.START_DATE "Emp Hire Date"
FROM   per_all_people_f papf,
 per_all_people_f papf2,
       per_all_assignments_m paam,
       per_jobs_f pjf,
   per_jobs_f_tl pjft,
GL_CODE_COMBINATIONS GCC,
PER_EMAIL_ADDRESSES PEA,
 per_assignment_supervisors_f pasf,
  per_person_names_f ppnf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE  papf.person_id = paam.person_id

AND    TRUNC(SYSDATE) BETWEEN papf2.effective_start_date AND papf2.effective_end_date

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    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 paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id

AND hauft.NAME=nvl(:P_BU_NAME,hauft.NAME)

AND    pjft.language = 'US'
AND    ppnf.name_type = 'GLOBAL'

AND    TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND hauft.organization_id = paam.business_unit_id

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 PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
AND    papf.person_id = pasf.person_id

AND    pasf.manager_type = 'LINE_MANAGER'

AND    ppnf.person_id = pasf.manager_id

AND  papf2.person_id = pasf.manager_id

AND    pjf.job_id = pjft.job_id

order by 1

Query to get supervisor Name in oracle Fusion