Oracle Approvals Management (AME) had first published over ten years ago, and also big question is “Why isn’t it so popular”. I think this is all about supporting unnecessary extra extensibility which is not required for traditional approval processes. AME includes many advantages for more flexibility/extensibility/functionality on approval processes. If we would like to check some of these advantages; you can create custom transaction types ,approval groups , approval types. These are definitions called rules, attributes, conditions, approval groups, action types.
Most featured functionality that AME provides us is action types like paralel or serial voting. You can use approval types like consensus (all approvers have to approve to end approval process) , first responder wins (first approver finishes all process). And you can define these approvers dynamically , It supports;
- HR Organization Hierarchy
- Roles
- Users
- Most important one: your custom approval processes with your custom rules
For further information about AME you can check these document for AME implementation : Metalink Note 336901.1 and Note 282529.1
In this case, We created a simple custom WF and PL/SQL package for AME rules and notification process. Custom WF contains two processes a main and a subprocess;
In main Workflow process we call AME process with only a return type approved or rejected. So! We are focussing only AME sub-process in this practice . Now check two notifcations and what function look like ;
These three functions use following package ;
CREATE OR REPLACE package apps.xx_ame_approval_pkg is procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number ); procedure getNextApprover(itemtype in varchar2, itemkey in varchar2, actid in number, funcmode in varchar2, resultout out NOCOPY varchar2) ; procedure updateAmeWithResponse(itemtype in varchar2, itemkey in varchar2, actid in number, funcmode in varchar2, resultout out nocopy varchar2) ; END; /
CREATE OR REPLACE package body apps.xx_ame_approval_pkg is procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number ) is cursor c1 is select fnd_application_id as application_id, transaction_type_id as transaction_type from ame_transaction_types_v where transaction_type_id = x_transaction_type; begin for crec in c1 loop x_transaction_type:=crec.transaction_type; x_application_id := crec.application_id ; end loop ; end ; procedure getNextApprover( itemtype in varchar2, itemkey in varchar2, actid in number, funcmode in varchar2, resultout out NOCOPY varchar2) IS E_FAILURE EXCEPTION; l_transaction_id number; l_next_approver ame_util.approverRecord2; l_next_approvers ame_util.approversTable2; l_next_approvers_count number; l_approver_index number; l_is_approval_complete VARCHAR2(1); l_transaction_type VARCHAR2(200); l_application_id number; l_role_users WF_DIRECTORY.UserTable; l_role_name VARCHAR2(320) ; l_role_display_name VARCHAR2(360) ; l_all_approvers ame_util.approversTable; cursor c1(p_user_name varchar2) is select papf.full_name from fnd_user fu , per_all_people_f papf where fu.employee_id = papf.person_id and fu.user_name = p_user_name and sysdate between papf.EFFECTIVE_START_DATE and nvl(papf.EFFECTIVE_end_DATE,sysdate+1) and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1) ; begin if (funcmode = 'RUN') THEN -- l_transaction_id := TO_NUMBER(itemkey); l_transaction_id:= wf_engine.getItemAttrNumber( itemtype => itemtype, itemkey => itemkey, aname => 'AME_TRANSACTION_ID' ); l_transaction_type := wf_engine.getItemAttrText( itemtype => itemtype, itemkey => itemkey, aname => 'AME_TRANSACTION_TYPE' ); getAmeTransactionDetail ( l_transaction_type,l_application_id ); ame_api2.getNextApprovers4(applicationIdIn=>l_application_id, transactionTypeIn=>l_transaction_type, transactionIdIn=>l_transaction_id, flagApproversAsNotifiedIn => ame_util.booleanTrue, approvalProcessCompleteYNOut => l_is_approval_complete, nextApproversOut=>l_next_approvers); l_next_approvers_count:=l_next_approvers.count ; if (l_is_approval_complete = ame_util.booleanTrue) then resultout:='COMPLETE:'||'APPROVAL_COMPLETE'; return; -- Incase of consensus voting method, next approver count might be zero but there will be pending approvers elsif (l_next_approvers.Count = 0) then ame_api2.getPendingApprovers(applicationIdIn=>l_application_id, transactionTypeIn=>l_transaction_type, transactionIdIn=>l_transaction_id, approvalProcessCompleteYNOut => l_is_approval_complete, approversOut =>l_next_approvers); end if; l_next_approvers_count := l_next_approvers.Count; if (l_next_approvers_count = 0) then resultout:='COMPLETE:'||'NO_NEXT_APPROVER'; return; end if; if (l_next_approvers_count > 0) then resultout:='COMPLETE:'||'VALID_APPROVER'; --return; end if; if (l_next_approvers_count = 1) then l_next_approver:=l_next_approvers(l_next_approvers.first()); wf_engine.SetItemAttrText( itemtype => itemType, itemkey => itemkey, aname => 'APPROVER_USER_NAME' , avalue => l_next_approver.name); wf_engine.SetItemAttrText( itemtype => itemType, itemkey => itemkey, aname => 'APPROVER_DISPLAY_NAME' , avalue => l_next_approver.display_name); /*role name is user name here */ for crec in c1(l_next_approver.name) loop l_role_display_name:=crec.full_name ; end loop ; -- l_role_name:= resultout:='COMPLETE:'||'VALID_APPROVER'; --return; end if; l_approver_index := l_next_approvers.first(); while ( l_approver_index is not null ) loop l_role_users(l_approver_index):= l_next_approvers(l_approver_index).name ; l_approver_index := l_next_approvers.next(l_approver_index); end loop; wf_directory.CreateAdHocRole2( role_name => l_role_name ,role_display_name => l_role_display_name ,language => null ,territory => null ,role_description => 'AME ROLE DESC' ,notification_preference => null ,role_users => l_role_users ,email_address => null ,fax => null ,status => 'ACTIVE' ,expiration_date => null ,parent_orig_system => null ,parent_orig_system_id => null ,owner_tag => null ); wf_engine.setitemattrtext(itemtype => itemtype, itemkey => itemkey, aname => 'RECIPIENT_ROLE', avalue => l_role_name ); return; end if; -- run exception when others then -- The line below records this function call in the error -- system in the case of an exception. wf_core.context('xx_ame_approval_pkg', 'getNextApprover', itemtype, itemkey, to_char(actid), funcmode); raise; end getNextApprover; procedure updateAmeWithResponse(itemtype in varchar2, itemkey in varchar2, actid in number, funcmode in varchar2, resultout out nocopy varchar2) is e_failure exception; l_transaction_id number; l_nid number; l_gid number; l_approver_name varchar2(240); l_result varchar2(100); l_ame_status varchar2(20); l_original_approver_name varchar2(240); l_forwardeein ame_util.approverrecord2; l_transaction_type varchar2 (200 byte); l_application_id number ; begin l_transaction_type := wf_engine.getItemAttrText( itemtype => itemtype, itemkey => itemkey, aname => 'AME_TRANSACTION_TYPE'); getAmeTransactionDetail (l_transaction_type, l_application_id); l_transaction_id:= wf_engine.getItemAttrNumber( itemtype => itemtype, itemkey => itemkey, aname => 'AME_TRANSACTION_ID'); if (funcmode = 'RUN') then -- l_transaction_id := itemkey; l_gid := wf_engine.context_nid; select responder,notification_id into l_approver_name,l_nid from wf_notifications ---where group_id=l_gid where group_id=l_gid and status = 'CLOSED'; l_result := wf_notification.getattrtext(l_nid, 'RESULT'); if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval l_ame_status := ame_util.approvedstatus; elsif (l_result = 'REJECTED') then l_ame_status := ame_util.rejectstatus; else -- reject for lack of information, conservative approach l_ame_status := ame_util.rejectstatus; end if; --set approver as approved or rejected based on approver response ame_api2.updateapprovalstatus2( applicationidin=>l_application_id, transactiontypein=>l_transaction_type, transactionidin=>l_transaction_id, approvalstatusin => l_ame_status, approvernamein => l_approver_name); elsif ( funcmode = 'TIMEOUT' ) then l_gid := wf_engine.context_nid; select responder,notification_id into l_approver_name,l_nid from wf_notifications where group_id=l_gid; --and status = 'CLOSED'; l_result := wf_notification.getattrtext(l_nid, 'RESULT'); if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval l_ame_status := ame_util.approvedstatus; elsif (l_result = 'REJECTED') then l_ame_status := ame_util.rejectstatus; else -- reject for lack of information, conservative approach l_ame_status := ame_util.rejectstatus; end if; --set approver as approved or rejected based on approver response ame_api2.updateapprovalstatus2( applicationidin=>l_application_id, transactiontypein=>l_transaction_type, transactionidin=>l_transaction_id, approvalstatusin => l_ame_status, approvernamein => l_approver_name); elsif ( funcmode = 'TRANSFER' ) then --l_transaction_id := itemkey; l_forwardeein.name :=wf_engine.context_new_role; l_original_approver_name:= wf_engine.context_original_recipient; ame_api2.updateapprovalstatus2(applicationidin=>l_application_id , transactiontypein=>l_transaction_type, transactionidin=>l_transaction_id, approvalstatusin => 'FORWARD', approvernamein => l_original_approver_name, forwardeein => l_forwardeein ); end if; -- run resultout:= wf_engine.eng_completed || ':' || l_result; exception when others then wf_core.context('xx_ame_approval_pkg', 'updateAmeWithResponse', itemtype, itemkey, to_char(actid), funcmode); raise; end updateAmeWithResponse; END; /
What we had done in package above is simple ;
in getNextApprover : we used AME engine to find existing situation of our notification and approvers. Then we created a role for the current approver then set it as notification performer .
in updateAmeWithResponse : we updated current notification status using AME engine(ame_api2 package) and control if it is in timeout period or not , also if it is final approver of AME approver group.
Now we begin to start this WF process from this following package below for Orders which have holds (startApprovalWF procedure) . We can log this process (insertLog procedure) . We set document type attribute to set a custom OAF page for notification content. We also have custom business rules like timeout for notification process just in case our approver doesn’t give a response to it . (getTimeoutDay function). In this case our AME transaction type has the same name as Workflow item type . Package ;
CREATE OR REPLACE package xx_ame_start_appr_pkg is procedure startApprovalWF(errbuff out varchar2, retcode out number, p_order_number in number, p_hold_type in varchar2 , p_comment in varchar2 ) ; function getTimeoutDay (p_header_id in number) return number ; procedure insertLog (itemtype IN VARCHAR2, itemkey IN VARCHAR2, p_log in varchar2 ) ; end ; /
CREATE OR REPLACE package apps.xx_ame_start_appr_pkg is /*This procedure starts our Approval WF via setting AME transaction types and required information (this can be also called as Concurrent Program)*/ procedure startApprovalWF(errbuff out varchar2, retcode out number, p_order_number in number, p_hold_type in varchar2, p_comment in varchar2 ) is cursor c1 is select oh.header_id , oh.order_number from oe_order_holds_all ha , oe_order_headers_all oh where ha.header_id = oh.header_id and oh.ORDER_NUMBER= p_order_number and ha.released_flag = 'N'; itemkey varchar2(240); itemtype varchar2(8); created_by_user_name varchar2(250); l_seq number ; is_entered boolean := false ; begin select xxxl.xxxl_wf_item_key.nextval into l_seq from dual ; itemkey := l_seq||'-'||p_hold_type; itemtype :='YOUR CUSTOM WF NAME'; for crec in c1 loop insertLog (itemtype , itemkey , 'startApprovalWF procedure has been called'); wf_engine.createprocess (itemtype, itemkey , 'YOUR MAIN PROCESS NAME'); select user_name into created_by_user_name from fnd_user where user_id = fnd_profile.value('USER_ID'); 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 =>'HOLD_TYPE', avalue=>p_hold_type) ; wf_engine.setItemAttrText(itemtype =>itemtype, itemkey =>itemkey, aname =>'AME_TRANSACTION_TYPE', avalue=>'YOUR CUSTOM WF NAME') ; wf_engine.setItemAttrNumber(itemtype =>itemtype, itemkey =>itemkey, aname =>'AME_TRANSACTION_ID', avalue=>crec.header_id) ; wf_engine.setItemAttrNumber(itemtype =>itemtype, itemkey =>itemkey, aname =>'ORDER_HEADER_ID', avalue=>crec.header_id ) ; wf_engine.setItemAttrNumber(itemtype =>itemtype, itemkey =>itemkey, aname =>'ORDER_NUMBER', avalue=>crec.order_number ) ; wf_engine.setItemAttrText(itemtype =>itemtype, itemkey =>itemkey, aname =>'START_COMMENT', avalue=>p_comment) ; wf_engine.setItemAttrText(itemtype =>itemtype, itemkey =>itemkey, aname =>'NUMBER_OF_WAIT_DAYS', avalue=>getTimeoutDay(crec.header_id )) ; wf_engine.setitemattrtext (itemtype, itemkey, 'NTF_HOLD_CONTENT_DOC', 'JSP:/OA_HTML/OA.jsp?page=/xxxl/oracle/apps/xxxl/ame/webui/XxxlAmeNtfRN&xx_order_header_id='||crec.header_id ||'&xx_ntf_type='||p_hold_type ||'&xx_item_type='||itemtype ||'&xx_item_key='||itemkey); wf_engine.startprocess (itemtype, itemkey); is_entered := true ; exit; end loop; if (not is_entered) then insertLog (itemtype , itemkey , 'startApprovalWF no record has been found' ); fnd_file.put_line (fnd_file.LOG,'startApprovalWF no record has been found'); retcode := 2; --error RETURN; end if ; end ; /*This function defines our custom rules for timeout function in our AME WF*/ function getTimeoutDay (p_header_id in number) return number is l_control number; begin begin select 1 into l_control from oe_transaction_types_tl ott, oe_order_headers_all ooh where language='US' and ott.name like 'Your Custom Transaction Type' and ooh.header_id=p_header_id and ott.transaction_type_id= ooh.order_type_id; /*Your Custom Transaction Type Order if they wouldn't order until next day at 5:00pm approval process cancel*/ return round( ((24-to_char(sysdate,'HH24') ))+ 17 )*60; --dakika --round( ((24-to_char(sysdate,'HH24') )/24)+ 17/24 , 2) ; exception when others then /*If it is not approved in 5 days it will be cancelled */ return 5*24*60 ; --dakika end ; end ; procedure insertLog (itemtype IN VARCHAR2, itemkey IN VARCHAR2, p_log in varchar2 ) is PRAGMA AUTONOMOUS_TRANSACTION; l_rec xxxl.xxxl_wf_log%rowtype ; begin l_rec.item_type:=itemtype ; l_rec.item_key:=itemkey ; l_rec.xlog:=p_log ; l_rec.object_name:='xx_ame_start_appr_pkg' ; l_rec.trnx_Date :=sysdate ; insert into xxxl.xxxl_wf_log VALUES l_rec ; commit; end ; END ;
I hope this will help to increase in demand for this productive Oracle Engine . We finished Creating Custom Oracle Workflow using AME process ! Thanks.