-------------------------------------------------------------------------
--SQL Statement to get AME Rules based on a condition across transaction types
-- Parameter:P_AME_CONDITION -- Input condition name(partial-match works)
-------------------------------------------------------------------------
SELECT ame.rulename,ame.creation_date,
ame.condition_and_action "condition/action",
atl.val "Condition_Desc"
FROM (SELECT 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 cu.rule_id = r.rule_id
AND rtl.LANGUAGE = 'US'
UNION ALL
SELECT DISTINCT r.description rulename, r.creation_date,
a.action_id, 'Action' condition_and_action,
atl.description val,
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 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
) ame
WHERE ame_utility_pkg.get_condition_description (condition_id) LIKE '%&P_AME_CONDITION%'
ORDER BY rulename, condition_and_action DESC;
