This discussion is archived
2 Replies Latest reply: Dec 7, 2012 6:58 AM by 892288 RSS

Query to retrieve 2 level of supervisors

Alejandro Tovar Lanz Expert
Currently Being Moderated
Hi all,

I am using the following query to retrieve the people who is being supervised for a given person_id... Is there any way to modify this query (below) in order to get two levels of employees? something like:

Supervisor 1
Employee 1
Employee 2
Employee 3
Supervisor 2
Employee 4
Employee 5
Employee 6

The query is working if I pass the person_id of Supervisor 1 for example I would get Employee 1 2 and 3. But if I pass a person id which of higher hierarchy I will just get Supervisor 1 Supervisor 2 ... And I need all the people (just two levels down)

select distinct * from(
select distinct ppf.first_name||' '||ppf.last_name
, ppf.last_name
, fu.user_name
, fu.user_id
, ppf.person_id
, papf.person_id supervisor_id
, pi.image_id
, initcap(hla.description) LOCATION
, pb.NAME pay_basis_name
, pax.grade_id
, haou.name
, employment_category
, ppos.date_start
from per_assignments_x pax
, per_grade_definitions pgd
, per_people_x ppf
, fnd_user fu
, per_all_people_f papf
, per_images pi
, hr_locations_all hla
, per_pay_bases pb
, hr_all_organization_units haou
, per_periods_of_service ppos
where ppf.person_id = pax.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and fu.person_party_id = papf.party_id
and pax.supervisor_id =papf.person_id
and pi.parent_id (+) = ppf.person_id
and pi.table_name(+) = 'PER_PEOPLE_F'
and pax.person_id = ppf.person_id
and hla.location_id (+)= pax.location_id
AND ppf.current_employee_flag = 'Y'
AND pb.pay_basis_id = pax.pay_basis_id
AND haou.organization_id = pax.organization_id
AND ppos.person_id = ppf.person_id
AND pax.grade_id = pgd.grade_definition_id
AND papf.person_id = :inPersonId
UNION ALL
select ppf.first_name||' '||ppf.last_name
, ppf.last_name
, fu.user_name
, fu.user_id
, ppf.person_id
, papf.person_id supervisor_id
, pi.image_id
, initcap(hla.description) LOCATION
, pb.NAME pay_basis_name
, pax.grade_id
, haou.name
, employment_category
, ppos.date_start
from per_people_x ppf
, per_grade_definitions pgd
, fnd_user fu
, per_all_people_f papf
, HR_WORKING_PERSON_LISTS HWPL
, per_images pi
, per_assignments_x pax
, hr_locations_all hla
, per_pay_bases pb
, hr_all_organization_units haou
, per_periods_of_service ppos
where
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and fu.person_party_id = papf.party_id
AND HWPL.owning_person_id = papf.person_id
and hwpl.selected_person_id = ppf.person_id
AND pi.parent_id (+) = ppf.person_id
and ppf.current_employee_flag = 'Y'
and pax.person_id = ppf.person_id
AND hla.location_id (+)= pax.location_id
AND pb.pay_basis_id = pax.pay_basis_id
AND haou.organization_id = pax.organization_id
AND ppos.person_id = ppf.person_id
AND pax.grade_id = pgd.grade_definition_id
AND papf.person_id = :inPersonId
)order by last_name
  • 1. Re: Query to retrieve 2 level of supervisors
    GiuseppeBonavita Journeyer
    Currently Being Moderated
    Hola Alejandro,


    you can play a little bit around with the one below you find more confortable with.

    /* multi level */
    select level
    ,assignment_number
    ,assignment_id
    ,supervisor_id
    ,(select full_name from per_people_x where person_id = pax.person_id)
    ,sys_connect_by_path( (select employee_number from per_people_x where person_id = pax.person_id), ' --> ')
    from per_assignments_x pax
    where primary_flag = 'Y'
    connect by prior person_id = supervisor_id
    start with person_id = 1523

    /* level 2 only */
    select (select full_name from per_people_x where person_id = pax1.person_id)
    ,(select full_name from per_people_x where person_id = pax2.person_id)
    from per_assignments_x pax1
    ,per_assignments_x pax2
    where pax1.supervisor_id = 1523
    and pax1.primary_flag = 'Y'
    and pax1.person_id = pax2.supervisor_id
    and pax2.primary_flag = 'Y'
  • 2. Re: Query to retrieve 2 level of supervisors
    892288 Explorer
    Currently Being Moderated
    1st query is looking good and simple one ..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points