Approval Management Engine AME queries in Oracle Applications

-- Transaction Type, Rule Name, Condition concatenated values

select   apl.APPLICATION_NAME Transaction_type,  r.description Rule,condition Conditions
from  (select distinct rule_id, description from ame_rules
where sysdate between start_date and end_date)  r,
  (select distinct  rule_id, LISTAGG (ame_utility_pkg.get_condition_description (acu.condition_id), chr(10))
         WITHIN GROUP (order by acu.condition_id) OVER (partition by acu.rule_id) condition from ame_condition_usages acu) cu,
         ame_rule_usages aru,
         (select APPLICATION_NAME, application_id from ame_calling_apps_vl acav
         where  application_name = 'Purchase Requisition Approval'
         and rownum = 1) apl
         where r.rule_id = cu.rule_id
         --and r.rule_id  = 20004
         and aru.rule_id = r.rule_id
         and aru.item_id = apl.application_id
        order by r.description ;


--Rule Name, Condition, Approval Group, Action Type


 SELECT distinct ar.rule_id,
         art.description rule_name,
         ar.start_date,
         ar.end_date,
       --  acu.condition_id,
        -- ame_utility_pkg.get_condition_description (acu.condition_id) condition,
         LISTAGG (ame_utility_pkg.get_condition_description (acu.condition_id), chr(10))
         WITHIN GROUP (order by acu.condition_id) OVER (partition by art.description) condition,
         aty.name action_type,
         ame_utility_pkg.get_action_description (ameactionusageeo.action_id)
            AS approver_group
    FROM ame_rules ar,
         ame_rules_tl art,
         ame_condition_usages acu,
         ame_action_usages ameactionusageeo,
         ame_actions_vl act,
         ame_action_types_vl aty,
         (SELECT *
            FROM ame_action_type_usages
           WHERE rule_type <> 2
                 AND SYSDATE BETWEEN start_date
                                 AND NVL (end_date - (1 / 86400), SYSDATE)) atu
   WHERE ar.rule_id = art.rule_id AND art.language = 'US'
         AND TRUNC (SYSDATE) BETWEEN ar.start_date
                                 AND NVL (
                                        ar.end_date,
                                        TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'))
         AND  (art.description) like 'XXXXXXXXXXXXXXXXXXXX'
         AND acu.rule_id = ar.rule_id
         AND TRUNC (SYSDATE) BETWEEN acu.start_date
                                 AND NVL (
                                        acu.end_date,
                                        TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'))
         AND ( (SYSDATE BETWEEN ameactionusageeo.start_date
                            AND NVL (ameactionusageeo.end_date - (1 / 86400),
                                     SYSDATE))
              OR (SYSDATE < ameactionusageeo.start_date
                  AND ameactionusageeo.start_date <
                         NVL (ameactionusageeo.end_date,
                              ameactionusageeo.start_date + (1 / 86400))))
         AND SYSDATE BETWEEN act.start_date
                         AND NVL (act.end_date - (1 / 86400), SYSDATE)
         AND SYSDATE BETWEEN aty.start_date
                         AND NVL (aty.end_date - (1 / 86400), SYSDATE)
         AND aty.action_type_id = atu.action_type_id
         AND act.action_id = ameactionusageeo.action_id
         AND act.action_type_id = aty.action_type_id
         AND ameactionusageeo.rule_id = ar.rule_id;




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