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 Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 
The purpose of this article is two fold.
For the beginners of Oracle Workflow, it shows a quick demo of how you can create Business Events and Test their pl/sql subscription.
Also, a quick video to show you how you can debug the Business Event processing.

Broadly speaking, following steps will be executed
1. Create a Business event
2. Subscribe a PL/SQL Function to this Business Event
3. Test this Business Event
4. Debug the Business event processing
[The debug API used is available from 11.5.10CU4 onwards]



Step 1.
create a Business event named xx.temp.oracle.try


Step 2.
Create a table to prove that subscription was executed.
The subscription of event in Step 1 will insert record into this temporary table.
Just for the purpose of demo, I am creating the tables in apps schema itself.
create table xx_temp_for_demo( msg varchar2(4000), creation_date DATE default sysdate ) ;


Step 3
Create a pl/sql function that will be called when subscription is executed
create or replace FUNCTION xx_temp_demo_subscribe
(
p_subscription_guid In RAW,
p_event IN OUT NOCOPY WF_EVENT_T
)
RETURN VARCHAR2 IS
BEGIN
insert into xx_temp_for_demo
(msg)
values
('Event ' || p_event.getEventName() || ' received for Key=>' ||
p_event.geteventkey());
Return 'SUCCESS';
END xx_temp_demo_subscribe;




Step 4
Confirm that subscription was indeed executed
select * from xx_temp_for_demo ;

For video demo of Step 1-4, please click this link

Now for debugging, you can either use the API below
or alternately, you can query applsys.aq$wf_deferred


Step 5
Create a table that will store debug information in HTML Format into a clob column.
You may optionally store this straight into html file using wf_diagnostics api
create table xxtemp_clob ( event_name VARCHAR2(1000) , event_key varchar2(100) , event_msg clob ) ;


Step 6
Create a procedure that captures the debug HTML Content
You may alternately save the html output into a file
DECLARE
l_content CLOB;
l_event VARCHAR2(240) := '&event';
l_evt_key VARCHAR2(240) := '&key';
BEGIN
wf_diagnostics.get_bes_debug(l_event
,l_evt_key
,l_content);
INSERT INTO xxtemp_clob
(event_name
,event_key
,event_msg)
VALUES
(l_event
,l_evt_key
,l_content);
COMMIT;
END;
/



Step 7
Now view the HTML text that shows the business event debug information
select * from xxtemp_clob


See the video for above two Step5-7 from this link


In case, you wish to run SQL itself, to see the deferred queue, then run SQL Below
SELECT a.msg_state, a.user_data.event_name, a.user_data.send_date, COUNT(*)
FROM applsys.aq$wf_deferred a
WHERE a.user_data.event_name LIKE 'xx%'
AND a.user_data.send_date > SYSDATE - 1
GROUP BY a.msg_state, a.user_data.event_name, a.user_data.send_date
ORDER BY 1, 2 ;



Anil Passi

Comments   

0 #1 kishore Ryali 2008-04-10 13:51
Hi Anil,
Good Article. But the video was bit off. The whole apps screen couldnt fit into a single frame. I felt the software u used for ur old OAF articles was better.

I had few queries on BEs. I read BEs are typically used to trigger a workflow process when a particular event is triggered. Lets say my event is "Booking a Sales Order", I wanted a raise a pl/sql proc on that event. I want to use the values of my Sales Order Header/Line in my proc. How do I access them? Is it through the record types in oe_order_pub?

Can you please give me few real-time scenarios where u have used BEs?

Regards,
Kishore Ryali
Quote
0 #2 Anil Passi 2008-04-19 01:51
Hi Kishore

There are several instances for Business Event usage.
For example, if a Contingent Worker is terminated, an event oracle.apps.per .api.assignment .actual_termina tion_cwk_asg is fired.
You can subscribe to this event, so that your Security Office is notified to disable their security/entry swipe card to your company facilities.

Th anks,
Anil Passi
Quote
0 #3 Rav 2008-06-16 22:36
Hi Anil,

I have performed the tutorial but i have encountered this error message.



Err or

ORA-20002: 3825: Error '-6550 - ORA-06550: line 1, column 14: PLS-00905: object APPS.XX_TEMP_DE MO_SUBSCRIBE is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored' encountered during execution of Rule function 'xx_temp_demo_s ubscribe' for event 'xx.temp.oracle .try' with key 'abc'. ORA-06512: at "APPS.WF_EVENT" , line 2874 ORA-06512: at line 1

Please help.

Thanks a lot,
Rav
Quote
0 #4 Anil Passi- 2008-06-17 02:29
Is function XX_TEMP_DEMO_SU BSCRIBE compiled and valid?
If so, then bounce the WF Services and re-try.

Thanks ,
Anil
Quote
0 #5 sreenikumar 2008-06-29 04:58
Hi Anil,


Thanks for good demo.I have some queries on BE's.I need to capture cash_receipt_id in the standard events on receipt creation/updati on in AR and also need to capture which event has occured.How can i access these values.For inserting receipt details into a table I created five subscriptions for five different standard events on receipt.Can you please tell me the way out for this issue.

