abdeen-ahmad
2/3/2016 - 3:07 PM

Get direct manager query

Get direct manager query

SELECT pps.NAME pos_struc_name,
           ppsv.version_number pos_struc_ver_no,
           LEVEL parent_pos_level,
           ppse.subordinate_position_id child_pos_id,
           ppse.parent_position_id parent_pos_id,
           ppsv.pos_structure_version_id
      FROM per_position_structures pps,
           per_pos_structure_versions ppsv,
           per_pos_structure_elements ppse
     WHERE     pps.position_structure_id = 61
           AND pps.position_structure_id = ppsv.position_structure_id
           AND ppsv.pos_structure_version_id = ppse.pos_structure_version_id
           AND TRUNC (SYSDATE) BETWEEN ppsv.date_from
                                   AND NVL (ppsv.date_to, SYSDATE)
CONNECT BY     PRIOR ppse.parent_position_id = ppse.subordinate_position_id
           AND ppse.pos_structure_version_id = 61
START WITH     ppse.subordinate_position_id = :p_starting_subordinate_pos_id
           AND ppse.pos_structure_version_id = 61
  ORDER BY parent_pos_level ASC