By Alejandro Sosa-Oracle on Aug 30, 2012
Oracle Workflow Notification System can be extended to perform extra validation or processing via PLSQL procedures when the notification is being responded to. These PLSQL procedures are called post-notification functions since they are executed after a notification action such as Approve, Reject, Reassign or Request Information is performed. The standard signature for the post-notification function is
procedure <procedure_name> (itemtype in varchar2, itemkey in varchar2, actid in varchar2, funcmode in varchar2, resultout in out nocopy varchar2);
The post-notification function provides the parameter 'funcmode' which will have the following values:
- 'RESPOND', 'VALIDATE, and 'RUN' for a notification is responded to (Approve, Reject, etc)
- 'FORWARD' for a notification being forwarded to another user
- 'TRANSFER' for a notification being transferred to another user
- 'QUESTION' for a request of more information from one user to another
- 'QUESTION' for a response to a request of more information
- 'TIMEOUT' for a timed-out notification
- 'CANCEL' when the notification is being re-executed in a loop.
Oracle Workflow provides different context information that corresponds to the current notification being acted upon to the post-notification function.
WF_ENGINE.context_nid - The notification ID
WF_ENGINE.context_new_role - The new role to which the action on the notification is directed
WF_ENGINE.context_user_comment - Comments appended to the notification
WF_ENGINE.context_user - The user who is responsible for taking the action that updated the notification's state
WF_ENGINE.context_recipient_role - The role currently designated as the recipient of the notification. This value may be the same as the value of WF_ENGINE.context_user variable, or it may be a group role of which the context user is a member.
WF_ENGINE.context_original_recipient - The role that has ownership of and responsibility for the notification. This value may differ from the value of the WF_ENGINE.context_recipient_role variable if the notification has previously been reassigned.
Let us assume there is an EBS transaction that can only be approved by a certain people thus any attempt to transfer or delegate such notification should be allowed only to users SPIERSON or CBAKER. The way to implement this functionality would be as follows:
- Edit the corresponding workflow definition in Workflow Builder and open the notification.
- In the Function Name enter the name of the procedure where the custom code is handled, for instance, TEST_PACKAGE.Post_Notification
- In PLSQL create the corresponding package TEST_PACKAGE with a procedure named Post_Notification, as follows:
procedure Post_Notification (itemtype in varchar2, itemkey in varchar2, actid in varchar2, funcmode in varchar2, resultout in out nocopy varchar2) is l_count number; begin if funcmode in ('TRANSFER','FORWARD') then select count(1) into l_count from WF_ROLES where WF_ENGINE.context_new_role in ('SPIERSON','CBAKER'); --and/or any other conditions if l_count<1 then WF_CORE.TOKEN('ROLE', WF_ENGINE.context_new_role); WF_CORE.RAISE('WFNTF_TRANSFER_FAIL'); end if; end if; end Post_Notification;
- Launch the workflow process with the changed notification and attempt to reassign or transfer it. When trying to reassign the notification to user CBROWN the screen would like like below:
Check the Workflow API Reference Guide, section Post-Notification Functions, to see all the standard, seeded WF_ENGINE variables available for extending notifications processing.