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
 

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.


Anil Passi

Comments   

0 #1 Ramakrishna 2007-01-18 00:00
Hi
Thanks a lot. And i am waiting for your Updates.
Quote
0 #2 Ramakrishna 2007-01-18 00:00
Hi
Thanks a lot. And i am waiting for your Updates.
Quote
0 #3 uday 2007-02-03 00:00
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
Quote
0 #4 Anil Passi 2007-02-03 00:00
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.

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

Good luck
Anil
Quote
0 #5 Muralidhar 2007-09-21 16:18
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
Mu rali
Quote
0 #6 Ram 2009-06-03 05:57
Hi Anil/Anyone,
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.
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213
14151617181920
21222324252627
282930    

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner