Tuesday May 29, 2012

Asynchronous Business Event Subscriptions - Troubleshooting Tips

Overview

This blog is intended as an example to help trace an asynchronous business event through it's execution within the Workflow Business Event System. This blog should also help clarify the myth among Oracle Workflow developers and administrators that all business events can be traced through Business Event System AQs.

As a reader of this blog post, it is expected that you understand following steps already.

  • Create a business event using Workflow Administrator Web Applications responsibility
  • Create subscriptions to business events
    • Synchronous subscriptions with phase <= 99
    • Asynchronous subscriptions with phase >100
    • Understand "On Error" subscription attributes such as "Stop and Rollback" and "Skip to Next"

It is strongly recommended that all business events and/or groups that has at least one LOCAL or EXTERNAL subscription should also have one ERROR subscription that launches following seeded error workflow process.

  • Workflow Type - WFERROR
  • Workflow Process - DEFAULT_EVENT_ERROR

If an error subscription is not created event subscription failures may go unnoticed. Create the error subscription as follows.

Subscription2.jpg

Subscription2Details.jpg

Sample Local Subscription

For testing purposes, let us assume following PLSQL rule function (in package XXX_PACKAGE) used in a LOCAL event subscription with phase > 99. This will cause the event to be enqueued to WF_DEFERRED queue and Workflow Agent Listener process to execute it in the background. This function inserts records into a temporary table.

    create table temp_table (key_value varchar2(50), result varchar2(20));
    create or replace package xxx_package is
      function subscription1 (p_subscription_guid in raw,
                              p_event in out nocopy wf_event_t) return varchar2;
    end xxx_package;
    /
    create or replace package body xxx_package is
      function subscription1(p_subscription_guid in raw,
                           p_event in out nocopy wf_event_t) return varchar2 is
        l_result varchar2(20);   
      begin
        --processing...
        l_result := p_event.GetValueForParameter('OUTCOME');
        if l_result='GOOD' then
          insert into temp_table values (p_event.getEventKey(), l_result);
          return 'SUCCESS';
        else
          insert into temp_table values (p_event.getEventKey(), l_result);
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
        end if;
      exception
        when others then
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
      end subscription1;
    end xxx_package;
    /
IMPORTANT NOTE: Return value should be in UPPERCASE, otherwise the Business Event System will not recognize the result.

Test the business event

Write a PLSQL procedure to create and set the event parameters and then raise it. 

     declare
      l_event_name varchar2(50) := 'oracle.apps.fnd.wfds.user.userUpdated';
      l_event_key varchar2(50) := to_char(sysdate, 'DD-MM-RRRR HH:MI:SS');
      l_attributes wf_parameter_list_t;
    begin
      --Add the logic to be executed when the event occurs. In this case the
      -- creation of a user was signaled.
      -- ...
      --Add all the parameters to the list:
      --WF_EVENT.AddParameterToList('OUTCOME', 'WRONG', l_attributes);
      WF_EVENT.AddParameterToList('OUTCOME', 'GOOD', l_attributes);
      -- Raise the event
      WF_EVENT.Raise(p_event_name => l_event_name,
                     p_event_key => l_event_key,
                     p_parameters => l_attributes);
      commit;
    end;
    /

When this block runs with parameter OUTCOME set to 'GOOD', a record is inserted into TEMP_TABLE by the LOCAL subscription's rule function. When a different value is used the function returns ERROR and a record is insert into the temporary table. But since the result is ERROR, the transaction is rolled back and error subscription is executed. The Business Event System uses the error subscription to launch WFERROR:DEFAULT_EVENT_ERROR process to send error notification to SYSADMIN with the details of the exception. These error details shown on the notification are as a result of the calls to WF_EVENT.SetErrorInfo and WF_CORE.Context in the subscription rule function.

SysadminNotification.jpg

