List of Approvers for a Purchase Order in Position Hierarchy

List of Approvers for a Purchase Order in Position Hierarchy

This script gets all the approvers and their details like approver name, position, approval group, amount limit, approval hierarchy path for a given position hierarchy.

Inputs for this script are:business_group_id for the desired POSITION HIERARCHY
pos_structure_version_id for the desired POSITION HIERARCHY
Top position in the desired POSITION HIERARCHY

SCRIPT TO GET POSITION_STRUCTURE_ID AND BUSINESS_GROUP_ID FOR A GIVEN HIERARCHY NAME

SELECT position_structure_id, business_group_id   FROM per_position_structures_v   WHERE NAME = '&POSITION HIERARCHY NAME'

SCRIPT TO GET ALL THE APPROVERS, THEIR POSITIONS, APPROVAL HIERARCHY PATH, APPROVAL LIMITS IN A POSITION HIERARCHY
This Script takes position_structure_id and business_group_id obtained from the above query and top position id for a given position hierarchy as inputs.

SELECT h.POSITION, h.PATH, ass.position_holder, al.doc_type,
 
al.approval_group, al.OBJECT, al.rule,al.amount_limit, al.low_value, al.high_value
 
FROM --Getting the Approval Limits 
 
(SELECT psc.position_id pos_id, pcf.control_function_name doc_type,
 
pcg.control_group_name approval_group, pcr.object_code OBJECT,
 
pcr.rule_type_code rule, amount_limit,
 
segment1_low
 
|| '-'
 
|| segment2_low
 
|| '-'
 
|| segment3_low
 
|| '-'
 
|| segment4_low
 
|| '-'
 
|| segment5_low low_value,
 
segment1_high
 
|| '-'
 
|| segment2_high
 
|| '-'
 
|| segment3_high
 
|| '-'
 
|| segment4_high
 
|| '-'
 
|| segment5_high high_value
 
FROM apps.po_position_controls_all psc,
 
apps.po_control_groups_all pcg,
 
apps.po_control_rules pcr,
 
apps.po_control_functions pcf
 
WHERE 1 = 1
 
AND psc.control_function_id = pcf.control_function_id
 
AND psc.org_id = 95
 
AND psc.control_group_id = pcg.control_group_id
 
AND pcg.control_group_id = pcr.control_group_id) al,
 
-- Getting approvers/users for a position in the heirarchy 
 
(SELECT he.full_name position_holder, pa.position_id pos_id
 
FROM apps.per_all_assignments_f pa, apps.hr_employees he
 
WHERE pa.business_group_id = 81
 
AND pa.effective_end_date = '31-DEC-4712'
 
AND pa.person_id = he.employee_id) ass,
 
-- Getting the Postion Heirarchy 
 
(SELECT pp.NAME POSITION, pse.parent_position_id position_id,
 
pp.NAME PATH
 
FROM per_pos_structure_elements_v pse, per_positions pp
 
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
 
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
 
AND pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY 
 
AND pse.parent_position_id = pp.position_id
 
UNION
 
SELECT DISTINCT has.NAME POSITION, has.position_id position_id,
 
(SELECT NAME
 
FROM per_positions
 
WHERE position_id = 98)
 
|| SYS_CONNECT_BY_PATH (has.NAME, '/') PATH
 
FROM (SELECT NAME, position_id
 
FROM apps.hr_all_positions_f_tl
 
WHERE LANGUAGE = USERENV ('LANG')) has,
 
per_pos_structure_elements pse
 
WHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
 
AND has.position_id = pse.subordinate_position_id
 
AND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
 
START WITH pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY 
 
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
 
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
 
AND PRIOR pse.business_group_id = pse.business_group_id
 
ORDER BY PATH) h
 
WHERE al.pos_id (+)= h.position_id 
 
AND ass.pos_id(+) = h.position_id

order by path