Query to get Employee and Supervisor hierarchy Details in Oracle Apps HRMS R12

-->--------------------------------------------------------------------

SELECT lpad('->',8*(level-1)) ||

( select distinct full_name from per_all_people_f

where person_id = paf.person_id

and sysdate between effective_start_date and effective_end_date ) TREE

FROM per_all_assignments_f paf

START WITH paf.person_id = <PUT_IN_PERSON_ID>

AND paf.primary_flag = 'Y'

AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date

CONNECT BY paf.person_id = PRIOR paf.supervisor_id

AND paf.primary_flag = 'Y'

AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

-->--------------------------------------------------------------------

-- Find Employee- Supervisor Hierarchy in Oracle Apps

  SELECT papf.employee_number,           papf.person_id,

           papf.full_name,

           ppg.segment1 officer_flag, --executive flag for officers (Y is officer, N is not)

           NVL (spapf.full_name, 'NONE') supervisor_name,

           spapf.person_id supv_person_id,

           LEVEL

      FROM per_people_x papf,

           PER_PEOPLE_X SPAPF,

           per_assignments_x paaf,

           pay_people_groups ppg

     WHERE     papf.person_id = paaf.person_id

           AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

           AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date

           and sysdate between SPAPF.EFFECTIVE_START_DATE and SPAPF.EFFECTIVE_END_DATE

           --AND xxx_person_type.is_employee(:p_eff_date, papf.person_id) = 'TRUE'

           AND paaf.assignment_type IN ('E', 'C')

           AND paaf.supervisor_id = spapf.person_id

           AND paaf.people_group_id = ppg.people_group_id

           AND paaf.primary_flag = 'Y'

START WITH papf.employee_number = :p_emp_num --emp num of employee or top level supervisor?

CONNECT BY PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level

  ORDER BY LEVEL DESC

;

-->--------------------------------------------------------------------

    SELECT LEVEL seq,

           e.person_id,

           e.grade_id,

           e.job_id,

           e.supervisor_id,

           e.employee_number,

           e.full_name

      FROM (SELECT DISTINCT paf.person_id,

                            paf.grade_id,

                            paf.job_id,

                            paf.supervisor_id,

                            ppf.employee_number,

                            ppf.full_name

              FROM    per_all_people_f ppf

                   LEFT JOIN

                      per_all_assignments_f paf

                   ON     ppf.person_id = paf.person_id

                      AND paf.person_id IS NOT NULL

                      AND (SYSDATE BETWEEN ppf.effective_start_date

                                       AND ppf.effective_end_date)

                      AND (SYSDATE BETWEEN paf.effective_start_date

                                       AND paf.effective_end_date)) e

CONNECT BY person_id = PRIOR supervisor_id

START WITH employee_number = :p_employee_number         --person_id = :P_PERSON_ID

  ORDER BY LEVEL

;

-->--------------------------------------------------------------------

SELECT 

LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,ose.organization_id_parent, 

org.organization_id 

FROM 

hr_all_organization_units org, 

per_org_structure_elements ose 

WHERE 1=1 

AND org.organization_id = ose.organization_id_child 

AND ose.org_structure_version_id = 61 

--and org.organization_id = 340 

START WITH 

ose.organization_id_parent = 81 

CONNECT BY PRIOR 

ose.organization_id_child = ose.organization_id_parent 

ORDER SIBLINGS BY 

org.location_id, 

ose.organization_id_child  

 

-->--------------------------------------------------------------------

SELECT haou.name, papf.EMPLOYEE_NUMBER, papf.full_name 

FROM  

per_org_structure_elements ose 

,hr_all_organization_units haou 

,per_all_assignments_f paaf 

,per_all_people_f papf 

WHERE ose.ORGANIZATION_ID_PARENT = 81 

AND ose.ORG_STRUCTURE_VERSION_ID = 61 

AND haou.organization_id = ose.organization_id_child 

--and paaf.organization_id = ose.organization_id_child 

and papf.person_id = paaf.person_id 

and paaf.organization_id = haou.organization_id 

and :P_GIVEN_DATE between papf.effective_start_date and papf.effective_end_date 

and :P_GIVEN_DATE between paaf.effective_start_date and paaf.effective_end_date

-->--------------------------------------------------------------------

select (select full_name from per_all_people_f where person_id=a1.person_id and effective_end_date > syadate) Employee_name,

 (select full_name from per_all_people_f where person_id=a1.supervisor_id and effective_end_date > syadate) Supervisor_name,

PATH,

LEVEL

from

(

SELECT paf.person_id, paf.supervisor_id

 , LPAD (' ', 2 * LEVEL - 1)

 || SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH

 , LEVEL

 FROM per_all_assignments_f paf

 START WITH paf.person_id = <:person_id>

 AND paf.primary_flag = 'Y'

 AND paf.assignment_type = 'E'

 AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date

 AND assignment_status_type_id = 1

 CONNECT BY PRIOR paf.person_id = paf.supervisor_id

 AND paf.primary_flag = 'Y'

 AND paf.assignment_type = 'E'

 AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date

 AND assignment_status_type_id = 1) a1

order by LEVEL

-->--------------------------------------------------------------------

SELECT LEVEL seq,

  e.person_id,

  e.grade_id,

  e.job_id,

  e.supervisor_id,

  e.employee_number,

  e.full_name

FROM

  (SELECT DISTINCT paf.person_id,

    paf.grade_id,

    paf.job_id,

    paf.supervisor_id,

    ppf.employee_number,

    ppf.full_name

  FROM per_all_people_f ppf

  LEFT JOIN per_all_assignments_f paf

  ON ppf.person_id   = paf.person_id

  AND paf.person_id IS NOT NULL

  AND (SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date )

  AND (SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date )

  ) e

  CONNECT BY person_id = PRIOR supervisor_id

  START WITH person_id = :P_PERSON_ID

ORDER BY LEVEL

-->--------------------------------------------------------------------