Oracle EBS query approver groups : SQL query to find the PO approval Groups in Oracle Apps

Oracle EBS query approver groups : SQL query to find the PO approver Groups in Oracle Apps

Here we are going to discuss about the SQL query to find the PO Hierarchy approver groups in Oracle Apps r12. approver groups is one of the part of Purchasing approval Hierarchy. When we are using the Position based  Purchasing approval Hierarchy in Oracle Apps , then we need to create the approver groups to set the amount limits for the approver like the maximum approval limit to approve the PO and which are the GL accounts combinations(KFF) assigned to this approver group. So it means PO for other GL accounts combinations that are not assigned to this approver group cannot approve the PO for that GL account Combinations. Here below I will share the Complete Oracle EBS query to extract these approver groups information in oracle apps.

 

 

 

 

We can Specify two components in the  approver groups

1. Approval amount Limit

2.Approval GL Account ranges Limit.

 

Approval amount Limit :-

Maximum approval Limit Approver can approve the Purchase Order or Purchase Requisition.

 

Approval GL Account ranges Limit:-

Approver can only approve the Purchase Order only from these GL account ranges in oracle apps. System drive these GL account ranges from PO distribution GL charge Accounts.

 

Detail Oracle EBS query for approver groups

SELECT  po_cntr_grp.control_group_name "Approver Group Name",
 po_cntr_rule.rule_type_code "Approver Group Type",
 po_cntr_rule.amount_limit "Approval Limit"
        (  segment1_low
         || '.'
         || segment2_low
         || '.'
         || segment3_low
         || '.'
         || segment4_low
         || '.'
         || segment5_low
         || '.'
         || segment6_low
        ) From_gl_account,
        (   segment1_high
         || '.'
         || segment2_high
         || '.'
         || segment3_high
         || '.'
         || segment4_high
         || '.'
         || segment5_high
         || '.'
         || segment6_high
        )  To_gl_account,
   FROM apps.po_control_rules po_cntr_rule, apps.po_control_groups_all po_cntr_grp
  WHERE po_cntr_grp.control_group_id =  po_cntr_rule.control_group_id
  AND po_cntr_rule.control_group_id in (select distinct control_group_id from apps.po_position_controls_all where org_id = :p_hr_org)