AME Related Useful SQL Queries

/*GET AME Trx Type's Rules, conditions*/ select apl.APPLICATION_NAME Transaction_type, r.description Rule,r.rule_id,r.start_date,r.end_date ,condition Conditions from (select distinct rule_id, description,start_date,end_date from ame_rules where sysdate between start_date and end_date) r, (select distinct rule_id, LISTAGG (ame_utility_pkg.get_condition_description (acu.condition_id), chr(10)) WITHIN GROUP (order by acu.condition_id) OVER (partition by acu.rule_id) condition from ame_condition_usages acu) cu, ame_rule_usages aru, (select APPLICATION_NAME, application_id from ame_calling_apps_vl acav where application_name = 'Payables Invoice Approval' and rownum = 1) apl where r.rule_id = cu.rule_id --and r.rule_id = 20004 and aru.rule_id = r.rule_id and aru.item_id = apl.application_id order by r.description

AME IMP Queries

AME IMP Queries

/* AME API to fetch all approvers for transaction */

DECLARE

   l_next_approver   ame_util.approverrecord;

   approversout      ame_util.approverstable;

BEGIN

   ame_api.getallapprovers

                       (800,                                -- APPLICATION_ID

                        5,                                  -- TRANSACTION_ID

                        'MANPOWER_REQUISITION_TRANSACTION', -- TRANSACTION_TYPE

                        approversout

                       );

   FOR i IN approversout.FIRST .. approversout.LAST

   LOOP

      DBMS_OUTPUT.put_line (i | '-' || approversout (i).person_id);

      DBMS_OUTPUT.put_line (i || '-' || approversout (i).authority);

   END LOOP;

END;

/* AME API to fetch next approver if available */

DECLARE

   l_next_approver   ame_util.approverrecord;

BEGIN

   ame_api.getnextapprover

                        (800,                                  -- APPLICATION_ID

                         1,                                    -- TRANSACTION_ID

                         'MANPOWER_REQUISITION_TRANSACTION',   -- TRANSACTION_TYPE

                         l_next_approver                       -- OUT PARAMETER

                        );

   DBMS_OUTPUT.put_line (l_next_approver.person_id);

END;

/* AME API to delete current approver from Approver hierarchy */

DECLARE

   l_next_approver   ame_util.approverrecord;

BEGIN

   l_next_approver.person_id := 1473;

   ame_api.deleteapprover

                       (800,                                 -- APPLICATIONI_ID

                        1,                                   --TRANSACTIONI_ID

                        l_next_approver,

                        'MANPOWER_REQUISITION_TRANSACTION'   --TRANSACTION_TYPE

                       );

   DBMS_OUTPUT.put_line (l_next_approver.person_id);

END;

/* Calling WorkFlow from Back End */

DECLARE

   itemkey                NUMBER         := 4;

   itemtype               VARCHAR2 (150) := 'XXMPRFWF';

   created_by_user_name   VARCHAR2 (150) := NULL;

BEGIN

  

   

   wf_engine.createprocess (itemtype, itemkey, 'MANPOWER REQUISITION_PROCE');

  

   SELECT user_name

     INTO created_by_user_name

     FROM fnd_user

    WHERE user_id = 1162;

   DBMS_OUTPUT.put_line (' created_by_user_name ' || created_by_user_name);

   wf_engine.setitemattrtext (itemtype      => itemtype,

                              itemkey       => itemkey,

                              aname         => 'REQUESTOR_UNAME',

                              avalue        => created_by_user_name

                             );

   wf_engine.setitemattrtext (itemtype      => itemtype,

                              itemkey       => itemkey,

                              aname         => 'CREATED_BY_USER_NAME',

                              avalue        => created_by_user_name

                             );

   wf_engine.setitemattrtext (itemtype      => itemtype,

                              itemkey       => itemkey,

                              aname         => 'AME_TRANSACTION_TYPE',

                              avalue        => 'MANPOWER_REQUISITION_TRANSACTION' -AME TRANSACTION NAME

                              );                         

   wf_engine.setitemattrnumber (itemtype      => itemtype,

                                itemkey       => itemkey,

                                aname         => 'AME_TRANSACTION_ID',

                                avalue        => itemkey

                               );

   wf_engine.startprocess (itemtype, itemkey);

   DBMS_OUTPUT.put_line (' Started Successfuly...!!!');

   COMMIT;

END;

/* 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 TRUNC(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 '%MPRF%'

         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'))