By Alejandro Sosa-Oracle on May 29, 2012
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.
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; /
Test the business event
Write a PLSQL procedure to create and set the event parameters and then raise it.
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');
--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);
- 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.
fnd_global.apps_initialize(user_id=>0, resp_id=>24240, resp_appl_id=>1);
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%
Oracle Workflow API Reference, section 'Event Subscription Rule Function APIs'
Oracle Workflow Developer's Guide, section 'Error Handling for Event Subscription Processing'