Thanks,
Sreenivas.
Quote
0 #6 Anil Passi 2008-06-29 05:30
Hi Sreeni

You need to find out the place from where that business event is being raised and then examine the parameters passed to the business event.

Most likely that BE would be raised from pl/sql package, hence all_source will be the first place where I will look for analysis.

Also , product specific guide might contain the parameters list passed to BE.

To extract the parameters into variables from a XML Payload, see these links
apps2fusion.com/apps/workflows/13-oracle-workflows/106-oracle-workflows-events-with-payload-part-1

apps2fusion.com/apps/workflows/13-oracle-workflows/107-workflows-events-with-xml-payload-part-2


Thanks,
Anil Passi
Quote
0 #7 sreenikumar 2008-07-11 01:57
Hi Anil,

Few small doubts on business events.
I need to create subscriptions for standard business event oracle.apps.ar. receipts.CashRe ceipt.create and also for other events like receipt modify,receipt delete,receipt reverse etc.May i know the parameters passed by standard events.Is there any standard method for getting package names from where in corresponding BE's raises.

And also can i use a single rule function in number of subscriptions.

Regds,
Sreeni.
Quote
0 #8 Anil Passi- 2008-07-11 02:09
Hi Sreeni

You need to look at product documentation for this.
In case you dont find there, then search all_source in text column, for object names begininng with AR or RA.
You need to search from all_source to locate where these business events are getting raised.
This will help you see how the payload/paramet es are being built.

Most likely, in your case p_event.getEven tKey will return the receipt_id or equivalent.

Ye s, you can use the same function in many subscriptions.

Thanks,
Anil Passi
Quote
0 #9 sreenikumar 2008-07-14 10:12
Hi Anil,

Thanks a lot for the updates on business events.

There is a type WF_EVENT_T,in which GetValueForPara meter is a member function .
Suppose in a table(E) , a column(col1) is of type WF_EVENT_T.
I can get e.col1.event_na me and e.col2.eventkey for a particular seeded event.


So in the above query how can i get the parameters of a particular standard event which can be caputured if known by l_id:=p_event.g etvalueforparam eter('xxxxxxxxx xxxxxx');

Than ks in advance,
Sreeni .
Quote
0 #10 khalid_md 2008-08-15 15:34
Hi Anil,

One issue i am facing related to business events. When ever i recompile the package after doing changes subscription is not invoked.
Looking into wf_error table and user_data.error _message i see that there is error stating that the Object (package) is not identified
as the state of object got changed. Then we need to request the DBA's to restart the agent listeners and then this subscription starts
firing successfully. Is this the expected behaviour of business events that when you changes and compile the package , restart of agent listeners
to be done ?

Also , I created a custom queue . when you goto the agent_activity dashboard, against this queue i donot the counts as shown in wf_deferred queue.
like processed,reten tion time etc. Can you provide pointers to this how can we achieve this.

Thanks
Khalid
Quote
0 #11 Anil Passi 2008-08-15 19:00
Hi Khalid

To overcome this package invalid error, you will need to bounce the Workflow Service Manager.

Thank s,
Anil Passi
Quote
0 #12 khalid_md 2008-08-16 03:18
Hi Anil

Thanks for the response.

Also , my second query for the custom queue if you goto workflow manager->agent activity you would see a dashboard listing the agents.
if you see the wf_deferred queue you would see some counts in Ready, waiting, Processed column and some values in other columns. But for our custom queue
we see counts 0 for all columns. Is there anything that is missing in queue creation.

Seco nd - When custom business events gets fired then in the agent activity dashboard if you select the wf_deferred and search you would see the latest business event that got fired. Since we are using phase > 99 ie deferred we could see the business event entry . Against that business event there is XML column which is seen as disabled. But in standard business events this is enabled. Could you please let me know why the xml column for custom business event is showing as disabled. how can we enable it.

Since we are using extensive use of business events so you might find some queries from me and would be great if you can help me out in addressing these issues.

Thanks for your help.

Thanks
K halid
Quote
0 #13 Anil Passi 2008-08-16 05:18
Hi Khalid

See if Business Event debugging scripts in this link help you

apps2fusion.com/at/gt/tc/44-bpel-soa/328-workflow-mailer-debugging-script-for-debugging-emails-issues

Thanks,
Anil Passi
Quote
0 #14 narinder 2009-04-08 01:37
I have an custome event defined which has a subscription and i raise the event and call my rule function but the package is being called once in a while and i have no idea why it is not being executed.
Quote
0 #15 sandeep 2014-06-03 13:34
Hi Anil,
Thanks for an insightful information you share on internet.

I have a requirement. Whenever an order is booked manually or through API. I want to raise my custom event.Can you please tell me how can we achieve this.

Thanks
Sandeep.G
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jul 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
     1  2  3  4
  5  6  7  8  91011
12131415161718
19202122232425
262728293031 

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner