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
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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 |