PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script

PO Approval and Hierarchy Details Script



PO Hierarchy Details Script

SELECT *
  FROM (SELECT POS_STRUCTURE_VERSION_ID,
               b.name structure_name,
               PARENT_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = PARENT_POSITION_ID)
                  PARENT_POSITION_NAME,
               SUBORDINATE_POSITION_ID,
               (SELECT name
                  FROM hr_all_positions_f
                 WHERE position_id = SUBORDINATE_POSITION_ID)
                  SUBORDINATE_POSITION_NAME
          --(select full_name from per_all_people_f where person_id in(select person_id from per_all_assignments_f where position_id=SUBORDINATE_POSITION_ID))
          FROM per_pos_structure_elements_v a, PER_POSITION_STRUCTURES b
         WHERE     1 = 1
               AND a.POS_STRUCTURE_VERSION_ID = b.POSITION_STRUCTURE_ID
               AND POS_STRUCTURE_VERSION_ID <> 0)
 WHERE STRUCTURE_NAME = 'XX_STRUTURE_NAME'          




PO Approval Group Details Script

SELECT decode(ppca.org_id,83,'XXORG_NAME',181,'XXORG_NAME') Org_Name,
--       ppca.position_id,
       (select name from per_positions where position_id=ppca.position_id)Position_Name,
--       ppca.job_id,
       (select name from per_jobs where job_id=ppca.job_id) Job_Name,
--       ppca.control_group_id,
--       ppca.control_function_id,
       control_function_name Document_Type,
       control_group_name Approval_Group
  FROM po_position_controls_all ppca,
       po_control_groups_all pcga,
       po_control_functions pcf
 WHERE     1 = 1
       AND ppca.control_group_id = pcga.control_group_id
       AND ppca.control_function_id = pcf.control_function_id;