SQL Query To Get Oracle HR Organization Hierarchy

SQL Query To Get Oracle HR Organization Hierarchy

 

Following SQL Query can be used  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;