Friday Apr 12, 2013

Understanding Workflow Errors - Part 1

Workflow Engine and other public APIs throw a variety of user-defined exceptions to indicate specific Engine conditions. It is important that Workflow Developers and Administrators understand the meaning of these errors and know how to deal with them. In this post, we will cover some of the important Workflow Engine related errors and understand how to deal with them.

Engine Errors

3122: Duplicate item 'ITEM_TYPE/ITEM_KEY' could not be created

Error Code: WFENG_ITEM_UNIQUE

Cause: This error may be thrown from WF_ENGINE.CreateProcess when you attempt to create a new instance of a workflow item. This error indicates that the Item Type and Item Key combination you have used to launch a new workflow instance already exists.

Fix: Use an unique item key for that item type

3136: Item 'ITEM_TYPE/ITEM_KEY' cannot be accessed while synchronous process in progress.

Error Code: WFENG_SYNCH_ITEM

Cause: This error may be thrown from WF_ENGINE.CreateProcess when attempt to create a new synchronous workflow instance while another synchronous workflow  instance is already running in the same session. All synchronous workflow instances are created with item key #SYNCH to make sure the workflow instances starts and completes in the same session.

Fix: Wait till the existing synchronous workflow instance completes before creating another one.

3146: Commit happened in activity/function 'ACTIVITY/FUNCTION'

Error Code: WFENG_COMMIT_INSIDE

Cause: While a workflow instance was executing, a commit was issued inside a PLSQL procedure associated to a workflow function activity before or after an error occurred in that PLSQL procedure. The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. 

Fix: You should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.

How to Catch these Errors?

Each error above is associated to an Error Code. Within your PLSQL code, you should following method to capture the specific error based on the error code to act on it.

 begin
   Wf_Engine.CreateProcess('ITEM_TYPE', 'ITEM_KEY', 'PROCESS_NAME');
 exception
    when others then
if (wf_core.error_name = 'WFENG_ITEM_UNIQUE') then
        -- Item already exists, use a unique item key
        Wf_Engine.CreateProcess('ITEM_TYPE', 'ITEM_KEY', 'PROCESS_NAME');
      end if;
 end

Thursday Jan 17, 2013

Oracle Workflow - E-Business Suite vs Standalone

Oracle Workflow has following components that together provide business process management capabilities.

  • Workflow Builder - Windows desktop tool to create and customize workflow processes
  • Workflow Loader - Commandline tool to load workflow definition to database
  • Workflow Engine - PLSQL engine to execute workflow processes definitions
  • Workflow Notification Mailer - Background program to send e-mail notifications
  • Workflow Business Event System - PLSQL and Java based engine to process business events
  • Workflow Agent Listeners - Background program to process business events

Oracle Workflow has been available in two flavors until about 2007.

  1. Workflow Embedded in E-Business Suite
  2. Workflow Standalone

This post is aimed at clarifying how Oracle Workflow in E-Business Suite and Standalone were versioned and the current status of these two flavors.

Workflow Embedded in E-Business Suite

In E-Business Suite, Oracle Workflow is an integral part of foundation modules (FND) alongside Profiles, Concurrent Programs, OA Framework and so on and is shipped in ATG patchsets. For example, Workflow's Java classes are available under $JAVA_TOP like any other FND module, it's PLSQL packages are compiled into APPS schema, datamodel is available under APPLSYS schema and so on. This flavor is also called Embedded Workflow, meaning it was embedded in E-Business Suite.

Versions

Embedded Workflow's packaging, release, installation and configuration follows standard E-Business suite's processes. Embedded Workflow is  not identified with it's own version in E-Business Suite, instead it's codelevel is always identified using the current ATG Patchset level.

For example, if a Customer is on E-Business Suite Release 12.1.3, then their Workflow codelevel is R12.ATG_PF.B.Delta.3 (in addition to any Workflow specific one-offs that may have applied on the baseline) which is a standard way of identifying any FND module.

Current Status

Oracle Workflow embedded in E-Business Suite has the same lifetime as the ATG Patchset in that Oracle E-Business Suite. For example, if a Customer is on Oracle E-Business Suite 12.1.3, the workflow module being integral part of Oracle E-Business Suite installation, has no different lifetime or support policy than the R12.ATG_PF.B Patchset. When looking at Oracle Workflow module from E-Business Suite standpoint, it should not be viewed as a separate product rather at the ATG Patchset level.

For more information please refer to 

Workflow Standalone

All of Oracle Workflow's sub-components and the underlying data model were packaged, released, installed and configured completely independent of E-Business Suite until about late 2007.  A Workflow Standalone installation results in a special database schema like OWF_MGR specific to workflow alone unlike the standard APPS and APPLSYS schemas of E-Business Suite.

