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