2 Replies Latest reply: Dec 7, 2012 8:58 AM by 892288 RSS

    Query to retrieve 2 level of supervisors

    Alejandro Tovar Lanz
      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
          Giuseppe Bonavita
          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
            1st query is looking good and simple one ..