Oracle Workflow Standalone however was not shipped on it's own but using following release vehicles.

  • Oracle Database - Upto 10gR2
  • Oracle iAS - Upto 10.1.2
  • Oracle Warehouse Builder - Still packaged with OWB 11g

Oracle Workflow standalone product was available as part of above product offerings which helped non-E-Business Suite Customers to have Workflow (Business Process Management) capabilities such as implementing custom approval flows, document approval routing, sending notifications and so on.

Versions

Workflow Standalone had it's own versioning mechanism that was completely unrelated to E-Business Suite's. For example, following were some of the recent verisons.

  • Oracle Database 10gR2 - Oracle Workflow 2.6.4
  • Oracle iAS 10.1.2 - Oracle Workflow 2.6.3.5
  • Oracle iAS 9.0.4 - Oracle Workflow 2.6.3

Current Status

With the advent of better Business Process Execution standards and tools, Oracle Workflow was ceased to be shipped with Oracle Database and Oracle iAS. Oracle BPEL Process Manager (in Oracle SOA Suite 10g) and Oracle SOA Suite 11g are the recommended technologies to be considered by Customers who were using Oracle Workflow Standalone to implement their business processes.

To this effect, MOS Doc 391546.1 was published to announce obsolescence of Workflow Standalone with Oracle Database 10gR2 as the terminal release vehicle for the product. This note applies only to Workflow as a standalone product and does not affect Workflow embedded in E-Business Suite.

Workflow Standalone Obsolescence Notice

MOS Doc 391546.1 clarifies that Workflow as a standalone product will not be shipped in Oracle Database 11g and above. Since Oracle Database 10gR2 is the last release Workflow Standalone was shipped in, existing Oracle Database 10gR2 Customers who also use Workflow Standalone will continue to receive support as per Oracle Database 10gR2 support policies. This obsolescence notice should not be confused with support for Workflow embedded in E-Business Suite.

Summary

Oracle Workflow embedded in E-Business Suite is being actively maintained and enhancements implemented based on Customer requirements. We have some exciting days ahead of us for Oracle Workflow embedded in E-Business Suite.

Monday Aug 02, 2010

Looping within a Workflow Process

This blog post contributed by Ajay Verma.

While designing a workflow process there seems to be a general tendency to make the process as granular as possible. Each steps are broken into several simpler steps, which are executed as part of separate workflow activities. It does give a much finer understanding of the business processes to the end user.But sometimes this drive to 'make it simpler' can get us into different kinds of complexities.

Lets see this with one particular example:

Below is a simplified version of a workflow process part we came across recently:

Loop

      Fig : Process 1

Here, notifications were being sent using workflow looping wherein completion of an activity causes a transition to another activity that has already been completed earlier. 'Get Recipient' activity in this particular case was determining the suitable notification recipient based on certain business rules while 'Send Notification' activity simply made use of Notification System's Send() API to send the notification to the identified recipient. The loop ended when no more eligible recipient were found.

Two issues were observed in this particular set up:

  1. The process taking too long a time (>40 hours) to complete when notifications were being sent to around 35K users.
  2. The rate at which notifications were being created decreased as the time passed, i.e initially when the process started , around 12-16 notifications(for the respective recipient) got created per second and later this rate got reduced to one notification in 2 minutes.

The issue was reproducible in a separate instance for say 2500 notifications with same observations as mentioned above.It took more than 3 hours for the process to complete and the rate of notification creation did decreased with time.

Now see the below process extract wherein the two activities in Process 1 have been merged into a single activity:

no_loop

      Fig : Process 2

 

The activity simply does the job being carried out by those in Process 1 but within a PL/SQL loop rather than the workflow loop:

--------------------------------------------------------------

Loop Starts

    Get Recipient Logic

    Send Notification to the identified recipient

Loop ends

--------------------------------------------------------------

The process 2 completed for 2500 notifications in less than 2 minutes ! And there was no noticeable performance degradation in the rate at which notifications were created.

So , what made the difference?

In general , internally for each execution of a workflow loop , a number of extra DML statements will run while re-executing the same function activity again and again. And workflow never issue any intermediate commits while executing a given flow until it reaches the end of the flow or a blocking activity. Hence the overall DMLs in Process 1 would result in heavy usage of redo logs and rollback segments until a commit happens. The workflow engine in effect is unnecessarily stressed by the loop here leading to performance deterioration when this long running workflow loop can be easily avoided as done in Process 2.

At the end, from a developer perspective use your own judgement and common sense to decide on how granular your workflow process should be such that it doesn't hits us somewhere else as demonstrated above.

About

This blog is dedicated to bring latest information on Oracle Workflow new features, best practices, troubleshooting techniques and important fixes directly from it's Development Team.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today