




















































(For more resources on Oracle, see here.)
In this article, we are going to take a look at workflow . There is a fair bit to go through but by the end of this article you will have a good grasp of the concepts of developing workflows and how to monitor them. We will also delve into business events and advanced queues as they are fairly closely related, especially in Release 12. Workfl ow Builder can be downloaded from Oracle (follow note ID 261028.1 from Oracle Support).
In this article we are going to create a workflow that is triggered by a business event. We are going to create our own advanced queue and service component that will monitor the inbound XML messages for the recipes in this article.
We are going to build a solution for a scenario where a signed absence form is sent in to the office from an employee's manager. The document is scanned and an XML message is extracted from metadata in the absence form. The XML message is then en-queued onto the advanced queue. The message on the queue launches a workflow, triggered by a business event, that will perform a number of actions and will end up creating an absence. When we create the workflow we will progress the workflow throughout the article until we have a solution to the business scenario. We will be starting the process at the point where we put an XML message onto the inbound queue. There will be a number of features of workflow that will be demonstrated, which will include:
Workflow builder is a development tool we will use to develop our workflow processes. It has a navigator similar to Oracle forms which is used to define attributes, processes, notifications, functions, events, messages, and lookup types. We can create and use these objects to build our processes. It is assumed that you have it installed on your PC.
There is a feature called access protection in Workflow Builder and this allows objects within a workflow to be locked. This prevents seeded workflows from being modified. Nearly all objects within Oracle Workflow have an Access tab on the Properties window except lookup codes, function attributes, and message attributes which inherit access protection settings from their parent object. The level of access can be set in Workflow Builder and developers need to set access levels as defined in the following list:
An object will be locked to users that have a higher protection level than the object. You can see this as there is a padlock against objects that you do not have the access level to modify. The access level defaults to 100 and as an e-Business Suite developer, we always operate with an access level of 100. The access level can be modified by navigating to Help | About Oracle Workflow Builder as shown in the following screenshot:
Oracle does not support customizations to standard workflows that have a protection level less than 100. We should not alter an object's protection level if it is less than 100 and we should never change the access level with the intention of modifying an object.
We have discussed access control and how it determines the objects that should not be modified. However, always refer to the product-specific user guide and documents available through Oracle Support for details relating to specific seeded workflows. The product specific documentation will detail what should not be modified. Oracle Support Services will not support extensions to any workflow processes that they specifically state should not be modified.
In addition to what we have discussed relating to access, it is also important to understand what the workflow engine is and how we can create supplemental engines to process costly activities such as Deferred workflows through the Workflow Background Process concurrent program.
The Workflow Engine is a collection of server side PL/SQL tables, views, packages, and procedures embedded in the Oracle Applications database. It processes activities as they are executed at runtime and will process these through to completion provided each preceding activity completes successfully. When an activity gets stuck, deferred, or timed out, it will be left in that state to be processed by the Workflow Background Engine as these activity statuses are too costly to be maintained at runtime. When the background engine runs any stuck, deferred, or timed out activity statuses will be re-evaluated and the workflow will resume if logical conditions permit it.
The workflow engine will process all function activities and send out notifications automatically. It can support numerous logical conditions such as launching subprocesses, running parallel processes, looping, and branching.
The Workflow Background Process concurrent program is available in the System Administrator responsibility request group and can also be run from the Oracle Applications Manager screen. Generally, we will schedule a background engine concurrent program for specific item types to run periodically but there should be at least one background engine for each of the following:
There are a number of parameters for the concurrent program that allow us to restrict the process to handle activities for specific item types, and within specific cost ranges. We can select parameters to process any combination of deferred, timed out, or stuck activities. A separate background engine to check for stuck processes can be scheduled at less frequent intervals than those scheduled for deferred activities.
For more information on Oracle Workflow refer to the Oracle Workflow User Guide, Oracle Workflow Developers Guide, and Oracle Workflow API Reference Guide .
Create the database objects for this article before you start by using a script provided. The code comes with the readme file, readme_5_1.txt.
We are going to create a number of objects that we will use throughout the article. For all the database objects, there is a script provided called 4842_05_01.sh. The following recipe provides details of how to run the script.
To create an advanced queue , perform the following steps:
cd $XXHR_TOP/install mkdir ch5
cd ch5
chmod 775 4842_05_01.sh
./4842_05_01.sh apps/apps
We have now created all of the database objects for this article.
We will now create an advanced queue and the messages we put into the queue will trigger our workflow process later in the article. The creation of advanced queues is performed by calls to the dbms_aqadm package. Advanced queues are very different from normal database tables. For example, we cannot insert records directly into the advanced queue tables. We must enqueue and dequeue messages to the advanced queue. When we create an advanced queue we will perform the following tasks:
To perform these actions there is a script in the download bundle called XXHR_CREATE_ABS_ AQ.sql. We are going to use this script to create the queue in the APPLSYS schema.
To create the advanced queue, perform the following steps:
cd $XXHR_TOP/install/ch5
sqlplusapplsys/<apps password>
(Remember the APPLSYS password will be the same as the apps password.)
SQL> @XXHR_CREATE_ABS_AQ.sql
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE (OBJECT_NAME LIKE 'XXHR%ABS%' OR OBJECT_NAME LIKE 'XXHR%XML%')ORDER BY 1, 2
The advanced queue will be used to enqueue XML messages in this article. We will use a script to put XML messages onto the queue. The queue will be monitored by an agent listener so that any message will trigger a business event. We will subscribe to the business event to launch a workflow.