Employee supervisor query in Oracle apps

Employee supervisor query in Oracle apps

In this post, We will be discuss about the employee supervisor query. This query will extract the employee supervisor hierarchy from top level to bottom level in oracle apps. This query will help to share the employee supervisor information’s. This is one of the important sql query to extract the employee with supervisory information’s. Here below I will share the one of the important tables used by this query and the detail employee supervisor query.

Most Important Tables used by employee supervisor query

1.per_people_x 

2.per_assignments_X

3.per_jobs

4.per_job_definitions

5.hr_positions_X 

6.per_position_definitions

Detail employee supervisor query

Here below is the Employee supervisor hierarchy , we need to pass the employee person id who is top in supervisor hierarchy as a parameter for extract the employee supervisor information in oracle apps.

select ppf.full_name employee_name,(select segment3 from APPS.gl_code_combinations where code_combination_id=paf.DEFAULT_CODE_COMB_ID) EMPLOYEE_COST_CENTER,

       (select full_name from apps.per_people_x where person_id = a.supervisor_id) supervisor_name, a.supervisor_id,

       pjd.segment1 employee_job, ppd.segment2 employee_position,

       ppf.email_address,

      a.hLevel

from

     apps.per_people_x ppf, apps.per_assignments_X paf,

     apps.per_jobs pj, apps.per_job_definitions pjd, apps.hr_positions_X hp, apps.per_position_definitions ppd,

     (              

               select paf1.assignment_id, paf1.person_id, paf1.supervisor_id, level hlevel

               from  apps.per_all_assignments_f paf1

               start with paf1.person_id = :person_id -- enter the employee person id who is top in supervior hierarchy--

               and   paf1.primary_flag = 'Y'

               and   sysdate between paf1.effective_start_date and paf1.effective_end_date

               connect by NOCYCLE  PRIOR paf1.person_id = paf1.supervisor_id

               and   paf1.primary_flag = 'Y'

               and   sysdate between paf1.effective_start_date and paf1.effective_end_date

      )a

where ppf.person_id = a.person_id

  and ppf.person_id = paf.person_id   

  and paf.job_id = pj.job_id

  and pj.job_definition_id = pjd.job_definition_id

  and paf.position_id = hp.position_id

  and hp.position_definition_id = ppd.position_definition_id

order by hlevel