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_num>';
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
Thanks a lot. And i am waiting for your Updates.
Thanks a lot. And i am waiting for your Updates.
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
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.
H ere it is for you
http://www.oracle.com/technology/software/products/workflow/index.html
Good luck
Anil
Thanx
Mu rali
Co uld you please let me know if the following customization is possible or not in Workflow
1. Add a button/link(url ) in standard PR Notification(Pr oject Accounting Workflow)
2. Try to trigger a report (any report) by clicking the button or clicking the link provided in step#1
3. Try to pass parameters to the report.
RSS feed for comments to this post