Query de jerarquia de puestos

select ps1.NAME,

       ps1.position_structure_id po_id,

       psv1.pos_structure_version_id po_vs_id,

        hapf.name aprobador_4,

       level3.name aprobador_3,

      level2.name aprobador_2,

       level1.name aprobador_1,

       pse1.name subordinado

  from PER_POSITION_STRUCTURES_V    ps1

   inner join PER_POS_STRUCTURE_VERSIONS_V psv1

   on  PS1.POSITION_STRUCTURE_ID = PSV1.POSITION_STRUCTURE_ID

   inner join   PER_POS_STRUCTURE_ELEMENTS_V pse1

   on pse1.POS_STRUCTURE_VERSION_ID = psv1.pos_structure_version_id

   left outer join   PER_POS_STRUCTURE_ELEMENTS_V level1

   on (level1.POS_STRUCTURE_VERSION_ID = psv1.pos_structure_version_id

   and level1.subordinate_position_id = pse1.PARENT_POSITION_ID)

   left outer join   PER_POS_STRUCTURE_ELEMENTS_V level2

   on (level2.POS_STRUCTURE_VERSION_ID = psv1.pos_structure_version_id

   and level2.subordinate_position_id = level1.PARENT_POSITION_ID)

   left outer join   PER_POS_STRUCTURE_ELEMENTS_V level3

   on (level3.POS_STRUCTURE_VERSION_ID = psv1.pos_structure_version_id

   and level3.subordinate_position_id =level2.PARENT_POSITION_ID)

   left outer join    apps.HR_ALL_POSITIONS_F      HAPF

   on  HAPF.position_id = nvl(level3.PARENT_POSITION_ID,                 nvl(level2.PARENT_POSITION_ID,nvl(level1.PARENT_POSITION_ID,

pse1.PARENT_POSITION_ID)))

    where    pse1.SUBORDINATE_POSITION_ID not in

       (select pse2.PARENT_POSITION_ID

          from PER_POSITION_STRUCTURES_V    ps2,

               PER_POS_STRUCTURE_VERSIONS_V psv2,

               PER_POS_STRUCTURE_ELEMENTS_V pse2

         where PS2.POSITION_STRUCTURE_ID = PSV2.POSITION_STRUCTURE_ID

           and pse2.POS_STRUCTURE_VERSION_ID = psv2.pos_structure_version_id

           and ps2.name = ps1.name)

   and ps1.name = '&name'---agregar jerarquia