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