Saturday, May 24, 2014

How to fetch Oracle AME Rule details based on Condition for across transaction types(SQL QUERY)

-------------------------------------------------------------------------
--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;

No comments:

Post a Comment