3 Replies Latest reply: Jan 31, 2013 2:31 AM by 689701 RSS

    Query to find supervisor_id hierarchy for a person

    801619
      Hi,

      I have a requirement, where i have to find the supervisor id hierarchy (bottom up) for a person. I am using below query to get it.

      SELECT LEVEL ,LPAD ( ' ', 7 (LEVEL-1)) || paaf.person_id AS person_id*
      FROM   per_all_assignments_f paaf
      WHERE sysdate between paaf.effective_start_date and paaf.effective_end_date
      START WITH paaf.person_id=2338
      CONNECT BY PRIOR paaf.supervisor_id  =  paaf.person_id

      Above query is returning the follwing results as i expected.

      1 2338

      2 2212

      3 2105

      Here, for 2338 supervisors is------> 2212 supervisor is -----------> 2105


      Now, my requirement is, for each person i have to find the direct reporteee list in separate column.

      for 2338, need to find direct reportees--------> for 2212, find direct reportees--------> for 2105, find direct reportees in separte column

      Note: I am developing a reporting to display the results like above.

      Any help is appreciated.

      Thanks in advance.
        • 1. Re: Query to find supervisor_id hierarchy for a person
          770460
          try this query :-
          SELECT LEVEL ,LPAD ( ' ', 7 (LEVEL-1)) || paaf.person_id AS person_id*
          FROM per_all_assignments_f paaf
          WHERE sysdate between paaf.effective_start_date and paaf.effective_end_date
          START WITH paaf.person_id=2338
          CONNECT BY paaf.supervisor_id = PRIOR paaf.person_id

          Is this what you are looking for ?
          • 2. Re: Query to find supervisor_id hierarchy for a person
            801619
            Hi,

            I finally got it. I used below query to get it.

            SELECT t1.supervisor_id, t2.person_id AS reportee_id
            FROM
            (SELECT LEVEL ,LPAD ( ' ', 7 * (LEVEL-1)) || paaf.person_id AS supervisor_id*
            FROM per_all_assignments_f paaf
            WHERE sysdate between paaf.effective_start_date and paaf.effective_end_date
            START WITH paaf.person_id=2338
            CONNECT BY PRIOR paaf.supervisor_id = paaf.person_id) t1,
            (SELECT paaf1.person_id, paaf1.supervisor_id FROM per_all_assignments_f paaf1) t2
            WHERE t1.supervisor_id=t2.supervisor_id
            ORDER BY t2.person_id,t2.supervisor_id


            Thanks.
            • 3. Re: Query to find supervisor_id hierarchy for a person
              689701
              Please use the below query it may help you get the employee & supervisor

              SELECT ROWNUM
              , pax.supervisor_id
              , pax.person_id
              , ppx.employee_number
              , mgx.employee_number supervisor_staff_number
              , REPLACE (ppx.full_name, '''', ' ') full_name
              , REPLACE (mgx.full_name, '''', ' ') supervisor_full_name
              , pj.NAME job_name
              , LEVEL
              FROM per_assignments_x pax
              , per_people_x ppx
              , per_people_x mgx
              , per_jobs pj
              WHERE ppx.person_id = pax.person_id
              AND ppx.current_employee_flag = 'Y'
              AND mgx.person_id = pax.supervisor_id
              AND pj.job_id = pax.job_id
              START WITH ppx.employee_number = :p_emp_num
              CONNECT BY NOCYCLE PRIOR mgx.employee_number = ppx.employee_number
              --ORDER BY   1 ASC;

              Sandeep Yaparla