In Oracle E-Biz applications Approvals Management Engine(AME) is widely used for approvals in Purchasing,HR modules. Especially an audit enabled systems, We need to capture the changes in the real-time systems. But any changes on the AME rules will impact the respective transactions and approvals. Unfortunately, the OAF pages do not have the capability to display/show the audit information unlike the PUI forms(Oracle Java Forms) where you can find out the Audit changes on the Java forms easily.
The below query will help you in finding the audit changes over the AME rules.
To check the AME Rule changes(Last modified)
select ar.rule_id,description name,ar.start_date,end_date,ar.creation_date,ar.last_update_date
from apps.ame_rules ar
where 1=1
and description = ‘Test REQ – 5M < REQAMT <= 50M – MEDIUM’
;
To check the AME Rule condition changes(Last modified)
SELECT
*
FROM
(
SELECT
ameconditionusageeo.rule_id,
ameconditionusageeo.condition_id,
ameconditionusageeo.object_version_number,
ameconditionusageeo.start_date,
ameconditionusageeo.end_date,
ameconditionusageeo.creation_date,
ameconditionusageeo.last_update_date,
— ame_utility_pkg.get_condition_description(cond.condition_id) AS description,
condition_type_lookup.meaning AS condition_type,
ic.user_item_class_name AS item_class,
cond.condition_type AS condition_type_code,
‘CASE1’ AS case1,
‘CASE3’ AS case2,
‘DE’ AS delete_switcher,
approver_order_lookup.meaning AS approver_order,
— ame_approver_type_pkg.getapproverdescription(cond.parameter_two) AS approver,
ic.item_class_id,
cond.parameter_one AS approver_order_code
FROM
apps.ame_condition_usages ameconditionusageeo,
apps.fnd_lookups condition_type_lookup,
(
SELECT
*
FROM
apps.fnd_lookups
WHERE
lookup_type = ‘AME_APPROVER_ORDER’
AND enabled_flag = ‘Y’
) approver_order_lookup,
apps.ame_conditions cond,
apps.wf_roles wf,
(
SELECT
*
FROM
apps.ame_attributes_vl
WHERE
sysdate BETWEEN start_date AND nvl(end_date -(1 / 86400), sysdate)
) atr,
(
SELECT
*
FROM
apps.ame_item_classes_vl
WHERE
sysdate BETWEEN start_date AND nvl(end_date -(1 / 86400), sysdate)
) ic
WHERE
sysdate BETWEEN cond.start_date AND nvl(cond.end_date -(1 / 86400), sysdate)
AND cond.condition_id = ameconditionusageeo.condition_id
AND cond.attribute_id = atr.attribute_id (+)
AND nvl(atr.item_class_id, – 99) = ic.item_class_id (+)
AND condition_type_lookup.lookup_type = ‘AME_CONDITION_TYPE’
AND condition_type_lookup.enabled_flag = ‘Y’
AND condition_type_lookup.lookup_code = upper(cond.condition_type)
AND upper(cond.parameter_one) = approver_order_lookup.lookup_code (+)
AND cond.parameter_two = wf.name (+)
) qrslt
WHERE
( rule_id = 30044 — Pass the Rule ID from Query 1 “‘Test REQ – 5M < REQAMT <= 50M – MEDIUM'”
AND condition_type_code <> ‘post’
AND ( ( sysdate BETWEEN start_date AND nvl(end_date -(1 / 86400), sysdate) )
OR ( sysdate < start_date
AND start_date < nvl(end_date, start_date +(1 / 86400)) ) ) )
;
To check the AME Rule Action changes(Last modified)
SELECT
*
FROM
(
SELECT
ameactionusageeo.rule_id,
ameactionusageeo.action_id,
ameactionusageeo.object_version_number,
ameactionusageeo.start_date,
ameactionusageeo.end_date,
ameactionusageeo.creation_date,
ameactionusageeo.last_update_date,
aty.name AS “”ACTION_TYPE_NAME””,
— ame_utility_pkg.get_action_description(ameactionusageeo.action_id) AS “”ACTION””,
aty.action_type_id,
‘Y’ AS “”CASE1″”,
‘A’ AS “”CASE2″”,
atu.rule_type AS rule_type,
aty.user_action_type_name AS user_action_type_name
FROM
apps.ame_action_usages ameactionusageeo,
apps.ame_actions_vl act,
apps.ame_action_types_vl aty,
(
SELECT
*
FROM
apps.ame_action_type_usages
WHERE
rule_type <> 2
AND sysdate BETWEEN start_date AND nvl(end_date -(1 / 86400), sysdate)
) atu
WHERE
( ( 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
) qrslt
WHERE
( rule_id = 30044 ) — Pass the Rule ID from Query 1 “‘Test REQ – 5M < REQAMT <= 50M – MEDIUM'”
