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)

Subscribe to this comment's feed

...
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