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.<\/p>\n
Inputs for this script are: business_group_id for the desired POSITION HIERARCHY
\npos_structure_version_id for the desired POSITION HIERARCHY
\nTop position in the desired POSITION HIERARCHY<\/p>\n
SCRIPT TO GET POSITION_STRUCTURE_ID AND BUSINESS_GROUP_ID FOR A GIVEN HIERARCHY NAME SCRIPT TO GET ALL THE APPROVERS, THEIR POSITIONS, APPROVAL HIERARCHY PATH, APPROVAL LIMITS IN A POSITION HIERARCHY<\/p>\n 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.<\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-1832","post","type-post","status-publish","format-standard","hentry","category-scripts"],"yoast_head":"\n
\nSELECT position_structure_id, business_group_id
\nFROM per_position_structures_v
\nWHERE NAME = '&POSITION HIERARCHY NAME'<\/code><\/p>\nSELECT h.POSITION, h.PATH, ass.position_holder, al.doc_type,
\nal.approval_group, al.OBJECT, al.rule,al.amount_limit, al.low_value, al.high_value
\nFROM --Getting the Approval Limits
\n(SELECT psc.position_id pos_id, pcf.control_function_name doc_type,
\npcg.control_group_name approval_group, pcr.object_code OBJECT,
\npcr.rule_type_code rule, amount_limit,
\nsegment1_low
\n|| '-'
\n|| segment2_low
\n|| '-'
\n|| segment3_low
\n|| '-'
\n|| segment4_low
\n|| '-'
\n|| segment5_low low_value,
\nsegment1_high
\n|| '-'
\n|| segment2_high
\n|| '-'
\n|| segment3_high
\n|| '-'
\n|| segment4_high
\n|| '-'
\n|| segment5_high high_value
\nFROM apps.po_position_controls_all psc,
\napps.po_control_groups_all pcg,
\napps.po_control_rules pcr,
\napps.po_control_functions pcf
\nWHERE 1 = 1
\nAND psc.control_function_id = pcf.control_function_id
\nAND psc.org_id = 95
\nAND psc.control_group_id = pcg.control_group_id
\nAND pcg.control_group_id = pcr.control_group_id) al,
\n-- Getting approvers\/users for a position in the heirarchy
\n(SELECT he.full_name position_holder, pa.position_id pos_id
\nFROM apps.per_all_assignments_f pa, apps.hr_employees he
\nWHERE pa.business_group_id = 81
\nAND pa.effective_end_date = '31-DEC-4712'
\nAND pa.person_id = he.employee_id) ass,
\n-- Getting the Postion Heirarchy
\n(SELECT pp.NAME POSITION, pse.parent_position_id position_id,
\npp.NAME PATH
\nFROM per_pos_structure_elements_v pse, per_positions pp
\nWHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
\nAND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
\nAND pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
\nAND pse.parent_position_id = pp.position_id
\nUNION
\nSELECT DISTINCT has.NAME POSITION, has.position_id position_id,
\n(SELECT NAME
\nFROM per_positions
\nWHERE position_id = 98)
\n|| SYS_CONNECT_BY_PATH (has.NAME, '\/') PATH
\nFROM (SELECT NAME, position_id
\nFROM apps.hr_all_positions_f_tl
\nWHERE LANGUAGE = USERENV ('LANG')) has,
\nper_pos_structure_elements pse
\nWHERE pse.business_group_id = 81 --business_group_id for SOLO CUP POSITION HIERARCHY
\nAND has.position_id = pse.subordinate_position_id
\nAND pse.pos_structure_version_id = 61 --pos_structure_version_id for SOLO CUP POSITION HIERARCHY
\nSTART WITH pse.parent_position_id = 98 --Top position in SOLO CUP POSITION HIERARCHY
\nCONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
\nAND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
\nAND PRIOR pse.business_group_id = pse.business_group_id
\nORDER BY PATH) h
\nWHERE al.pos_id (+)= h.position_id
\nAND ass.pos_id(+) = h.position_id
\norder by path<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"