Find the parameters passed to a business event

In many cases there is no documentation available in Oracle to specify the list of parameters that are passed to Workflow Business Events. In case you wish to develop a custom subscription for an existing Oracle Apps business events, then it helps for you to know the list of Business Event Parameters being passed by Oracle's code to this business event.

More importantly, some products do not even pass parameters, but instead only pass XML payload to the business event.

Using the simple steps below, you will be able to capture both the XML payload and parameters that are passed to the business event.
Although you can query table wf_deferred, but this table will only list the event details provided the event was raised from pl/sql layer.

The sample code in this article can be applied equally to any business event regardless of whether it gets raised from pl/sql or from java

Steps in brief are

1. Create a temporary debug table and procedure to capture these parameters.

In this example, the table is xx_debug_tab and procedure being xx_debug. The procedure performs autonomous transaction.

2. Create a temporary pl/sql procedure xx_capture_event_parameters and attach this as a subscription to the business event for which you wish to grab parameters.

3. After making the business application to raise business event, do

select * from xx_debug_tab order by n_seq ;

Here is the sample code

create table xx_debug_tab ( v CLOB , n_seq NUMBER, dt date) ;

create sequence xx_debug_s start with 1000005;

create or replace procedure xx_debug ( p in clob) is
pragma AUTONOMOUS_TRANSACTION;
i integer ;
begin

IF p ='DELETE' THEN
delete xx_debug_tab ;
END IF ;
select xx_debug_s.nextval into i from dual ;
insert into xx_debug_tab (v,n_seq ,dt)values ( p , i ,sysdate) ;
commit ;
end ;
/

CREATE OR REPLACE FUNCTION xx_capture_event_parameters(p_subscription_guid IN RAW,

p_event IN OUT NOCOPY wf_event_t)

RETURN VARCHAR2 IS

l_wf_parameter_list_t wf_parameter_list_t;

l_parameter_name VARCHAR2(30);

l_parameter_value VARCHAR2(4000);

n_total_number_of_parameters INTEGER;

n_current_parameter_position NUMBER := 1;

BEGIN

--Clear the debug table to begin with

xx_debug('DELETE');

l_wf_parameter_list_t := p_event.getparameterlist();

n_total_number_of_parameters := l_wf_parameter_list_t.COUNT();

xx_debug('Name of the event is =>' || p_event.geteventname());

xx_debug('Key of the event is =>' || p_event.geteventkey());

xx_debug('Event Data is =>' || p_event.EVENT_DATA);

xx_debug('Total number of parameters passed to event are =>' ||

n_total_number_of_parameters);

WHILE (n_current_parameter_position <= n_total_number_of_parameters) LOOP

l_parameter_name := l_wf_parameter_list_t(n_current_parameter_position)

.getname();

l_parameter_value := l_wf_parameter_list_t(n_current_parameter_position)

.getvalue();

xx_debug('Parameter Name=>' || l_parameter_name || ' has value =>' ||

l_parameter_value);

n_current_parameter_position := n_current_parameter_position + 1;

END LOOP;

RETURN 'SUCCESS';

EXCEPTION

WHEN OTHERS THEN

xx_debug('Unhandled Exception=>' || SQLERRM);

END xx_capture_event_parameters;

-------End of article----------

Use the below SQL to get insight into the business events from the deferred queue itself.

SELECT wd.user_data.event_name,

sender_name,

sender_address,

sender_protocol,

wd.user_data.event_key,

rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,

n.NAME parameter_name,

n.VALUE parameter_value,

decode(state,

'0 = Ready',

'1 = Delayed',

'2 = Retained',

'3 = Exception',

'4 = Wait',

to_char(state)) state,

wd.user_data.send_date,

wd.user_data.error_message,

wd.user_data.error_stack,

wd.msgid,

wd.delay

FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n

WHERE /*lower(wd.user_data.event_name) LIKE 'xx.oracle.apps.fnd.document.processed'

-- AND upper(wd.user_data.event_key) = upper('&user_name')

AND wd.user_data.event_key = '25155'*/

wd.user_data.send_date > SYSDATE - .1

AND wd.user_data.event_name LIKE '%'

ORDER BY wd.user_data.send_date DESC,

wd.user_data.event_name,

wd.user_data.event_key,

n.NAME

Set as favorite

Bookmark

Email This

Comments (3)

Subscribe to this comment's feed

Thanks for taking the time to talk about this, I feel fervently about this and I take pleasure in learning about this topic.
written by TiffanyBaron , April 20, 2011

Thanks for taking the time to talk about this, I feel fervently about this and I take
pleasure in learning about this topic.

report abuse

vote down

vote up

Votes: -1

Thanks! But what if event params equal 0?
written by Archie , October 12, 2011

Hi Anil,

Firstly a big thanks for this article... Real life saver. As you said, there is not much documentation around the Business events. I was wondering if its possible that the HR Employee termination can be called without event params being passed. Was unable to figure out how to loop through the p_event record. Did this and it was a cake-walk...

Now the results I got are something like this -

DELETE100000512-OCT-11
Name of the event is =>oracle.apps.per.api.ex_employee.actual_termination_emp100000612-OCT-11
Key of the event is =>11100000712-OCT-11
Event Data is =>100000812-OCT-11
Total number of parameters passed to event are =>0100000912-OCT-11

With the number of params being passed to this business event being 0, I am unable to understand/figure out how to derive the person_id whose termination has kicked off the event. Any pointers please?

Regards,
Archie.

report abuse

vote down

vote up

Votes: +0

Business Events related to Offer Creation Process In Irecruitment
written by Gaurav Batra , April 08, 2012

Can i Trace all the business events being fired orderly during Offer creation process in Irecruitment because i have subscribed my custom code to an event
oracle.apps.per.irc.api.offers.create_offer and fetching the event data using l_event_data := p_event.getEventData(); expression . This event Data conrtains offer basic information fields (Clob data) but does not contain Basic Salary details whih is also entered in first page (offer creation) along with offer details ....

I may be subscribing my custom code to very first business event i.e Offer creation event.......The basic salary details might be captured in the event data of later business event But i am not able to find the right event name by searching into WF_EVENTS using '&#xOf;fer%'. Another imporatnt fact i realized that also Record was not inserted in HR_API_TRANSACTION in the same event . it seems that oracle.apps.per.irc.api.offers.create_offer is the most initial event which gets fired in after clicking on next button on Offer page whereas i have to subscribe my custom code to the event when data gets inserted into HR_API_TRANSACTIOn coz then only i can process my logic on information fields in transaction_document column. Pls help me know all the business event names of the processes running in background of offer creation process in ordered manner.

report abuse

vote down

vote up

Votes: +0

Write comment

Name

Title

Comment