Position Hierarchy

begin
mo_global.set_policy_context('S',81);
end;


SELECT ppse.ps_name Hierarchy_Name,pcf.CONTROL_FUNCTION_NAME Document_Type, pp.name Position_Name,papf.full_name,pcg.CONTROL_GROUP_NAME Approval_Group,ppse.pst_name Reporting_To,
 pcr.amount_limit Approver_Limit
FROM po_control_rules pcr,
po_position_controls_all ppca,
po_control_functions pcf,
per_all_assignments_f paaf,
per_all_people_f papf,
per_positions PP,
per_pos_structure_elements_v2 ppse,
po_control_groups pcg
WHERE pcr.control_group_id = ppca.control_group_id
AND pcg.control_group_id = pcr.CONTROL_GROUP_ID
AND ppca.control_function_id = pcf.control_function_id
AND ppca.position_id = paaf.position_id
AND paaf.effective_end_date >= SYSDATE
AND papf.effective_end_date >= SYSDATE
AND paaf.person_id = papf.person_id
AND PP.POSITION_ID = paaf.position_id
AND object_code = 'DOCUMENT_TOTAL'
AND ppse.SUBORDINATE_POSITION_ID = pp.POSITION_ID
ORDER BY ppse.ps_name,pcr.control_group_id, pcr.control_rule_id