List of Approvers for a Purchase Order in Position Hierarchy

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