SQL Query to get Oracle HR Organization Hierarchy

SQL Query to get Oracle HR Organization Hierarchy

 

 

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name  like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115   -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child