Query to get Employee Hierarchy.

Query to get Employee Hierarchy

SELECT  fu.user_name, emp.heirachy,emp.path1 ,he.employee_num

  FROM (    SELECT he.full_name,

                   he.employee_num , 

                   level path1,        

                   SYS_CONNECT_BY_PATH (he.full_name, '|') heirachy,

                   SUBSTR (

          SYS_CONNECT_BY_PATH (he.full_name, '|'),

          2,

          DECODE (INSTR (SYS_CONNECT_BY_PATH (he.full_name, '|'), '|', 2),

                  0, 240,

                  INSTR (SYS_CONNECT_BY_PATH (he.full_name, '|'), '|', 2))-2) emp_name                 

              FROM apps.hr_employees he,                  

                   apps.per_assignments_v7 pav                

             WHERE     he.employee_id = pav.person_id

                   AND SYSDATE BETWEEN pav.effective_start_date

                                   AND NVL (pav.effective_end_date, SYSDATE + 1)                                 

        CONNECT BY NOCYCLE PRIOR pav.supervisor_id = he.employee_id) emp,

        apps.hr_employees he ,

        fnd_user fu     

 WHERE 1=1

     AND (emp.full_name = :Emp_name)

    AND he.full_name= emp.emp_name

    AND HE.EMPLOYEE_NUM = fu.user_name(+)

order by emp.path1