Sample Workflow Process
This chapter describes a sample workflow process that illustrates several Oracle Workflow features.
This chapter covers the following topics:
Sample Workflow Process
The sample Requisition workflow process illustrates the integration of different Oracle Workflow features, including results-based branching, parallel branching, subprocesses, timeouts, looping, and integration of PL/SQL documents in a notification.
Note: You should consider this process mainly as an example for explanation purposes, and not for demonstration use. The files necessary to set up and run this process are not provided with Oracle E-Business Suite.
Requisition Process
The Requisition process is an example workflow process that is initiated when a user creates a new requisition to purchase an item. The Requisition process is based on two tables that store approval hierarchy and spending authority information.
Important: Oracle E-Business Suite includes a predefined requisition process that is different from the example process described here. The example process documented in this section is for explanation purposes only and not for production use. For detailed information about runnable workflow processes that are integrated with Oracle E-Business Suite, refer to the appropriate Oracle E-Business Suite user's guide or online documentation. See: Predefined Workflows Embedded in Oracle E-Business Suite.
When a user submits a requisition in this example, the process sends a notification to the next manager in the approval hierarchy to approve the requisition. If the spending limit of the approving manager is less than the requisition amount, the process forwards the requisition to the next higher manager in the approval hierarchy until it finds a manager with the appropriate spending limit to approve the requisition. Each intermediate manager must approve the requisition to move it to the next higher manager. When a manager with the appropriate spending limit approves the requisition, the process ends with a result of Approve.
The process can end with a result of Reject if:
-
Any manager rejects the requisition.
-
The requisition amount is greater than the highest spending limit.
-
The requisition's requestor does not have a manager.
The sample Requisition workflow is based on a sample data model that includes two tables with data: one table that maintains an employee approval hierarchy and another that maintains the spending limit of each employee. These two tables make up the database application used to approve a requisition. In addition, the data model also includes a directory service that identifies the Oracle Workflow users and roles in the example implementation.
The example assumes that the application that creates the requisition provides the name of the employee who prepared the requisition, the requisition amount, the requisition number, a requisition description, and a requisition process owner. The application calls the sample PL/SQL procedure WF_REQDEMO.StartProcess to initiate the Requisition process. See: Sample StartProcess Function.
Sample Requisition Data Model
The data model used in the sample Requisition process includes the following components:
-
Two tables with seed data that make up the sample workflow-enabled database application.
-
WF_REQDEMO_EMP_HIERARCHY - Maintains the employee approval hierarchy. The approval chain consists of these employee user IDs, listed in ascending order with the employee having the most authority listed last: BLEWIS, KWALKER, CDOUGLAS, and SPIERSON.
-
WF_REQDEMO_EMP_AUTHORITY - Maintains the spending limit for each employee. The limits for each employee are as follows: BLEWIS: 500, KWALKER: 1000, CDOUGLAS: 2000, and SPIERSON: 3000.
-
-
User and role data in the WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES tables. The following table shows the users and roles that are used in the example.
Sample Users and Roles User ADMIN Role MANAGERS Role WORKERS Role OTHERS Role SYSADMIN yes WFADMIN yes BLEWIS yes KWALKER yes CDOUGLAS yes yes SPIERSON yes yes -
The PL/SQL specification and body for a packages called WF_REQDEMO, which contains the PL/SQL stored procedures called by the function activities used in the Requisition Process workflow.
-
The Requisition workflow definition in the wfdemo.wft file. You can view this process in Oracle Workflow Builder.
Sample Requisition Item Type
The sample Requisition process is associated with an item type called Requisition. There are two workflow processes associated with Requisition: Requisition Approval and Notify Approver.
The Requisition item type has a persistence type of Temporary and persistence number of days of 0. These properties mean that the runtime data associated with any work items for this item type are eligible for purging as soon as they complete. The item type calls a selector function named WF_REQDEMO.SELECTOR. This selector function is an example PL/SQL stored procedure that returns the name of the process to run when more than one process exists for a given item type. The selector function in this example returns REQUISITION_APPROVAL or 'Requisition Approval' as the process to run.
The Requisition item type also has several attributes associated with it. These attributes reference information in the example application tables. The attributes are used and maintained by function activities as well as notification activities throughout the process. The following table lists the Requisition item type attributes.
| Display Name | Description | Type | Length/Format/Lookup Type |
|---|---|---|---|
| Forward From Username | Username of the person that the requisition is forwarded from | Role | |
| Forward To Username | Username of the person that the requisition is forwarded to | Role | |
| Requestor Username | Username of the requisition preparer | Role | |
| Requisition Amount | Requisition amount | Number | 9,999,999,999.99 |
| Requisition Number | Unique identifier of a requisition | Text | |
| Monitor URL | Monitor URL | URL | |
| Requisition Description | Unique user identifier of a requisition | Text | 80 |
| Requisition Process Owner | Username of the requisition owner | Role | |
| Reminder Requisition Document | Reminder Requisition Document is generated by PL/SQL | Document | |
| Note | Note | Text | |
| Note for reminder | Note for saving the previous responder's note | Text |
Summary of the Requisition Approval Process
To view the properties of the Requisition Approval process, select the process in the navigator tree, and choose Properties from the Edit menu. The Requisition Approval process has a result type of Approval, indicating that when the process completes, it has a result of Approve or Reject, which are the lookup codes in the Approval lookup type associated with the Standard item type. This process activity is also runnable, indicating that it can be initiated as a top level process to run.
The Details property page of the process activity indicates that the Requisition process has an error process assigned to it that is initiated only when an error is encountered in the process. The error process is associated with an item type called WFERROR and is called DEFAULT_ERROR. For example, if you attempt to initiate the Requisition Approval process with a requisition that is created by someone who is not listed in the employee approval hierarchy, the Workflow Engine would raise an error when it tries to execute the Select Approver activity. This error would initiate WFERROR/DEFAULT_ERROR, which is the Default Error Process. See: Default Error Process.
The Requisition Approval process consists of 12 unique activities, several of which are reused to comprise the 15 activity nodes that appear in the workflow diagram. To examine the activities of the process in more detail, we have numbered each node for easy referencing below. The numbers themselves are not part of the process diagram.
Requisition Approval Process Diagram
The Requisition workflow begins when a requisition is submitted with a requisition requestor, requisition number, requisition amount, requisition description, and process owner. The workflow begins at node 1 with the Start activity.
At node 2, the process attempts to select an approver for the requisition. If an approver cannot be found for the requisition, the requestor is notified and the process ends with the final process result of Reject. If an approver is found, then the requestor is notified of who that approver is and a function records in the application that the requisition is being forwarded to the approver. Both of these activities must complete before the approver is actually notified in node 8.
Node 8 is a subprocess that requests the approver to approve the requisition by a specified period of time and if the approver does not respond by that time, the subprocess performs a timeout activity to keep sending a reminder to the approver until the approver responds. If the approver rejects the requisition, the requisition gets updated as rejected in node 9, and the requestor is notified in node 10. The process ends at this point with a result of Reject.
If the approver approves the requisition, the process transitions to node 12 to verify that the requisition amount is within the approver's spending limit. If it is, the process approves the requisition in node 13, and notifies the requestor in node 14. The process ends in this case with a result of Approve.
Requisition Process Activities
Following is a description of each activity listed by the activity's display name. You can create all the components for an activity in the graphical Oracle Workflow Builder except for the PL/SQL stored procedures that the function activities call. Function activities can execute functions external to the database by integration with Oracle Advanced Queuing or execute PL/SQL stored procedures which you must create and store in the Oracle Database. All the function activities in the Requisition process execute PL/SQL stored procedures. The naming convention for the PL/SQL stored procedures used in the Requisition process is:
WF_REQDEMO.<PROCEDURE>
WF_REQDEMO is the name of the package that groups all the procedures used by the Requisition process. <PROCEDURE> represents the name of the procedure.
Several activities are described in greater depth to provide examples of how they are constructed. See: Example Function Activities and Example Notification Activity.
Start (Node 1)
This is a Standard function activity that simply marks the start of the process.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.NOOP |
| Result Type | None |
| Prerequisite Activities | None |
Select Approver (Node 2)
This function activity determines who the next approver is for the requisition by checking the employee approval hierarchy table. This activity also saves the name of the previous approver or the name of the preparer if the requisition was never approved before. If an approver is found, this procedure returns a value of 'T', for True, otherwise it returns a value of 'F' for False.
| Variable | Description |
|---|---|
| Function | WF_REQDEMO.SelectApprover |
| Result Type | Boolean |
| Prerequisite Activities | None |
Notify Requestor No Approver Available (Node 3)
This activity notifies the requisition preparer that no appropriate approver could be found for the requisition. The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, and who the last approver was, if there was any. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
This activity occurs in process node 3. In the property page of the node, the activity is assigned to a performer whose name is stored in an item type attribute named Requestor Username.
| Variable | Description |
|---|---|
| Message | Requisition No Approver Found |
| Result Type | None |
| Prerequisite Activities | Select Approver |
Notify Requestor of Forward (Node 5)
This activity notifies the requisition preparer that the requisition was forwarded for approval. The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, name of the approver that the requisition is forwarded to, name of the previous approver, if any, and the most recent comments appended to the requisition. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
In the property page of this node, the activity is assigned to a performer whose name is stored in an item type attribute named Requestor Username.
| Variable | Description |
|---|---|
| Message | Requisition Forward |
| Result Type | None |
| Prerequisite Activities | Select Approver |
Record Requisition Forward (Node 6)
Currently this activity does nothing. It represents a point in the process where you can integrate a recording function. For example, if you have a purchasing application into which you want to integrate this workflow, you can customize this activity to execute a PL/SQL stored procedure that updates your purchasing application table to indicate that the requisition is being forwarded to the next approver.
| Variable | Description |
|---|---|
| Function | WF_REQDEMO.Forward_Req |
| Result Type | None |
| Prerequisite Activities | Select Approver |
And (Node 7)
This Standard function activity merges two or more parallel branches in the flow only when the activities in all of those branches complete.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.ANDJOIN |
| Result Type | None |
| Prerequisite Activities | Must have at least two separate activities that each transition into this activity. |
Notify Approver (Node 8)
This activity is a subprocess that notifies the approver that an action needs to be taken to either approve or reject the requisition. The subprocess sends a notification to the approver, and if the approver does not respond within a specified time, sends another reminder notification to the approver to take action. See: Summary of the Notify Approver Subprocess.
| Variable | Description |
|---|---|
| Result Type | Approval |
| Prerequisite Activities | Select Approver |
Reject Requisition (Node 9)
Currently this activity does nothing. It represents a point in the process where you can integrate a function. For example, if you have a purchasing application into which you want to integrate this workflow, you can customize this activity to execute a PL/SQL stored procedure that updates your purchasing application table to indicate that the requisition is rejected.
| Variable | Description |
|---|---|
| Function | WF_REQDEMO.Reject_Req |
| Result Type | None |
| Prerequisite Activities | Select Approver, Notify Approver |
Notify Requestor of Rejection (Node 10)
This activity notifies the requisition preparer that the requisition was rejected. The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, name of the manager that rejected the requisition, and comments from that manager. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
In the property page of this activity node, the activity is assigned to a performer whose name is stored in an item type attribute named Requestor Username.
| Variable | Description |
|---|---|
| Message | Requisition Rejected |
| Result Type | None |
| Prerequisite Activities | Notify Approver |
Verify Authority (Node 12)
This function activity verifies whether the current approver has sufficient authority to approve the requisition. The procedure compares the requisition amount with the approver's approval limit amount and returns a value of 'Y' for Yes or 'N' for No. If your business rules are not sensitive to the amount that an approver can approve, then you can remove this activity to customize the process.
| Variable | Description |
|---|---|
| Function | WF_REQDEMO.VerifyAuthority |
| Result Type | Yes/No |
| Prerequisite Activities | Select Approver and Notify Approver |
Approve Requisition (Node 13)
Currently this activity does nothing. It represents a point in the process where you can integrate a function. For example, if you have a purchasing application into which you want to integrate this workflow, you can customize this activity to execute a PL/SQL stored procedure that updates your purchasing application table to indicate that the requisition is approved.
| Variable | Description |
|---|---|
| Function | WF_REQDEMO.Approve_Req |
| Result Type | None |
| Prerequisite Activities | Select Approver, Notify Approver, Verify Authority |
Notify Requestor of Approval (Node 14)
This activity notifies the requisition preparer that the requisition was approved. The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, approver name, and comments from the approver. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
In the property page of the activity node, the activity is assigned to a performer whose name is stored in an item type attribute named Requestor Username.
| Variable | Description |
|---|---|
| Message | Requisition Approved |
| Result Type | None |
| Prerequisite Activities | Select Approver, Notify Approver, Verify Authority |
End (Nodes 4, 11, and 15)
This function activity marks the end of the process. Although the activity itself does not have a result type, each node of this activity in the process must have a process result assigned to it. The process result is assigned in the property page of the activity node. Since the Requisition process activity has a result type of Approval, each End activity node must have a process result matching one of the lookup codes in the Approval lookup type.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.NOOP |
| Result Type | None |
| Prerequisite Activities | Start |
Summary of the Notify Approver Subprocess
To view the properties of the Notify Approver subprocess, select its process activity in the navigator tree, and choose Properties from the Edit menu. The Notify Approver subprocess has a result type of Approval, indicating that when the subprocess completes, it has a result of Approve or Reject, based on the lookup codes in the Approval lookup type. It is not runnable, indicating that it cannot be initiated as a top level process to run, but rather can only be run when called by another higher level process as a subprocess.
The Notify Approver subprocess consists of six unique activities, several of which are reused to comprise the nine activity nodes that appear in the workflow diagram. To examine the activities of the process in more detail, we have numbered each node for easy referencing below. The numbers themselves are not part of the process diagram.
Notify Approver Process Diagram
The subprocess begins at node 1 with the Start activity. At node 2, the comments from the previous approver, if any, which are stored in the Note item attribute, are assigned to the Note for Reminder item attribute for use in the Reminder-Approval Needed notification. Then the Note item attribute is cleared, so that it can be used to store comments from the current approver.
At node 4, the process notifies the current approver to approve a requisition within a specified period of time. If the approver approves the requisition, the subprocess ends at node 7 and returns the result Approve to the top level Requisition process. Similarly, if the approver rejects the requisition, the subprocess ends at node 9 and returns the result Reject to the top level Requisition process.
If the approver does not respond by the due date, the subprocess takes the <Timeout> transition to node 5 to send a reminder to the approver to approve the requisition. Node 5 also has a timeout value assigned to it, and if the approver does not respond to the reminder by that time, the subprocess takes the next <Timeout> transition to loop back to node 5 to send another reminder to the approver. This loop continues until the approver approves or rejects the requisition, which would end the subprocess at node 7 or 9, respectively.
Notify Approver Subprocess Activities
Following is a description of each activity in the Notify Approver subprocess, listed by the activity's display name.
Start (Node 1)
This is a Standard function activity that simply marks the start of the subprocess.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.NOOP |
| Result Type | None |
| Prerequisite Activities | None |
Assign (Node 2)
This is a Standard function activity that assigns a value to an item attribute. This Assign activity node assigns the value of the Note item attribute to the Note for Reminder item attribute.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.ASSIGN |
| Result Type | None |
| Prerequisite Activities | None |
Assign (Node 3)
This is a Standard function activity that assigns a value to an item attribute. This Assign activity node assigns a blank value to the Note item attribute to clear any previous value.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.ASSIGN |
| Result Type | None |
| Prerequisite Activities | None |
Notify Requisition Approval Required (Node 4)
This activity notifies the approver that the requisition needs to be approved or rejected. This activity must be completed within 5 minutes; otherwise it times out.
The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, previous approver name, and preparer name for the requisition when the notification is sent. The special WF_NOTIFICATION() message function is called to display these attributes in a message attribute table within the message, and also to include a notification history table in the message. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
The message includes a special RESULT attribute and a "Respond" attribute. The RESULT attribute has a display name of Action and prompts the approver to respond with a value of 'APPROVE' or 'REJECT' from the lookup type called Approval. The value that the approver selects becomes the result that determines which activity branch the Workflow Engine transitions to next.
The "Respond" attribute is called Note and this attribute prompts the approver for optional comments to include in the notification response.
In the property page of this activity node, the activity is assigned to a performer whose name is stored in an item type attribute named Forward To Username.
| Variable | Description |
|---|---|
| Message | Requisition Approval Required |
| Result Type | Approval |
| Prerequisite Activities | Select Approver |
Reminder-Approval Needed (Node 5)
This activity occurs only if the Notify Requisition Approval Required activity times out before being completed. This activity sends a reminder notice to the approver that the requisition needs to be approved or rejected.
The message includes 'Send' attributes that display the requisition number, requisition description, requisition amount, previous approver name, and preparer name for the requisition when the notification is sent. The special WF_NOTIFICATION() message function is called to include a notification history table in the message. The message also includes a 'Send' attribute for the from role that sent the notification, to be displayed in the notification header.
The message includes a special RESULT attribute and a "Respond" attribute. The RESULT attribute has a display name of Action and prompts the approver to respond with a value of 'APPROVE' or 'REJECT' from the lookup type called Approval. The value that the approver selects becomes the result that determines which activity branch the Workflow Engine transitions to next.
The "Respond" attribute is called Note and this attribute prompts the approver for optional comments to include in the notification response.
In the property page of this activity node, the activity is assigned to a performer whose name is stored in an item type attribute named Forward To Username.
| Variable | Description |
|---|---|
| Message | Requisition Approval Required Reminder |
| Result Type | Approval |
| Prerequisite Activities | Select Approver, Notify Requisition Approval Required |
Or (Nodes 6 and 8)
This Standard function activity merges two or more parallel branches in a flow as soon as an activity in any one of those branches completes.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.ORJOIN |
| Result Type | None |
| Prerequisite Activities | None |
End (Nodes 7 and 9)
This function activity marks the end of the subprocess. Although the activity itself does not have a result type, each node of this activity in the subprocess must have a process result assigned to it. The process result is assigned in the property page of the activity node. Since the Notify Approver process activity has a result type of Approval, each End activity node must have a process result matching one of the lookup codes in the Approval lookup type.
| Variable | Description |
|---|---|
| Function | WF_STANDARD.NOOP |
| Result Type | None |
| Prerequisite Activities | Start |
Sample StartProcess Function
To initiate the sample Requisition process, an application calls a PL/SQL stored procedure named WF_REQDEMO.StartProcess.
To examine StartProcess in more detail, we divide the procedure into several sections and number each section with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.
(1) procedure StartProcess
(RequisitionNumber in varchar2,
RequisitionDesc in varchar2,
RequisitionAmount in number,
RequestorUsername in varchar2,
ProcessOwner in varchar2,
Workflowprocess in varchar2 default null,
item_type in varchar2 default null) is
(2) ItemType varchar2(30) := nvl(item_type, 'WFDEMO');
ItemKey varchar2(30) := RequisitionNumber;
ItemUserKey varchar2(80) := RequisitionDesc;
(3) begin
wf_engine.CreateProcess (itemtype => ItemType,
itemkey => ItemKey,
process => WorkflowProcess );
(4) wf_engine.SetItemUserKey (itemtype => itemtype,
itemkey => itemkey,
userkey => ItemUserKey);
(5) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUISITION_NUMBER',
avalue => RequisitionNumber);
(6) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUISITION_DESCRIPTION',
avalue => ItemUserKey);
(7) wf_engine.SetItemAttrNumber (itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUISITION_AMOUNT',
avalue => RequisitionAmount);
(8) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUESTOR_USERNAME',
avalue => RequestorUsername);
(9) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'FORWARD_TO_USERNAME',
avalue => RequestorUsername);
(10) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUISITION_PROCESS_OWNER',
avalue => ProcessOwner);
(11) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'MONITOR_URL',
avalue => wf_monitor.GetDiagramUrl
(null,itemtype,itemkey,'NO'));
(12) wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'REM_DOCUMENT',
avalue => 'PLSQL:wf_reqdemo.reminder_req_document/'
||ItemType||':'||ItemKey);
(13) wf_engine.SetItemOwner (itemtype => itemtype,
itemkey => itemkey,
owner => ProcessOwner);
(14) wf_engine.StartProcess (itemtype => itemtype,
itemkey => itemkey );
(15) exception
when others then
wf_core.context('WF_REQDEMO','StartProcess',
RequisitionNumber,RequisitionAmount,
RequestorUsername,ProcessOwner,Workflowprocess);
raise;
(16) end StartProcess;
(1) This section represents the specification of the procedure, which includes the list of parameters that must be passed to StartProcess.
(2) The declarative part of the procedure body begins in this section. StartProcess consists of calls to various Workflow Engine PL/SQL APIs. See: Workflow Engine APIs, Oracle Workflow API Reference.
Since all of these APIs require an item type and item key input, we define ItemType and ItemKey as local arguments. The argument ItemType is defined as 'WFDEMO', which is the internal name for the Requisition item type. The argument ItemKey is the value of the RequisitionNumber parameter that is passed to the StartProcess procedure.
Note: The item key for a process instance can only contain single-byte characters. It cannot contain a multibyte value.
(3) The executable part of the procedure body begins here. This section calls the CreateProcess Workflow Engine API. This API creates a new runtime instance of the Requisition process, whose internal name is 'WFDEMO', and the new instance is identified by the item type and item key that are supplied. See: CreateProcess, Oracle Workflow API Reference.
Note: If you do not pass a value for <process_int_name> when initiating the requisition, the selector function for the Requisition item type determines what process to run.
(4) This section calls the SetItemUserKey Workflow Engine API to mark the new runtime instance of the Requisition process with an end-user key. The end-user key makes it easier for users to query and identify the process instance when it is displayed. See: SetItemUserKey, Oracle Workflow API Reference.
(5), (6), (7), (8), (9), (10), (11), and (12) These sections call either the SetItemAttributeText or SetItemAttributeNumber Workflow Engine APIs to set values for the item type attributes defined for this process. The attributes are REQUISITION_NUMBER, REQUISITION_DESCRIPTION, REQUISITION_AMOUNT, REQUESTOR_USERNAME, FORWARD_TO_USERNAME, REQUISITION_PROCESS_OWNER, MONITOR_URL, and REM_DOCUMENT. See: SetItemAttribute, Oracle Workflow API Reference.
(13) This section calls the SetItemOwner Workflow Engine API to mark the new runtime instance of the Requisition process with a process owner user name. Administrators can query for process instances by process owner. See: SetItemOwner, Oracle Workflow API Reference.
(14) This section calls WF_CORE.CONTEXT() if an exception occurs, to include context information in the error stack to help you locate the source of an error. See: CONTEXT, Oracle Workflow API Reference.
(15) This section calls the Oracle Workflow Engine StartProcess API to invoke the Requisition process for the item type and item key specified. See: StartProcess, Oracle Workflow API Reference.
(16) This section marks the end of the procedure.
Example Function Activities
In general, a function activity must have the following information specified in its Activity property page:
-
Internal name for the activity.
-
Display name for the activity.
-
Result type for the activity, which can be none or the name of a predefined lookup type.
-
Name of the PL/SQL stored procedure that the activity calls.
Also, the PL/SQL stored procedure that a function activity calls must comply with a specific API. See: Standard API for PL/SQL Procedures Called by Function Activities.
Related Topics
Example: Select Approver
The Select Approver function activity calls a PL/SQL stored procedure named WF_REQDEMO.SelectApprover that determines who the next approver is based on the employee approval hierarchy in the example data model.
Result Type
This activity expects a response of 'T' if an approver is found or 'F' if an approver is not found. The possible responses are defined in a lookup type called Boolean, associated with the Standard item type.
PL/SQL Stored Procedure
The PL/SQL stored procedure that this function activity calls is described in detail below. Each section in the procedure is numbered with the notation (1) for easy referencing.
procedure SelectApprover
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out varchar2) is
(1) l_forward_from_username varchar2(320);
l_forward_to_username varchar2(320);
(2) begin
if (funcmode = 'RUN') then
l_forward_to_username := wf_engine.GetItemAttrText (
itemtype => itemtype,
itemkey => itemkey,
aname => 'FORWARD_TO_USERNAME');
(3) if (l_forward_to_username is null) then
l_forward_to_username := wf_engine.GetItemAttrText (
itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUESTOR_USERNAME');
end if;
(4) l_forward_from_username := l_forward_to_username;
(5) wf_engine.SetItemAttrText (itemtype => itemtype;
itemkey => itemkey,
aname => 'FORWARD_FROM_USERNAME';
avalue => l_forward_from_username);
(6) l_forward_to_username := wf_reqdemo.GetManager(
l_forward_from_username);
(7) wf_engine.SetItemAttrText (itemtype => itemtype;
itemkey => itemkey,
aname => 'FORWARD_TO_USERNAME';
avalue => l_forward_to_username);
(8) if (l_forward_to_username is null) then
resultout :='COMPLETE:F';
else
resultout :='COMPLETE:T';
end if;
(9) end if;
(10) if (funcmode = 'CANCEL') then
resultout :='COMPLETE';
return;
end if;
(11) if (funcmode = 'TIMEOUT') then
resultout :='COMPLETE';
return;
end if;
(12) exception
when others then
wf_core.context('WF_REQDEMO','SelectorApprover',itemtype,
itemkey,actid,funcmode);
raise;
(13) end SelectApprover;
(1) The local arguments l_forward_from_username and l_forward_to_username are declared in this section.
(2) If the value of funcmode is RUN, then retrieve the name of the last person that this requisition was forwarded to for approval by assigning l_forward_to_username to the value of the FORWARD_TO_USERNAME item type attribute, determined by calling the Workflow Engine API GetItemAttrText. See: GetItemAttribute, Oracle Workflow API Reference.
(3) If the value of l_forward_to_username is null, then it means that the requisition has never been forwarded for approval. In this case, assign it the value of the REQUESTOR_USERNAME item type attribute, determined by calling the Workflow Engine API GetItemAttrText.
(4) Assign l_forward_from_username to the value of l_forward_to_username.
(5) This section assigns the value of l_forward_from_username to the FORWARD_FROM_USERNAME item type attribute by calling the Workflow Engine SetItemAttrText API.
(6) This section calls the function GetManager to return the manager of the previous approver stored in l_forward_from_username, from the WF_REQDEMO_EMP_HIERARCHY table and assigns that manager's name to l_forward_to_username.
(7) This section assigns the value of l_forward_to_username to the FORWARD_TO_USERNAME item type attribute by calling the Workflow Engine SetItemAttrText API.
(8) If l_forward_to_username is null, meaning there is no manager above the previous approver in the hierarchy, then assign resultout to be COMPLETE:F. Otherwise, assign resultout to be COMPLETE:T.
(9) This ends the check on funcmode =' RUN'.
(10) If the value of funcmode is CANCEL, then assign resultout to be COMPLETE.
(11) If the value of funcmode is TIMEOUT, then assign resultout to be COMPLETE.
(12) This section calls WF_CORE.CONTEXT if an exception occurs, to include context information in the error stack to help you locate the source of an error. See: CONTEXT, Oracle Workflow API Reference.
(13) This section marks the end of the procedure.
Example: Verify Authority
The Verify Authority function activity calls a PL/SQL stored procedure named WF_REQDEMO.VerifyAuthority to verify whether the requisition amount is within the approver's spending limit. This activity is also another example of an automated function activity that returns a result based on a business rule that you implement as a stored procedure.
Result Type
This activity expects a result of 'Yes' or 'No' when the procedure completes to indicate whether the approver has the authority to approve the requisition. These result values are defined in the lookup type called Yes/No, associated with the Standard item type.
PL/SQL Stored Procedure
The PL/SQL stored procedure that this function activity calls is described in detail below. Each section in the procedure is numbered with the notation (1) for easy referencing. We also use the convention 'l_' to identify local arguments used within the procedure.
procedure VerifyAuthority
(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out varchar2) is
(1) l_forward_to_username varchar2(320);
l_requisition_amount number;
l_spending_limit number;
(2) begin
if (funcmode = 'RUN') then
l_requisition_amount := wf_engine.GetItemAttrNumber (
itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUISITION_AMOUNT');
(3) l_forward_to_username := wf_engine.GetItemAttrText (
itemtype => itemtype,
itemkey => itemkey,
aname => 'FORWARD_TO_USERNAME');
(4) if (wf_reqdemo.checkSpendingLimit(l_forward_to_username,
l_requisition_amount)) then
resultout :='COMPLETE:Y';
else
resultout :='COMPLETE:N';
end if;
end if;
(5) if (funcmode = 'CANCEL') then
resultout :='COMPLETE:';
return;
end if;
(6) if (funcmode = 'TIMEOUT') then
resultout :='COMPLETE:';
return;
end if;
(7) exception
when others then
wf_core.context('WF_REQDEMO', 'VerifyAuthority',
itemtype, itemkey, actid, funcmode);
raise;
(8) end VerifyAuthority;
(1) The local arguments l_forward_to_username, l_requisition_amount, and l_spending_limit are declared in this section.
(2) If the value of funcmode is equal to RUN, then assign l_requisition_amount to the value of the REQUISITION_AMOUNT item type attribute, determined by calling the Workflow Engine API GetItemAttrNumber. See: GetItemAttribute, Oracle Workflow API Reference.
(3) This section assigns l_forward_to_username to the value of the FORWARD_TO_USERNAME item type attribute, determined by calling the Workflow Engine API GetItemAttrText.
(4) This section calls the function CheckSpendingLimit for the current approver to determine whether the requisition amount is less than or equal to the approver's spending limit. If the requisition amount is less than or equal to the value in l_spending_limit, meaning the approver has authority to approve, then assign resultout to be COMPLETE:Y. Otherwise, assign resultout to be COMPLETE:N.
(5) If the value of funcmode is CANCEL, then assign resultout to be COMPLETE:.
(6) If the value of funcmode is TIMEOUT, then assign resultout to be COMPLETE:.
(7) This section calls WF_CORE.CONTEXT if an exception occurs, to include context information in the error stack to help you locate the source of an error. See: CONTEXT, Oracle Workflow API Reference.
(8) This section marks the end of the procedure.
Example Notification Activity
The Requisition process contains several notification activities that send informative messages to users. The Notify Approver subprocess also includes notification activities that request a response from a user.
A notification activity requires the following information be defined in its Activity property page:
-
Internal name for the activity.
-
Display name for the activity.
-
Result type for the activity, which can be none or the name of a predefined lookup type.
-
Name of a predefined message that the notification sends out.
Related Topics
Example: Notify Requisition Approval Required
Example: Notify Requisition Approval Required
The Notify Requisition Approval Required activity sends a message called Requisition Approval Required to an approving manager. The message requests that the manager approve or reject a requisition and provides details about the requisition within the body of the message.
Result Type
The manager's response determines the activity that the process transitions to next. The possible responses, 'APPROVE' or 'REJECT', are defined in a lookup type called Approval. These values are defined by the message's special Result attribute, whose display name is Action. These values are also the possible results of the notification activity, as defined by the Result Type field in the Activity property page.
Message
The content of the notification is defined in the message called Requisition Approval Required:
| Variable | Description |
|---|---|
| Subject |
Requisition &REQUISITION_NUMBER, &REQUISITION_DESCRIPTION for &REQUISITION_AMOUNT requires approval |
| Body |
WF_NOTIFICATION(ATTRS,REQUISITION_NUMBER,REQUISITION_AMOUNT, REQUISITION_DESCRIPTION,FORWARD_FROM_USERNAME,REQUESTOR_USERNAME) WF_NOTIFICATION(HISTORY) |
Message attributes, preceded by an ampersand '&' in the subject line or body of the message, are token substituted with runtime values when the notification is sent. However, in order for token substitution to occur properly, all message attributes referenced in the subject line or body of the message must be defined with a source of 'Send'.
In this example, the message body consists of two calls to the special message function called WF_NOTIFICATION(). The first call displays the requisition number, requisition description, requisition amount, previous approver name, and preparer name for the requisition in a message attribute table within the message. The second call displays a notification history table in the message. Additionally, the message includes a special 'Send' attribute named #FROM_ROLE, for the from role that sent the notification, to be displayed in the notification header. See: #FROM_ROLE Attribute and WF_NOTIFICATION() Message Function.
This message also contains a special result message attribute called Action and a 'Respond' message attribute called Note.
The result message attribute is defined in the Result tab of the message's property page. The result attribute prompts the approver to respond with a value from a list of possible values provided by the lookup type specified. The response, in turn, becomes the result of the Notify Requisition Approval Required activity. In this case, the possible response values are 'APPROVE' or 'REJECT', as defined by the Approval lookup type. This result determines which activity the process transitions to next.
The 'Respond' message attribute Note is of type 'Text'. This attribute prompts the approver to enter optional comments when responding to the notification.
Note: To view the content of any message, double-click the message in the navigator tree or select the message and choose Properties from the Edit menu.
Process Node Properties
In the properties page of the Notify Requisition Approval Required activity node in the Notify Approver subprocess, this node is set to Normal because it is neither the start nor end activity in the process.
The performer is set to the Forward To Username item type attribute, indicating that the notification is sent to the user whose name is stored in the item type attribute called 'Forward To Username'. The value of 'Forward To Username' is determined earlier in the Requisition process by the activity called Select Approver.