Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Oracle Workflows - All Articles
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

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,
              '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
/





Anil Passi

Comments   

0 #1 TiffanyBaron 2011-04-20 03:08
Thanks for taking the time to talk about this, I feel fervently about this and I take
pleasure in learning about this topic.
Quote
0 #2 Archie 2011-10-12 01:32
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 -

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.
Quote
0 #3 Gaurav Batra 2012-04-08 18:05
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.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 '&#xOf;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.
Quote
0 #4 Wendy 2021-06-11 09:12
Howdy, I think your blog might be having internet browser compatibility problems.

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!
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner