Oracle Workflow Builder

by Andy Penver | May 2012 | Oracle

In this article by Andy Penver, we will cover the following:

  • Introducing Workflow Builder
  • Installing the database objects
  • Creating an advanced queue
  • Building a workflow
  • Creating a business event
  • Viewing a workflow status and monitoring progress
  • Adding a function
  • Creating a lookup
  • Creating a subprocess
  • Sending notifications and processing responses
  • Calling an API

(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:

  1. Create a local directory C:\packt\scripts\ch5 where the scripts are downloaded to.
  2. Open Putty and connect to the application tier user.cd $XXHR_TOP/install    mkdir ch5 
  3. Create a new directory on the application tier under $XXHR_TOP/install with the following commands:

    cd $XXHR_TOP/install    mkdir ch5 

  4. Navigate to the new directory with the following command:

    cd ch5 

  5. Open WinSCP and ftp the files from C:\packt\scripts\ch5 to $XXHR_TOP/ install/ch5 as shown in the following screenshot:
  6.  

  7. In Putty, change the permissions of the script with the following command:

     chmod 775 4842_05_01.sh 

  8. Run the following script to create all of the objects by issuing the following command:

    ./4842_05_01.sh apps/apps 

  9. 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.
  10. 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:

  1. Ftp the SQL script XXHR_CREATE_ABS_AQ.sql to $XXHR_TOP/install/ch5.
  2. Open a Putty session and change the directory to $XXHR_TOP/install/ch5 with the following command:

    cd $XXHR_TOP/install/ch5 

  3. 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.)

  4. Run the SQL script with the following command:

    SQL> @XXHR_CREATE_ABS_AQ.sql 

  5. When the script has completed type exit to come out of SQL*Plus.
  6. Now you can close the Putty session.
  7. 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.

(For more resources on Oracle, see here.)

Building a workflow

We are going to start off by creating a simple workflow and then we will save it to the database. We need to do this before we create the business event subscription as we must select the workflow when we configure the business event subscription later in the article. To begin with, the workflow will have just a start and end function which all workflows must have. We will create some attributes which we will use later in the article to store the values passed in an XML payload.

To achieve this, we will need to complete a number of mini recipes as follows:

  • Creating a new workflow
  • Creating a new item type
  • Creating a new process
  • Creating a start function
  • Creating an end function
  • Creating attributes to store the event details
  • Assigning the start event details
  • Saving a workflow to the database

Creating a new workflow

We will start by creating a new workflow using a workflow template.

Getting ready

It is assumed that you have installed Workflow Builder locally on your PC.

How to do it...

To create a new workflow, perform the following steps:

  1. Open Workflow Builder.
  2. Browse to the Workflow Home | wf | DATA | US directory and select the WFSTD.wft template.
  3. Select File | Save As from the toolbar and rename the workflow to XXHRIABS.

How it works...

When we create a new workflow we will use a template. This is because it already has all of the standard objects in it which we will use in our workflow.

Creating a new item type

To add new processes we must first create a new item type.

How to do it...

To create a new item type we need to add a new item type in the navigator and complete the item type properties. To create a new item type, perform the following steps:

  1. Highlight XXHRIABS and right-click as shown in the following screenshot:

  2. Select New Item Type from the pop-up menu.
  3. Complete the item type property details as shown in the following table and click OK.

    Internal Name

    XXHRIABS

    Display Name

    XXHR Inbound Absence Item

    Description

        XXHR Inbound Absence Item

    Persistence

       Temporary

    Number of Days

           0

    Selector

     

How it works...

The Item Type is the definition of the workflow. The Internal Name is what is referred to when we search for the workflow. The Internal Name is in uppercase and is the short name for the workflow.

Creating a new process

We are going to add a new process to our workflow which will be triggered by a subscription to a business event. We will first create the process and then look at the start function in the next recipe.

How to do it...

We will now create our main process for the workflow by following the given steps:

  1. Navigate to XXHRIABS | XXHR Inbound Absence Item | Processes.
  2. Right-click Processes and select New Process from the pop-up menu.
  3. Complete the properties of the new process with the details in the following table:

    Internal Name

    XXHR_INBOUND_ABSENCES

    Display Name

     XXHR Inbound Absences

    Description

     XXHR Inbound Absences

    Icon

     PROCESS.ICO

    Runnable

     þ

    The properties will now be as shown in the following screenshot:

  4. Click the OK button.

