SQL to get Manager Hierarchy

Summary:

SQL to get Manager Hierarchy

Content (required):

SQL to get Manager Hierarchy

Version (include the version you are using, if applicable):

23C

Code Snippet (add any code snippets that support your topic, if applicable):

SELECT 

DISTINCT

papf_sup.person_number as "Line_Manager_Person_Number",

ppnf_sup.first_name as "Line_Manager_First_Name",

ppnf_sup.Last_name as "Line_Manager_Last_Name",

pmhd.manager_level as "Line_Manager_Level",

(SELECT PU.USERNAME FROM PER_USERS PU 

WHERE PU.PERSON_ID = papf_sup.PERSON_ID

AND PU.ACTIVE_FLAG = 'Y') AS "Line_Manager_Username",

decode(pmhd.manager_level, '1', 'Direct Reportee', 'Indirect Reportee') Direct_Indirect,