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
0 comments:
Post a Comment