Home » Applications » Oracle Fusion Apps & E-Business Suite » Approval group's members list (11.1.3)
Approval group's members list [message #571794] Fri, 30 November 2012 09:50 Go to next message
vids
Messages: 1
Registered: November 2012
Junior Member
All,

Im very new to Oracle so please kindly bear with me if this is a irrevalant forum for my post.

I need to know how to get the list of members for a PO approval group.

Many thanks,
Vidhya
Re: Approval group's members list [message #572144 is a reply to message #571794] Thu, 06 December 2012 08:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
I don't have much idea.
Please check the link.

By
Vamsi
Re: Approval group's members list [message #572693 is a reply to message #572144] Sun, 16 December 2012 05:32 Go to previous messageGo to next message
gopigt
Messages: 7
Registered: December 2012
Location: Bangalore
Junior Member

Find below the script to get the Approval Group Members based on Position Hierarchy.
SELECT b.pos_structure_version_id, 
       b.date_from, 
       (SELECT name 
        FROM   hr.hr_all_positions_f 
        WHERE  1 = 1 
               AND position_id = c.parent_position_id 
               AND Trunc(SYSDATE) BETWEEN effective_start_date AND 
                                          effective_end_date) 
       parent_position_name, 
       (SELECT papf.full_name 
        FROM   per_all_people_f papf, 
               per_all_assignments_f paaf 
        WHERE  1 = 1 
               AND papf.person_id = paaf.person_id 
               AND Trunc(SYSDATE) BETWEEN paaf.effective_start_date AND 
                                          paaf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND paaf.primary_flag = 'Y' 
               AND paaf.position_id = c.parent_position_id) 
       parent_position_holder, 
       (SELECT name 
        FROM   hr.hr_all_positions_f 
        WHERE  1 = 1 
               AND position_id = c.subordinate_position_id 
               AND Trunc(SYSDATE) BETWEEN effective_start_date AND 
                                          effective_end_date) 
       subordinate_position_name, 
       (SELECT papf.full_name 
        FROM   per_all_people_f papf, 
               per_all_assignments_f paaf 
        WHERE  1 = 1 
               AND papf.person_id = paaf.person_id 
               AND Trunc(SYSDATE) BETWEEN paaf.effective_start_date AND 
                                          paaf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND paaf.primary_flag = 'Y' 
               AND paaf.position_id = c.subordinate_position_id) 
       parent_position_holder 
FROM   per_position_structures a, 
       per_pos_structure_versions b, 
       per_pos_structure_elements c 
WHERE  1 = 1 
       AND a.name = '&position_hiersrchy_name' 
       /* Pass the Position hierarch name*/ 
       AND a.position_structure_id = b.position_structure_id 
       AND b.pos_structure_version_id = c.pos_structure_version_id 
ORDER  BY 1, 
          5, 
          6 


Thanks
Gopi GT

[EDITED by LF: removed useless color formatting; formatted code and applied [code] tags]

[Updated on: Sun, 16 December 2012 15:06] by Moderator

Report message to a moderator

Re: Approval group's members list [message #572709 is a reply to message #572693] Sun, 16 December 2012 07:47 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: po stuck inprocess status
Next Topic: Assignment Package
Goto Forum:
  


Current Time: Thu Mar 28 09:24:52 CDT 2024