Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Tuesday, February 28, 2017

Query to Get AME Details which contains Rule Name, Condition and Approver Group

 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
ORDER BY ar.rule_id

References:
http://josephbijoy.blogspot.com/2013/11/query-to-get-ame-details-which-contains.html

3 comments:

  1. So nice to find someone with some original thoughts on this subject seriously. Many thanks for starting this up. This website is something that's needed on the internet.
    Oracle Fusion HCM Technical Coaching

    ReplyDelete
  2. Hi,
    This is very interesting post.Thanks for sharing such a nice post.
    oracle fusion SCM online training


    ReplyDelete