Query to Get Organization Hierarchy

SELECT *

  FROM (SELECT haou.name,

               (    SELECT LISTAGG (

                              ' Level: ' || LEVEL || ' ' || posev.d_parent_name,

                              CHR (10))

                           WITHIN GROUP (ORDER BY LEVEL)

                      FROM per_organization_structures pos,

                           per_org_structure_versions posv,

                           per_org_structure_elements_v posev

                     WHERE     pos.business_group_id = haou.business_group_id

                           AND UPPER (pos.name) = UPPER (:l_hierarchy_name)

                           AND posv.organization_structure_id =

                                  pos.organization_structure_id

                           AND posv.org_structure_version_id =

                                  posev.org_structure_version_id

                           AND TRUNC (SYSDATE) BETWEEN date_from

                                                   AND NVL (

                                                          date_to,

                                                          hr_general.end_of_time)

                START WITH posev.organization_id_child = haou.organization_id

                CONNECT BY posev.organization_id_child =

                              PRIOR posev.organization_id_parent)

                  hierarchy

          FROM hr_all_organization_units haou

         WHERE business_group_id = :l_business_group_id)

 WHERE hierarchy IS NOT NULL