Troubleshooting Tips

  • Only Busienss Events with asynchronous subscriptions are enqueued to WF_DEFERRED or WF_JAVA_DeFERRED queues based on whether the subscription is PLSQL based or Java based. All events with synchronous subscriptions are executed in the same thread they are raised in and not enqueued to any AQs.
    NOTE: The event's synchronous subscriptions themselves may enqueue the events to some Out Agents such as WF_JMS_OUT or WF_WS_JMS_OUT and so on which is purely specific to subscription's implementation and not related to core Business Event processing.
  • From Workflow Manager, ensure the Workflow Deferred Agent Listener and the Workflow Java Deferred Agent Listener components are running
  • Ensure an ERROR subscription is created for the business event
  • To trace a business event with Asynchronous Subscription from Raise to Dispatch to Error
    • Ensure an error subscription was created as in the sample above
    • Stop Workflow Deferred Agent Listener and Workflow Error Agent Listener
    • Raise the event
    • Verify the event is enqueued to WF_DEFERRED using below SQL. Assuming the event key is unique across all occurences, it should return 1
      select count(1) from applsys.aq$wf_deferred a where a.user_data.event_name = '&eventName' and a.user_data.event_key = '&eventKey' and a.msg_state = 'READY';
    • Start Workflow Deferred Agent Listener and after few minutes verify the event is processed successfully using above SQL. The count should be 0
    • If the event was not dispatched successfully, check if the event is enqueued to WF_ERROR queue using SQL. If the event errored, it should be in error queue. If this SQL returns 0, the subscription was executed successfully. select count(1) from applsys.aq$wf_error a where a.user_data.event_name = '&eventName'and a.user_data.event_key = '&eventkey'and a.msg_state = 'READY'
    • Start Workflow Error Agent Listener. If there was a record in WF_ERROR queue, after few minutes verify the event is processed successfully using above SQL
    • Now check SYSADMIN's worklist for error notification with details of the error.
  • To obtain E-Business Debug Log messages for business event processing
    • Enable Log profile options for the E-Business Suite user whose session triggers the business event.
      FND: Debug Log Enabled=YES
      FND: Debug Log Level=STATEMENT
      FND: Debug Log Module=wf.plsql%
    • In the case where the event is raised from a PLSQL block you can add the following at begining of the procedure to identify the session and to activate the debug profile options.
      declare

      ...

      begin

        fnd_global.apps_initialize(user_id=>0, resp_id=>24240, resp_appl_id=>1);

        --...

      end;

      fnd_global.apps_initialize will enable the profile options for this PLSQL block for user SYSADMIN, application 'System Administration' and responsibility 'System Administrator'
    • Then query table APPLSYS.FND_LOG_MESSAGES where column MODULE like 'wf.plsql%' to see the debug messages
      sqlplus apps/***** @$FND_TOP/patch/115/sql/wfdbgprnt wf.plsql%

References

Oracle Workflow API Reference, section 'Event Subscription Rule Function APIs'

Oracle Workflow Developer's Guide, section 'Error Handling for Event Subscription Processing'


Tuesday Aug 03, 2010

Oracle Business Event and Subsciptions Execution Flow

This blog post contributed by Shivdas Tomar.

Please read earlier post about Workflow Business Event System.

We will see the exact execution flow when an event is raised using a PLSQL API and a Java API.

Event raised using PLSQL API


As soon as event is deferred to WF_DEFERRED or WF_JAVA_DEFERRED, control is returned back to calling program. Workflow Deferred Agent Listener and Workflow Java Deferred Agent Listener continues execution of the subscriptions respectively from these queues. If there is any error during subscription or generate function execution, event will be enqueued to WF_ERROR or WF_JAVA_ERROR queue respectively. The Error Agent Listeners then execute error subscriptions associated to the event.

plsql_raise.JPG


Event raised using Java API

Once event enqueued to WF_JAVA_DEFERRED queue, further execution will be resumed from the deferred subscription, by Workflow Deferred Java Agent Listener. If any error occurs during processing of a subscription by the Deferred Java Agent Listener, event is enqueued to WF_JAVA_ERROR queue.

java_raise.JPG

 


Monday Aug 02, 2010

Workflow Engine vs Business Event System

Oracle Workflow has two major execution engines.

    • Workflow Engine
    • Workflow Business Event System

Here is a simple comparison of what they process and their associated background components.

Workflow Engine Workflow Business Event System
Executes workflow processes created using Windows based Workflow Builder client Executes subscriptions to business events registered using Event Manager in Workflow Administrator Web Applications Responsibility
Entry point foreground APIs are WF_ENGINE.CreateProcess and WF_ENGINE.StartProcess Entry point foreground API is WF_EVENT.Raise
Execution deferred to background by enqueuing message to AQ WF_DEFERRED_QUEUE_M Execution deferred to background by enqueuing message to AQ WF_DEFERRED
Entry point background API is WF_ENGINE.Background Entry point background API is WF_EVENT.Listen
AQ Payload is SYSTEM.WF_PAYLOAD_T AQ Payload is WF_EVENT_T
Background processing is done by Concurrent Program - FNDWFBG (Workflow Background Engine) Background processing is done by GSC Component - Workflow Deferred Agent Listener
Background Engine is submitted as recurring concurrent request from SRS form or Workflow Manager in OAM Agent Listener is a service component managed through Workflow Manager in OAM

This blog post is as a result of confusion about what AQ and corresponding background process comes into picture when troubleshooting a given problem. For example,

  • When troubleshooting issues with Business Event System, users verify that the Workflow Background Engine is running.
  • When troubleshooting deferred workflow processes, users verify that the Workflow Deferred Agent Listener is running.

It is important to understand the two processing engines in Oracle Workflow, the supporting background components and how these two engines integrate with each other.

PLSQL vs Java Business Event System

Oracle Workflow provides Business Event System implementation within the database (PLSQL) and in the middle tier (Java). The implementation is exactly the same in terms of the event subscription processing in both these layers but the only difference is how the Developer wants to leverage Business Event System's capabilities for event processing requirements. With the availability of Business Event System implementation in PLSQL and Java, different subscription processing scenarios can be achieved.

Subscription Scenarios

    1. Execute PLSQL rule function synchronously or asynchronously. Oracle Workflow provides a bunch of default rule functions in WF_RULE package for your use
    2. Execute Java rule function synchronously or asynchronously. Oracle Workflow provides a default rule function oracle.apps.fnd.wf.bes.WebServiceInvokerSubscription to invoke web services
    3. Launch workflow process
    4. Send event to an agent (AQ)

Event Raise APIs

Workflow Business Events can be raised using one of two following ways.

    1. PLSQL API WF_EVENT.Raise
    2. Java method oracle.apps.fnd.wf.BusinessEvent.raise

Testing Business Events

Starting 12.1.1 and 11.5.10.ATG RUP7 releases, Test Business Event page in Workflow Administrator Web Application responsibility is enhanced to test the business event using both PLSQL and Java Raise APIs.

testevent.png

 

Raise in PLSQL

Clicking button "Raise in PLSQL" for a business event results in the PLSQL API WF_EVENT.Raise being used to raise the event. This results in following behavior for different subscription definitions.

  1. Execute PLSQL subscriptions with phase < 100 in the same session
  2. Enqueue PLSQL subscriptions with phase >= 100 to WF_DEFERRED queue
  3. Enqueue ALL Java subscriptions regardless of phase to WF_JAVA_DEFERRED queue

Raise in Java

Clicking button "Raise in Java" for a business event results in the Java method oracle.apps.fnd.wf.BusinessEvent.raise() being used to raise the event. This results in following behavior for different subscription definitions.

  1. Execute JAVA subscriptions (such as WebServiceInvokerSubscription) with phase < 100 in the same session
  2. Enqueue JAVA subscriptions with phase >= 100 to WF_JAVA_DEFERRED queue
  3. Execute PLSQL subscriptions with phase < 100 in the same session
  4. Enqueue PSLQL subscriptions with phase >= 100 to WF_JAVA_DEFERRED queue

Details of different combinations of subscription types and phases and how the subscription is executed based on where it is raised from is discussed in next section.

Verifying Subscription Execution

As long as the call to the Event Raise APIs does not throw an error, the event is raised successfully. There is a wrong notion among developers and administrators that all the events raised are enqueued to an agent (AQ) such as WF_DEFERRED or WF_JAVA_DEFERRED. No, it is not enqueued to an agent always, but it is driven based on how the subscription is defined. Now how are the event subscriptions dispatched and how to check the status of a subscription execution?

Subscriptions are either synchronous (phase < 100) or asynchronous (phase >= 100) with respect to the layer it is raised such as either PLSQL or Java and that layer's ability to dispatch the subscriptions. Following table gives an idea about how different subscriptions are dispatched.

 

 

 

Scenario Event raised in Subscription type Subscription phase Agent (AQ) Dispatched by
1 PLSQL PLSQL >=100 WF_DEFERRED Workflow Deferred Agent Listener
2 PLSQL PLSQL <100 None Same session as WF_EVENT.Raise
3 PLSQL Java Any WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
4 Java Java >=100 WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
5 Java Java <100 None Same session as BusinessEvent.raise()
6 Java PLSQL >=100 WF_JAVA_DEFERRED Workflow Java Deferred Agent Listener
7 Java PLSQL <100 None Same session as BusinessEvent.raise()

 

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