Prerequisites:-
a. Check the Invoice Approval Workflow at the Payables Option(Payables Responsibility).
b. Check the Validation before Approval if the invoice needs to be validated before it is sent for Approval ( Payables Responsibility
c. The Profile option “AME installed” should be set to “Yes” at the Application level for the Payables application ( System Administrator responsibility).
d. Define Users (System Administrator)
e. Login as SYSADMIN
Navigation: User Management> Users
Select your User Name, click on Update, Click on Assign roles
User Name
XXXX
Click on go and say Update
Click on Assign roles.
Search By
Roles and Responsibilities
Approvals Management Business Analyst
Roles and Responsibilities
Functional Administrator
Roles and Responsibilities
Approvals Management Administrator
Enter Justification for each Responsibility.
-----------------------------------------------------------------------
AME table list
select * from AME_APPROVALS_HISTORY
select * from AME_APPROVAL_GROUPS
select * from AME_APPROVAL_GROUPS_TL
select * from AME_APPROVAL_GROUP_CONFIG
select * from AME_APPROVAL_GROUP_ITEMS
select * from AME_APPROVAL_GROUP_MEMBERS
select * from AME_APPROVER_TYPES
select * from AME_APPROVER_TYPE_USAGES
select * from AME_ACTIONS
select * from AME_ACTIONS_TL
select * from AME_ACTION_TYPES
select * from AME_ACTION_TYPES_TL
select * from AME_ACTION_TYPE_CONFIG
select * from AME_ACTION_TYPE_USAGES
select * from AME_ACTION_USAGES
select * from AME_ATTRIBUTES
select * from AME_ATTRIBUTES_TL
select * from AME_ATTRIBUTE_USAGES
select * from AME_CALLING_APPS
select * from AME_CALLING_APPS_TL
select * from AME_CONDITIONS
select * from AME_CONDITION_USAGES
select * from AME_CONFIG_VARS
select * from AME_CONFIG_VARS_TL
select * from AME_EXCEPTIONS_LOG
select * from AME_FIELD_HELP
select * from AME_HELP
select * from AME_ITEM_CLASSES
select * from AME_ITEM_CLASSES_TL
select * from AME_ITEM_CLASS_USAGES
select * from AME_MANDATORY_ATTRIBUTES
select * from AME_REC_ACCESS_TRANS
select * from AME_RULES
select * from AME_RULES_TL
select * from AME_RULE_USAGES
select * from AME_STRING_VALUES
select * from AME_TEMP_DELETIONS
select * from AME_TEMP_HANDLER_STATES
select * from AME_TEMP_INSERTIONS
select * from AME_TEMP_OLD_APPROVER_LISTS
select * from AME_TEMP_TRANSACTIONS
select * from AME_TEMP_TRANS_ATT_VALUES
select * from AME_TEMP_TRANS_LOCKS
select * from AME_TEST_TRANSACTIONS
select * from AME_TEST_TRANS_ATT_VALUES
select * from AME_TRANS_APPROVAL_HISTORY
select * from AME_TXN_APPROVERS
------------------------------------
SELECT distinct ar.rule_id, art.description rule_name, ar.start_date, ar.end_date,
ame_utility_pkg.get_condition_description (acu.condition_id) 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 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
and ame_utility_pkg.get_condition_description (acu.condition_id) like '%OPERATING%UNIT%95%'
---------------------------
SELECT ar.rule_id,
art.description rule_name,
ar.start_date,
ar.end_date,
ame_utility_pkg.get_condition_description (acu.condition_id) 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 UPPER (art.description) LIKE 'DOPA%'
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
----------------------------------------
Approval Management Engine (AME) - Query to find Rule/condition/approval type/approval group
Agenda:
This article will help to extract AME data from database.
-- Rule
select * from AME_RULES_TL where rule_id=10023;
--Condition
select * from AME_CONDITION_USAGES where rule_id=10023;
select * from AME_CONDITIONS where condition_id in (12013,12023);
-- Condition value
select * from ame_string_values where condition_id in (12013,12023);
-- Attribute
select * from AME_ATTRIBUTES where attribute_id in (15265,15264,15264);
--Action
select * from AME_ACTION_USAGES where rule_id=10023;
select * from AME_ACTIONS where action_id=16322;
--approval group
select * From hr.ame_approval_groups f where approval_group_id='15019';
--appgroval goroup type
select * from AME_ACTION_TYPES where action_type_id in (10013,10007,10006);
--approval group member
select * from ame_approval_group_members where approval_group_id=;
select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
from AME_RULES_TL a, AME_CONDITION_USAGES b, AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e, hr.ame_approval_groups f, AME_ACTIONS g, AME_ACTION_USAGES h,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
and f.creation_date > '01-Jan-2010'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
order by 1,2;
--and f.approval_group_id = i.approval_group_id
-- and i.orig_system = 'PER'
-- and j.person_id = i.orig_system_id
-- AND trunc(sysdate) BETWEEN j.effective_start_date AND j.effective_end_date;
---------------------------
select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
,to_char(f.APPROVAL_GROUP_ID)
from AME_RULES_TL a, AME_CONDITION_USAGES b, AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e
, hr.ame_approval_groups f
, AME_ACTIONS g
, AME_ACTION_USAGES h
,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
--and f.creation_date > '30-Mar-2022'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
and Upper(f.Query_string) Like '%XX_AME_PKG%'
--and f.APPROVAL_GROUP_ID in ()
--and to_char(f.APPROVAL_GROUP_ID) in ('10000','17046')
select * from AME_ACTIONS_TL
select * from AME_ACTION_TYPES
select * from AME_ACTION_TYPES_TL
select * from AME_ACTION_TYPE_CONFIG
select * from AME_ACTION_TYPE_USAGES
select * from AME_ACTION_USAGES
select * from AME_ATTRIBUTES
select * from AME_ATTRIBUTES_TL
select * from AME_ATTRIBUTE_USAGES
select * from AME_CALLING_APPS
select * from AME_CALLING_APPS_TL
select * from AME_CONDITIONS
select * from AME_CONDITION_USAGES
select * from AME_CONFIG_VARS
select * from AME_CONFIG_VARS_TL
select * from AME_EXCEPTIONS_LOG
select * from AME_FIELD_HELP
select * from AME_HELP
select * from AME_ITEM_CLASSES
select * from AME_ITEM_CLASSES_TL
select * from AME_ITEM_CLASS_USAGES
select * from AME_MANDATORY_ATTRIBUTES
select * from AME_REC_ACCESS_TRANS
select * from AME_RULES
select * from AME_RULES_TL
select * from AME_RULE_USAGES
select * from AME_STRING_VALUES
select * from AME_TEMP_DELETIONS
select * from AME_TEMP_HANDLER_STATES
select * from AME_TEMP_INSERTIONS
select * from AME_TEMP_OLD_APPROVER_LISTS
select * from AME_TEMP_TRANSACTIONS
select * from AME_TEMP_TRANS_ATT_VALUES
select * from AME_TEMP_TRANS_LOCKS
select * from AME_TEST_TRANSACTIONS
select * from AME_TEST_TRANS_ATT_VALUES
select * from AME_TRANS_APPROVAL_HISTORY
select * from AME_TXN_APPROVERS
------------------------------------
SELECT distinct ar.rule_id, art.description rule_name, ar.start_date, ar.end_date,
ame_utility_pkg.get_condition_description (acu.condition_id) 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 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
and ame_utility_pkg.get_condition_description (acu.condition_id) like '%OPERATING%UNIT%95%'
---------------------------
SELECT ar.rule_id,
art.description rule_name,
ar.start_date,
ar.end_date,
ame_utility_pkg.get_condition_description (acu.condition_id) 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 UPPER (art.description) LIKE 'DOPA%'
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
----------------------------------------
Approval Management Engine (AME) - Query to find Rule/condition/approval type/approval group
Agenda:
This article will help to extract AME data from database.
-- Rule
select * from AME_RULES_TL where rule_id=10023;
--Condition
select * from AME_CONDITION_USAGES where rule_id=10023;
select * from AME_CONDITIONS where condition_id in (12013,12023);
-- Condition value
select * from ame_string_values where condition_id in (12013,12023);
-- Attribute
select * from AME_ATTRIBUTES where attribute_id in (15265,15264,15264);
--Action
select * from AME_ACTION_USAGES where rule_id=10023;
select * from AME_ACTIONS where action_id=16322;
--approval group
select * From hr.ame_approval_groups f where approval_group_id='15019';
--appgroval goroup type
select * from AME_ACTION_TYPES where action_type_id in (10013,10007,10006);
--approval group member
select * from ame_approval_group_members where approval_group_id=;
select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
from AME_RULES_TL a, AME_CONDITION_USAGES b, AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e, hr.ame_approval_groups f, AME_ACTIONS g, AME_ACTION_USAGES h,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
and f.creation_date > '01-Jan-2010'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
order by 1,2;
--and f.approval_group_id = i.approval_group_id
-- and i.orig_system = 'PER'
-- and j.person_id = i.orig_system_id
-- AND trunc(sysdate) BETWEEN j.effective_start_date AND j.effective_end_date;
---------------------------
select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
,to_char(f.APPROVAL_GROUP_ID)
from AME_RULES_TL a, AME_CONDITION_USAGES b, AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e
, hr.ame_approval_groups f
, AME_ACTIONS g
, AME_ACTION_USAGES h
,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
--and f.creation_date > '30-Mar-2022'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
and Upper(f.Query_string) Like '%XX_AME_PKG%'
--and f.APPROVAL_GROUP_ID in ()
--and to_char(f.APPROVAL_GROUP_ID) in ('10000','17046')
order by 1,2;
0 comments:
Post a Comment