Oracle HRMS Hierarchy Query (upward, down wards)

 Upward:

SELECT ORGANIZATION_ID_PARENT

  FROM (    SELECT LEVEL LEVEL_ID, ORGANIZATION_ID_PARENT

              FROM per_org_structure_elements org

             WHERE ORGANIZATION_ID_PARENT <> 81

        START WITH ORGANIZATION_ID_CHILD =  :p_org_id

        CONNECT BY PRIOR ORGANIZATION_ID_PARENT = ORGANIZATION_ID_CHILD)

 WHERE LEVEL_ID =

       (SELECT MAX (LEVEL_ID)

          FROM (    SELECT LEVEL LEVEL_ID, ORGANIZATION_ID_PARENT

                      FROM per_org_structure_elements org

                     WHERE ORGANIZATION_ID_PARENT <> 81

                START WITH ORGANIZATION_ID_CHILD = :p_org_id

                CONNECT BY PRIOR ORGANIZATION_ID_PARENT =

                           ORGANIZATION_ID_CHILD))

down wards:

           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 org.organization_id = pose.organization_id_child

       --and org.name like 'org name'

       START WITH pose.organization_id_parent = :p_org_id-- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displayed

       CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent

ORDER SIBLINGS BY org.location_id, pose.organization_id_child