Oracle Workflows

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

Add comment