Friday, November 4, 2016

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)



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect