How To Query Approval Group and Approval Assignment Details For iProcurement And Purchasing Users

How can Approval Group and Approval Assignment details be queried from the database for iProcurement and Purchasing Users?

Solution:-
 
Use the Job Based query below when Employee Supervisor job based approvals are used, or Use the Position
Based query below when Position Hierarchy position based approvals are used.  The query results will show the approval list / approver list based on the job / position of the employees.

Note1:  Enter the operating unit org_id value, and specify either the User Names or the Employee IDs (comma separated). 
Note2:  The query is currently setup for Approve Purchase Requisitions; change the
pcf.CONTROL_FUNCTION_NAME value to run the query for other approval types;

Job Based :-
 
- Shows the Approval Group / Assignment rule details for specified approvers based on their JOB in HR 

 /* Formatted on 7/4/2014 11:42:44 AM (QP5 v5.115.810.9015) */
SELECT fndu.user_name,
       papf.full_name,
       paaf.person_id,
       pcr.control_group_id,
       pcr.control_rule_id,
       pcr.last_update_date,
       pcr.rule_type_code,
       pcr.object_code,
       pcr.amount_limit,
       pcr.segment1_low low1,
       pcr.segment2_low low2,
       pcr.segment3_low low3,
       pcr.segment4_low low4,
       pcr.segment5_low low5,
       pcr.segment6_low low6,
       pcr.segment7_low low7,
       pcr.segment8_low low8,
       pcr.segment1_high high1,
       pcr.segment2_high high2,
       pcr.segment3_high high3,
       pcr.segment4_high high4,
       pcr.segment5_high high5,
       pcr.segment6_high high6,
       pcr.segment7_high high7,
       pcr.segment8_high high8
FROM po_control_rules pcr,
     po_position_controls_all ppca,
     po_control_functions pcf,
     per_all_assignments_f paaf,
     per_all_people_f papf,
     fnd_user fndu
WHERE     pcr.control_group_id = ppca.control_group_id
      AND ppca.org_id = &myorgid
      AND ppca.control_function_id = pcf.control_function_id
      AND pcf.control_function_name = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
      AND ppca.job_id = paaf.job_id --- THIS MAKES THE QUERY FOR JOB BASED APPROVALS
      AND paaf.effective_end_date >= SYSDATE
      AND papf.effective_end_date >= SYSDATE
      AND paaf.person_id = papf.person_id
      AND CONCAT (CONCAT (paaf.person_id, '-'),
                  NVL (paaf.object_version_number, 0)
         ) IN
               (SELECT CONCAT (CONCAT (person_id, '-'),
                               NVL (MAX (object_version_number), 0)
                       )
                FROM per_all_assignments_f
                WHERE person_id IN
                            (SELECT employee_id
                             FROM fnd_user
                             WHERE user_name IN ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                   OR employee_id IN (&employeeids) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                                                   )
                GROUP BY person_id)
      AND paaf.person_id = fndu.employee_id
      AND (fndu.user_name IN ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
           OR fndu.employee_id IN (&employeeids) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                                )
ORDER BY pcr.control_group_id, pcr.control_rule_id, fndu.user_name

Position Based :-
 
- Shows the Approval Group / Assignment rule details for specified approvers based on their POSITION in HR  

/* Formatted on 7/4/2014 11:47:45 AM (QP5 v5.115.810.9015) */
SELECT fndu.user_name,
       papf.full_name,
       paaf.person_id,
       pcr.control_group_id,
       pcr.control_rule_id,
       pcr.last_update_date,
       pcr.rule_type_code,
       pcr.object_code,
       pcr.amount_limit,
       pcr.segment1_low low1,
       pcr.segment2_low low2,
       pcr.segment3_low low3,
       pcr.segment4_low low4,
       pcr.segment5_low low5,
       pcr.segment6_low low6,
       pcr.segment7_low low7,
       pcr.segment8_low low8,
       pcr.segment1_high high1,
       pcr.segment2_high high2,
       pcr.segment3_high high3,
       pcr.segment4_high high4,
       pcr.segment5_high high5,
       pcr.segment6_high high6,
       pcr.segment7_high high7,
       pcr.segment8_high high8
FROM po_control_rules pcr,
     po_position_controls_all ppca,
     po_control_functions pcf,
     per_all_assignments_f paaf,
     per_all_people_f papf,
     fnd_user fndu
WHERE     pcr.control_group_id = ppca.control_group_id
      AND ppca.org_id = &myorgid
      AND ppca.control_function_id = pcf.control_function_id
      AND pcf.control_function_name = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
      AND ppca.position_id = paaf.position_id ---- THIS MAKES THE QUERY FOR POSITION BASED APPROVALS
      AND paaf.effective_end_date >= SYSDATE
      AND papf.effective_end_date >= SYSDATE
      AND paaf.person_id = papf.person_id
      AND CONCAT (CONCAT (paaf.person_id, '-'),
                  NVL (paaf.object_version_number, 0)
         ) IN
               (SELECT CONCAT (CONCAT (person_id, '-'),
                               NVL (MAX (object_version_number), 0)
                       )
                FROM per_all_assignments_f
                WHERE person_id IN
                            (SELECT employee_id
                             FROM fnd_user
                             WHERE user_name IN ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                   OR employee_id IN (&employeeids) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                                                   )
                GROUP BY person_id)
      AND paaf.person_id = fndu.employee_id
      AND (fndu.user_name IN ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
           OR fndu.employee_id IN (&employeeids) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
                                                )
ORDER BY pcr.control_group_id, pcr.control_rule_id, fndu.user_name