HR: Query to Get Organizational Hierarchy of an Organization

This query lists the Organization structure from the below navigation : 

Global HRMS Manager -> Work Structures -> Hierarchy(Or Global Hierarchy)

 

SELECT *

  FROM (SELECT (lpad(' ', (lvl - 1) * 5, ' ') || haou_c.name) org_name,

               apps.hr_general.decode_lookup('ORG_TYPE', haou_c.type) TYPE,

               org_hier.*

          FROM hr_all_organization_units haou_c,

               (SELECT hier.organization_id_parent,

                       hier.organization_id_child,

                       LEVEL lvl,

                       sys_connect_by_path(hier.organization_id_child, '/') org_level,

                       sys_connect_by_path(LEVEL, '/') hier_level

                  FROM per_org_structure_elements hier,

                       (SELECT vers.org_structure_version_id

                          FROM per_org_structure_versions_v  vers,

                               per_organization_structures_v struct

                         WHERE struct.business_group_id = 81

                           AND struct.name = 'your Hierarchy Structure name'

                           AND vers.organization_structure_id =

                               struct.organization_structure_id

                           AND struct.business_group_id =

                               vers.business_group_id

                           AND SYSDATE BETWEEN vers.date_from AND

                               nvl(vers.date_to, SYSDATE)

                           AND rownum = 1) orgstr

                 WHERE hier.org_structure_version_id =

                       orgstr.org_structure_version_id

                   AND hier.business_group_id = 81

                 START WITH hier.organization_id_parent =

                            (SELECT a.organization_id

                               FROM hr_all_organization_units a

                              WHERE a.organization_id = 81)

                        AND hier.org_structure_version_id =

                            orgstr.org_structure_version_id

                        AND hier.business_group_id = 81

                CONNECT BY PRIOR

                            organization_id_child = organization_id_parent) org_hier

         WHERE haou_c.organization_id = org_hier.organization_id_child

         ORDER BY org_hier.org_level);