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