Oracle R12 – Query para recuperar hierarquia de aprovadores do PO

SELECT distinct

       hph.position_hierarchy_name hierarquia

      ,hph.parent_position_id pai

      ,hph.child_position_id filho

      ,hph.position_hierarchy_level nivel

      ,hph.child_position_name aprovadores

      ,pps.attribute2 de

      ,pps.attribute3 ate

      ,hr_general.decode_position_latest_name(hph.parent_position_id)

  FROM per_position_structures pps

      ,per_pos_structure_elements ppe

      ,hrfv_position_hierarchies hph

      ,po_employee_hierarchies_all peh

      ,(

      SELECT pap.position_id

              ,ppf.business_group_id

              ,ppf.full_name

              ,pap.position_definition_id

          FROM per_all_people_f      ppf

              ,per_all_assignments_f paa

              ,per_all_positions     pap

         WHERE ppf.person_id = paa.person_id

           AND ppf.business_group_id = paa.business_group_id

           AND paa.position_id = pap.position_id

           AND pap.business_group_id = paa.business_group_id

           ) sub_a

 WHERE pps.position_structure_id = ppe.pos_structure_version_id

   AND pps.business_group_id = ppe.business_group_id

   AND ppe.pos_structure_version_id = hph.position_hierarchy_id

   AND ppe.business_group_id = hph.business_group_id

   AND ppe.business_group_id = pps.business_group_id

   AND ppe.pos_structure_version_id = pps.position_structure_id

   –AND hph.position_hierarchy_name = ’00-11-050-> 10000_< 300000′

   AND peh.business_group_id = ppe.business_group_id

   AND peh.position_structure_id = ppe.pos_structure_version_id

   AND peh.business_group_id = hph.business_group_id

   AND peh.position_structure_id = hph.position_hierarchy_id

   AND ppe.parent_position_id = sub_a.position_id

   AND ppe.business_group_id = sub_a.business_group_id

   AND hph.business_group_id = sub_a.business_group_id

   AND peh.employee_id = sub_a.position_id

   AND peh.superior_id = sub_a.position_id

 

Deixe uma resposta

Pular para a barra de ferramentas