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;