Search This Blog

Showing posts with label Oracle Organization HRMS Hierarchy Query. Show all posts
Showing posts with label Oracle Organization HRMS Hierarchy Query. Show all posts

Tuesday, December 27, 2011

Oracle Organization Hierarchy Query

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