How it works...

The process diagram is a graphical representation of the workflow. It displays the logical steps that a process may follow. We can drag-and-drop our functions and notifications into the diagram and connect them together to represent the connection between activities.

Creating a start function

Every workflow has to have a Start function. It is a special function that is defined as a start process in the function properties.

How to do it...

To create the Start function, perform the following steps:

  1. Navigate to XXHRIABS | XXHR Inbound Absence Item | Processes.
  2. Double-click the XXHR Inbound Absences process and a blank process diagram window will open.
  3. Click the Business Event icon on the toolbar and create a business event activity in the process diagram as shown in the following screenshot.

    You will notice that the cursor becomes a crosshair as you move the mouse in the process window as shown in the following screenshot. Click the mouse button while the cursor is a crosshair and the Properties window will open.

  4. Complete the details of the Event tab as shown in the following table:

    Item Type

    XXHR Inbound Absence  Item

    Internal Name

    o         ABSENCE_START_EVENT

    Display Name

    o         XXHR Inbound Absence

    Description

    o         XXHR Inbound Absence

    Icon

    o         EVENT.ICO

    Event Action

    o        Receive

    Event Filter

    o         oracle.apps.xxhr.absence.inbound

    Cost

    o         0.00

  5. Click on the Node tab and set the Start/End property to Start.

  6. Click on OK to accept the properties and the start object will appear as shown in the following screenshot:

How it works...

All processes in a workflow have to have a Start and an End function. The Start function can be a standard function where it is initiated by PL/SQL for example or it can be an event where it is triggered as a result of a subscription to a business event.

Creating an end function

We will now create an end activity to complete the workflow.

How to do it...

  1. Click on the New Function ( ) icon on the toolbar in the XXHR Inbound Absences window.
  2. When the cursor becomes a crosshair, click the mouse in the process window, somewhere to the right of the start event that we created.
  3. When the Properties window opens complete the properties with the details from the following table:

    Item Type

    Standard

    Internal Name

    END

    The function Properties window will look similar to the following screenshot:

  4. Click on the Node tab and set the Start/End property to End.

  5. Click on OK to accept the changes and the Properties window will close as shown in the following screenshot:

  6. Link the Start and End together as shown in the following screenshot:

  7. A black line will show the Start and End connected as shown in the following screenshot:

  8. Click on the Save button.

How it works...

The End function is a standard function that is built in to workflow and is used to represent the end of a process.

Creating attributes to store the event details

We will now create some workflow attributes to store details of event message that will trigger the workflow. We will look into business events shortly so don't worry too much about that right now as we will go through it in more detail.

How to do it...

To create workflow attributes, perform the following steps:

  1. Navigate to XXHRIABS | Attributes.
  2. Right-click the Attributes node and select New Attribute from the pop-up menu:

     

  3. Create three new attributes and set the properties as follows:
    • EVENT_NAME
    • EVENT_KEY
    • EVENT_MESSAGE
    • The following are the properties for the EVENT_NAME attribute:

    Item Type

    XXHR Inbound Absence Item

    Internal Name

    o         EVENT_NAME

    Display Name

    o         Event Name

    Description

    o         Event Name

    Type

    o        Text

    Length

     

    Default Type

    o         Constant

    Default Value

    o       oracle.apps.xxhr.absence.inbound

    The following are the properties for the EVENT_KEY attribute:

    Item Type

    XXHR Inbound Absence Item

    Internal Name

    o         EVENT_KEY

    Display Name

    o         Event Key

    Description

    o         Event Key

    Type

    o         Text

    The following are the properties for the EVENT_MESSAGE attribute:

    Item Type

    XXHR Inbound Absence Item

    Internal Name

    o         EVENT_MESSAGE

    Display Name

    o         Event Message

    Description

    o         Event Message

    Type

    o         Event

How it works...

