Oracle Approval Management Engine (AME) Scripts

SELECT   ar.rule_id,

         ar.description rule_name,

         ar.start_date,

         ar.end_date,

         aca.transaction_type_id,

         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,

         ame_rule_usages amu,

         ame_calling_apps aca,

         (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 '%'

     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

     AND ar.rule_id = 12226

     AND amu.item_id = aca.application_id

     AND TO_CHAR (ar.start_date, 'YYYY') = '2016'

     AND aca.fnd_application_id = 200

ORDER BY ar.rule_id;

SELECT rul.rule_id,

       rul.rule_key,

       rul.rule_type,

       rul.description,

       rul.item_class_id,

       rlu.item_id,

       aca.fnd_application_id,

       aca.transaction_type_id,

       rlu.created_by,

       rlu.creation_date,

       rlu.start_date,

       rlu.end_date,

       rlu.priority,

       rlu.approver_category,

       rlu.object_version_number

  FROM ame_rules_vl rul,

       ame_rule_usages rlu,

       ame_calling_apps aca

 WHERE rul.rule_id = rlu.rule_id

   AND rlu.item_id = aca.application_id

   AND (   (SYSDATE BETWEEN rul.start_date AND NVL (rul.end_date - (1 / 86400), SYSDATE))

        OR (SYSDATE < rul.start_date AND rul.start_date < NVL (rul.end_date, rul.start_date + (1 / 86400)))

       )

   AND (   (SYSDATE BETWEEN rlu.start_date AND NVL (rlu.end_date - (1 / 86400), SYSDATE) )

        OR (SYSDATE < rlu.start_date AND rlu.start_date < NVL (rlu.end_date, rlu.start_date + (1 / 86400)) )

       )

   AND SYSDATE BETWEEN aca.start_date AND NVL (aca.end_date - (1 / 86400), SYSDATE)