Have any question?
+2 0100 62 62 837
[email protected]
Register Login

Login with your site account

Lost your password?

Not a member yet? Register now

AppsLead | Learn Oracle From The LeadersAppsLead | Learn Oracle From The Leaders
  • Home
  • Courses
    • About Courses

      • Free Training
        • Oracle ebusiness suite Free
        • Oracle Fusion Free
      • Online Live Training
        • Oracle ebusiness suite
        • Oracle Fusion
        • Oracle Database
        • Java & Middleware
      • All Training Courses
      • Training Schedule
      • Register For Live Course
      • Join Our Team
      • Exam Request
  • Blog
    • Oracle ebusiness Suite
      • Oracle Financial
      • Oracle HCM
      • Oracle SCM
    • Oracle Fusion
      • Fusion Financial
      • Fusion HCM
    • Our Authors
    • Be an Author
  • Schedule
  • FAQs
  • About Us
    • About Us
    • Our Services
    • Our Authors
  • Contact Us
  • Home
  • Courses
    • About Courses

      • Free Training
        • Oracle ebusiness suite Free
        • Oracle Fusion Free
      • Online Live Training
        • Oracle ebusiness suite
        • Oracle Fusion
        • Oracle Database
        • Java & Middleware
      • All Training Courses
      • Training Schedule
      • Register For Live Course
      • Join Our Team
      • Exam Request
  • Blog
    • Oracle ebusiness Suite
      • Oracle Financial
      • Oracle HCM
      • Oracle SCM
    • Oracle Fusion
      • Fusion Financial
      • Fusion HCM
    • Our Authors
    • Be an Author
  • Schedule
  • FAQs
  • About Us
    • About Us
    • Our Services
    • Our Authors
  • Contact Us

Blog

  • Home
  • Blog
  • Get all Approvers for a Purchase Order in Position Hierarchy

Get all Approvers for a Purchase Order in Position Hierarchy

  • Posted by Sameh Bakkar
  • Categories Blog
  • Date February 1, 2013
  • Comments 0 comment
Hi, Many times we need a script to get all the approvers and their details like approver name, position, approval group, amount limit and so on. so today i will deliver this script. our 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. now we will use script to get our needed inputs first:
<pre>SELECT position_structure_id, business_group_id
 
FROM per_position_structures_v
 
WHERE NAME = '&POSITION HIERARCHY NAME'

[/pjp]
Now we are ready to use our script which takes position_structure_id and business_group_id obtained from the above query and top position id 
for a given position hierarchy as inputs:

</pre>
<pre>
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 

Tag:Approvers for a Purchase Order

  • Share:
Admin bar avatar
Sameh Bakkar
Sameh Bakkar has over 15 years Oracle Applications implementation experience (EBS/Fusion) and he is an Oracle authorized trainer. He has in-depth technical and Functional knowledge of Oracle Applications and Oracle HCM Cloud. Sameh has excellent experience in Team management, Man Power and resource planning, and Pre-Sales management Process. Additionally he has also given Best Practices seminars on Oracle e-business suite and Oracle Fusion.

Previous post

How to list all responsibilities for a user
February 1, 2013

Next post

How To Use API User Hooks
12 March, 2013

You may also like

  • Entering Request for Quotations (RFQ)
    19 June, 2012
  • HRMS Assignment Migration – API
    9 March, 2011

Leave A Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Categories

  • Blog
  • Fusion HCM
  • Oracle ebusiness Suite
  • Oracle Financial
  • Oracle Fusion
  • Oracle HCM
  • Oracle SCM

Latest Courses

R12.x Oracle E-Business Suite Essentials for Implementers

R12.x Oracle E-Business Suite Essentials for Implementers

Free
R12.x Oracle HRMS Work Structures Fundamentals

R12.x Oracle HRMS Work Structures Fundamentals

Free
R12.x Oracle Payroll Fundamentals

R12.x Oracle Payroll Fundamentals

Free
AppsLead | Learn Oracle From The Leaders

+2 0100 62 62 837

[email protected]

Company

  • About Us
  • Blog
  • Contact Us

Links

  • Events

Copyright © 2017 AppsLead, All rights reserved. Developed by ECDSCS Co.

  • Privacy
  • Terms
  • Sitemap