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

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;

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


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

PositionBased
- Shows the Approval Group / Assignment rule details for specified approvers based on their POSITION in HR
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