(For more resources on Oracle, see here.)
Introduction
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:
- Creating attributes
- Creating messages
- Creating lookups
- Branching
- Notifications
- Timeouts
- Events
- Processes and subprocesses
Introducing Workflow Builder
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.
How to do it…
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:
- 0-9 is reserved for Oracle Workflow
- 10-19 is reserved for Oracle Application Object Library
- 20-99 is reserved for Oracle E-Business Suite
- 100-999 is reserved for customer specific extensions
- 1000 is reserved for public
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.
How it works…
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.
There’s more…
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:
- Timed out activities
- Deferred activities
- Stuck processes
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.
See also
For more information on Oracle Workflow refer to the Oracle Workflow User Guide, Oracle Workflow Developers Guide, and Oracle Workflow API Reference Guide .
Installing the database objects
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.
How to do it…
To create an advanced queue , perform the following steps:
- Create a local directory C:packtscriptsch5 where the scripts are downloaded to.
- Open Putty and connect to the application tier user.cd $XXHR_TOP/install mkdir ch5
- Create a new directory on the application tier under $XXHR_TOP/install with the following commands:
cd $XXHR_TOP/install mkdir ch5
- Navigate to the new directory with the following command:
cd ch5
- Open WinSCP and ftp the files from C:packtscriptsch5 to $XXHR_TOP/ install/ch5 as shown in the following screenshot:
- In Putty, change the permissions of the script with the following command:
chmod 775 4842_05_01.sh
- Run the following script to create all of the objects by issuing the following command:
./4842_05_01.sh apps/apps
- The script checks whether all of the files are present in your $XXHR_TOP/install/ ch5 directory and will prompt you to continue if they are all there, so type Y and press Return.
- After the script has completed check the XXHR_4842_05_01.log file for errors. (It will be created in the same directory, $XXHR_TOP/install/ch5).
How it works…
We have now created all of the database objects for this article.
Creating an advanced queue
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:
- Create queue table
- Create queue
- Start queue
- Grant ENQUEUE and DEQUEUE privileges to the apps user
Getting started
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.
How to do it…
To create the advanced queue, perform the following steps:
- Ftp the SQL script XXHR_CREATE_ABS_AQ.sql to $XXHR_TOP/install/ch5.
- Open a Putty session and change the directory to $XXHR_TOP/install/ch5 with the following command:
cd $XXHR_TOP/install/ch5
- Start an SQL*Plus session and log on as the APPLSYS user by typing the following command:
sqlplusapplsys/<apps password>
(Remember the APPLSYS password will be the same as the apps password.)
- Run the SQL script with the following command:
SQL> @XXHR_CREATE_ABS_AQ.sql
- When the script has completed type exit to come out of SQL*Plus.
- Now you can close the Putty session.
- Run the following query to check that all of the objects have been created successfully:
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
How it works…
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.