We have created some attributes that will store the details of the event that triggered the workflow. The event message attribute will store the XML message we are going put onto the advanced queue.

Assigning the start event details

We are going to assign the event attributes in the Event Details tab for the XXHR_INBOUND_ ABSENCES process. We are going to do this so that the details of the business event are captured in them at runtime.

How to do it...

To assign the event attributes, perform the following steps:

  1. Navigate to XXHRIABS | Processes | XXHR_INBOUND_ABSENCES.
  2. Open the properties of the XXHR_INBOUND_ABSENCE business event.
  3. Navigate to the Event Details tab.
  4. Set the following properties as shown in the following table:

    Event Name

    Event Name

    Event Key

    o        Event Key

    Event Message

    o        Event Message

  5. Click on OK.
  6. Save the workflow.

How it works...

The event properties will be stored in these attributes and will be visible when we view the workflow during runtime. Now that we have completed the basic workflow we are going to trigger it from a business event which we will move onto later. The reason we have created a basic workflow definition first and saved to the database is because we have to select it from a list of values when we configure the business event. The list of values is generated from stored workflow definitions in the database.

Saving a workflow to the database

We will be using WinSCP to ftp the files to the server and Putty to upload the workflow to the database. To save the workflow to the database we need to ftp it to the server and upload it to the database using the command line. It is recommended that we do this rather than saving the workflow directly to the database from Workflow Builder, it should be transferred to the application tier and loaded via a script or the command line as follows.

How to do it...

To save the workflow to the database, perform the following steps:

  1. Open WinSCP connecting to the application server.
  2. Ftp the XXHRIABS.wft workflow definition to the $XXHR_TOP/install/ch5 directory.
  3. Open a Putty session.
  4. Navigate to the install directory by typing the following command:

    cd $XXHR_TOP/install/ch5 

  5. Type the following command:

    WFLOAD apps/<password> 0 Y FORCE $XXHR_TOP/install/ch5/XXHRIABS.wft 

  6. The command line will return the details of uploading the workflow as shown in the following screenshot:

How it works...

Now that we have created a workflow and saved it to the database it will be visible within the application when we subscribe to the business event that we will create in the next recipe. The subscription will be configured to launch the XXHRIABS | XXHR_INBOUND_ABSENCES workflow process. Now, each time we enqueue a message to the advanced queue, the workflow will be triggered.

 

Summary

In this article, we looked at developing and deploying and Oracle workflow within EBS. Oracle Workflow is heavily integrated with Oracle EBS to automate and integrate business processes. Oracle supports workfl ow extensions as long as they are done in a supported manner. The tool that is used is Oracle Workfl ow Builder, which is a GUI interface that allows us to drag and drop objects to build or modify processes.




About the Author :


Andy Penver

Andy Penver currently lives and works in the U.K. as a solution architect for a large public sector client. He studied at Christ Church (University of Kent). He has over 18 years of experience in working with Oracle E-Business Suite. He has worked in both the private and public sectors, and has a strong technical background. He has led and managed teams of consultants through the full project lifecycles on some very large programs throughout the U.K. and Europe. Andy has been heavily involved in two large-scale, award-winning implementations of a shared service centre.

Andy is currently the Managing Director of his own consultancy, NU-TEKK Limited.

Andy has previously authored and published a book called Oracle E-Business Suite R12 Core Development and Extension Cookbook.

Books From Packt


 

The Oracle Universal   Content Management Handbook
The Oracle Universal Content Management Handbook

Oracle Essbase 11 Development Cookbook
Oracle Essbase 11 Development Cookbook

Middleware   Management with Oracle Enterprise Manager Grid Control 10g R5
Middleware Management with Oracle Enterprise Manager Grid Control 10g R5

Oracle Database 11g –   Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Oracle Application Express 4.0 with Ext JS
Oracle Application Express 4.0 with Ext JS

Oracle BPM Suite 11g Developer's cookbook
Oracle BPM Suite 11g Developer's cookbook

Oracle   Coherence 3.5
Oracle Coherence 3.5

Oracle APEX 4.1 Reporting
Oracle APEX 4.1 Reporting


Your rating: None Average: 4 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Y
g
s
i
j
K
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software