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.
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 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,
'0 = Ready',
1,
'1 = Delayed',
2,
'2 = Retained',
3,
'3 = Exception',
4,
'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
/
Comments
pleasure in learning about this topic.
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 -
DELETE1000005 12-OCT-11
Name of the event is =>oracle.apps.p er.api.ex_emplo yee.actual_term ination_emp1000 00612-OCT-11
Ke y of the event is =>11100000712-O CT-11
Event Data is =>100000812-OCT -11
Total number of parameters passed to event are =>0100000912-OC T-11
With the number of params being passed to this business event being 0, I am unable to understand/figu re out how to derive the person_id whose termination has kicked off the event. Any pointers please?
Regards,
Arch ie.
oracle.apps.pe r.irc.api.offer s.create_offer and fetching the event data using l_event_data := p_event.getEven tData(); 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 'Of;fer%'. Another imporatnt fact i realized that also Record was not inserted in HR_API_TRANSACT ION 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_TRANSACT IOn coz then only i can process my logic on information fields in transaction_doc ument column. Pls help me know all the business event names of the processes running in background of offer creation process in ordered manner.
When I take a look at your site in Safari, it looks fine however when opening in IE, it has some overlapping issues.
I merely wanted to provide you with a quick heads up!
Other than that, great site!
RSS feed for comments to this post