<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow Oracle Workflows arrow Oracle Workflows Events with Payload-Part 1
This website has now moved to http://apps2fusion.com





Oracle Workflows Events with Payload-Part 1 | Print |  E-mail
Written by Anil Passi   
Monday, 15 January 2007
In our earlier "workflow events" training, we saw an example to raise an event and consume that via a pl/sql function. I hope you have read Oracle Workflows Event Basics Article[linked here]
When an event is raised, it must provide subscriber with enough details, by means of parameters.  In the previous event article, we passed individual parameters. That works well if you have a small number of parameters. But what if you wish to pass a complete purchase order with its lines or aPayables Invoice or a Customer Record details?
Obviously you will not be defining 100s of parameters for the event, hence you will pass a payload, i.e. a XML document as a parameter

 

The purpose of this article is :-
1. To create an event

2. Pass this event an XML Payload [sample to show how this works]

In the next article, a workflow will be attached to this event, and that workflow will read the Payload.



How will the event be raised in this example?
Lets say, whenever a Bank Account record gets modified, we want an event to be raised. Hence, we can write a Database Trigger on AP_BANK_ACCOUNTS_ALL [before update for each row]. The event will be raised inside the database trigger.

 

What is a XML payload?
In simple words, it is a mechanism of passing parameters to the event, when raising an event.

 

But why is this payload of type XML?
Events may be raised by external systems. XML is an industry standard for transferring data between different systems. Hence payload is nothing but parameters presented in XML format. Hence all future references of Payload imply parameters in XML format.

 

NOTE:- For simplicity, we will split this article into two parts
Part 1- Preparing the Event & its payload and then raising the event [by passing payload as parameter]
Part-2 Subscribing a workflow to the event, and reading the payload and taking further action.

 

 

Business example:-
Lets say the Bank account details of your supplier has changed, and you wish to notify your payables department by notification.

We will pass the following parameters to event
A. Bank Branch Id
B. Bank Account Number
C. Changed By User ID


Steps  in brief
Step 1. Create an event
Step 2. Prepare the Payload inside the trigger and raise the event



Steps in detail
Step1
. Navigate to responsibility "Workflow Administrator Web Applications" and click on menu "Business Events"

Create an event with details as below by clicking on button "Create Event"
a. Event Name and Display Name:- xx.ap.bank.accountupdate
b
. Owner name & Tag :- SYSADMIN


Step 2. Prepare the payload using a pl/sql function as below.
Please note, a datatype of clob will be used. We will pass the required parameters to this function, and that will build and return the payload.

create or replace trigger xxap_bank_accounts_bru1
BEFORE UPDATE
ON ap.ap_bank_accounts_all
FOR EACH ROW
DECLARE
  l_event_key number;
  l_event_data clob;
  l_event_name varchar2(250);
  l_text varchar2(2000);
  l_message varchar2(10);
BEGIN
  SELECT ap_payment_event_s.NEXTVAL INTO l_event_key FROM dual;
  l_event_name := 'xx.ap.bank.accountupdate';
  l_message    := wf_event.test(l_event_name);
  dbms_lob.createtemporary(l_event_data
                          ,FALSE
                          ,dbms_lob.CALL);
  l_text := '<?xml version =''1.0'' encoding =''ASCII''?>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);
  l_text := '<ap_bank_accounts>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);
  ------------------------------------
  l_text := '<bank_branch_id>';
  l_text := l_text || fnd_number.number_to_canonical(:new.bank_branch_id);
  l_text := l_text || '</bank_branch_id>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);
  ------------------------------------
  l_text := '<bank_account_number>';
  l_text := l_text || :new.bank_account_num;
  l_text := l_text || '</bank_account_number>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);
  ------------------------------------
  l_text := '<changed_by_user_id>';
  l_text := l_text ||
            fnd_number.number_to_canonical(:new.last_updated_by);
  l_text := l_text || '</changed_by_user_id>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);
  ------------------------------------
  l_text := '</ap_bank_accounts>';
  dbms_lob.writeappend(l_event_data
                      ,length(l_text)
                      ,l_text);

  -- raise the event with the event with Bank Account Payload
  wf_event.RAISE(p_event_name => l_event_name
                ,p_event_key  => l_event_key
                ,p_event_data => l_event_data);
END;



Note some notes:-

1. As soon as a Bank Account record gets modified, we are then raising this event.
2. We are creating the Event Key from sequence ap_payment_event_s
3. When creating subscriptions, you can specify whether it will read the Event Key or the entire Payload.
If all your subscriptions have Rule Data set to "Key" as shown below, then you do not need to pass the payload.
But in this example, we will be setting our Rule Data to Message, as we desire our subscription to read entire XML payload.

 

4. We are using dbms_lob.writeappend to concatenate the text to clob field l_event_data.

 

In the next article, we will attach a workflow to this event, and the PAYLOAD will be read within the workflow.

Comments (5)add
...
written by Ramakrishna , January 18, 2007
Hi
Thanks a lot. And i am waiting for your Updates.
...
written by Ramakrishna , January 18, 2007
Hi
Thanks a lot. And i am waiting for your Updates.
...
written by uday , February 03, 2007
hi anil,

How are you? Thanks for you to guide us.I want to learn workflow.I dont have the workflow buider to install in my pc. How can I get that one? Is there any free downloads for workflow buider tools? what is the installation process? please mailto me.

thanking you,

regards,
uday.k
...
written by Anil Passi , February 03, 2007
Hi Uday,

I am very well, thanks for asking.
After doing some google search I have managed to find the link for free download of Oracle Workflows.

Here it is for you
http://www.oracle.com/technology/software/products/workflow/index.html

Good luck
Anil
...
written by Muralidhar , September 21, 2007
I am workig on Billing and Receiopt History Report. I need to raise an On Account Credit Transaction in AR. Can you please let me know whats the navigation.I am stuck here.

Thanx
Murali
You must be logged in to a comment. Please register if you do not have an account yet.

busy