AME Rules and AME Condition Through SQL

By | April 16, 2018

Many times it is needed when we want all AME in one excel to analyze our AME rules. This requirements generally arrises when you are joining in a new company and want to learn all AME Setup ASAP.

So you want to view all rules and its detail at a glance so that it would be easier for you to understand the inner configurations of AME.

Following two queries will help you to find out all rules, conditions, action and approval groups used in the AME. It is very helpful query and has made my life easier. 

Find the magic query below

select * from
(
select r.rule_id,r.description RuleName,r.creation_date,condition_id,’Condition’           Condition_and_Action,ame_utility_pkg.get_condition_description(condition_id) Val, NULL Action_Type
from AME_RULES_TL rtl,AME_RULES r,AME_CONDITION_USAGES cu
where r.rule_id = rtl.rule_id
and rtl.description =’LSG Leave Rule’ –Rule Name
and cu.rule_id = r.rule_id
and rtl.language= ‘US’
and rtl.description like ‘LSG%’
union all
select distinct r.rule_id,r.description RuleName,r.creation_date,a.action_id,’Action’ Condition_and_Action,atl.description,actl.user_action_type_name Action_Type
from AME_RULES_TL rtl,AME_RULES r,AME_ACTION_USAGES au,ame_actions a,ame_actions_tl atl,AME_ACTION_TYPES act,AME_ACTION_TYPES_TL actl
where r.rule_id = rtl.rule_id
and rtl.description =’LSG Leave Rule’ –Rule Name
and au.rule_id = r.rule_id
and rtl.language= ‘US’
and au.action_id = a.action_id
and a.action_id = atl.action_id
and act.ACTION_TYPE_ID = actl.ACTION_TYPE_ID
and act.ACTION_TYPE_ID = a.ACTION_TYPE_ID
and rtl.description like ‘LSG%’
) a
order by rulename,condition_and_action desc

–Query to find Approval Group Definition

select Approval_group_id,name,query_string
from AME_APPROVAL_GROUPS