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

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

Workflow Event with Payload Part -2

 

The pre-requisite for this article is Raise Event with XML as Payload.

This article illustrates how to build a workflow that consumes XML payload of an event. Writing this specific tutorial took me a bit longer, simply because I never professionally used XML payload with workflow events. Anyway I have done by R&D now, and have got a working example to share with here. In the prior training article we had defined a Workflow Event that transmitted a XML document as PAYLOAD. In this Workflows event training article we will create a workflow that can be attached to our Event. This "attachment of Workflow to Event" happens via a Subscription.



Before we build the workflow that reads XML Payload from an event, lets do some Q & A.

 

The workflow that we build in this exercise, will this workflow be attached as a subscription to event xx.ap.bank.accountupdate?

Correct. In the event definition, subscription action type will be Launch Workflow. And also, the Workflow ItemType and Process will be attached to the Subscription.

Note:- Ensure that  the phase is 99 [and not 100]. Phase between 1 & 99 means that workflow will kick-off as soon as Event is Raised.

 

 

What should the first step in workflow process be?

It should be something that receives the event, and not only that, the first step should also read the Payload(xml) information as well.



Do I have to write my own code to read the XML payload in Workflow?

Not really, we will create a workflow step of type event[ screenshot and code will follow latter in this article]



How will Workflow read the contents of XML Payload?

Firstly, we will define a WF Attribute [of type Event] into which the xml payload will be read. The new workflow step [as defined in previous step] will then be attached to this attribute. Note:- we need to define some other attributes too which will capture e.g. Event key.



What are the steps to achieve the above steps in practicality, with screenshots?

Here are the steps for defining the Workflow. The wft file can also be downloaded[I will provide a link]

STEP 1. Open workflow builder and open WFSTD.wft

 

STEP 2. Rightclick to create a new ItemType

Create a new Item Type as Name:-XTSTPYLD, Display Name:-XX Test Payload

 

STEP 3. Do file/save as and save this workflow as XTSTPYLD.wft

 

STEP 4. Inside "XX Test Payload" right click on Process.

Create a new process with name XX_RECEIVE_EVENT

 

STEP 5. Create three attributes as below [you will find these from wft file provided]:-

   a. Attribute Named "BANK_ACC_CHANGED_EVENT" of type Text

   b. Attribute Named "BANK_ACC_CHANGED_EVENT_KEY" of type Text

  --> Note:- In this attribute we will capture the Event Key[remember we generate event key using DB Sequence "ap_payment_event_s.NEXTVAL"- as in part 1 of this article]

   c. Attribute Named "BANK_ACC_CHANGED_PAYLOAD" of type Event

  --> Note:-In this attribute we will capture the Event Payload [remember the XML we build in Part 1 of this article]

   d. Create an attribute of type text, and name it BANK_ACCT_NUMBER_FROM_PAYLOAD

This is the attribute into which the bank account number will be stored[after extracting its value from XML Payload].

Hence, this exercise will do following steps:-

a. Read the value of Bank Account Number from Payload.

b. Assign Bank Account Number to WF Attribute BANK_ACCT_NUMBER_FROM_PAYLOAD

 

STEP 6. Double click on this process. Inside the process, right-click and select "New Event"

 

 

STEP 7. Inside the event property window, give this event any name say "RECEIVE_BANK_EVENT", with Event Filter="xx.ap.bank.accountupdate"

Figure Define Event

 

Click on Node tab, and assign Start in field labelled Start/End.

Click on Tab "Event Details", and assign the values as below

Figure Define Event Details

What is the significance of above setup?

Well, we have just defined an WF Step of type Event for xx.ap.bank.accountupdate.

The workflow that we are building here will be attached to the Business Event as a Subscription.

a. We will be attaching Workflow XTSTPYLD & its process XX_RECEIVE_EVENT as a subscription to the bank account change event

b. When this event gets raised, Oracle will kick-off this workflow process.

c. The event filter specified in Article Part1 during Event Creation will get matched against the Event Filter

d. Once the Event Filter has been matched, Oracle will assign the Event Name, Event Key[from DB Sequence] and our XML Payload to three attributes we have defined.

This happens because in Event Details tab, we attach these attributes to the receiving Event [see the above figure].

 

STEP 8.Sole purpose of Workflow exercise is to fetch the value of BANK_ACCOUNT_NUMBER from the XML Payload.

Now, we know how  the event and its payload are read by the workflow. However, how will read the bank_account_number field from the XML payload?

Well. there are various ways we can parse the XML text. In this example I will be using XPATH, to parse the value of a XML node, using PL/SQL.

 

Our XML Payload has been loaded into Attribute named BANK_ACC_CHANGED_PAYLOAD.

Lets write pl/sql function xx_get_bank_account_number as below

CREATE OR REPLACE PROCEDURE xx_get_bank_account_number(itemtype  IN VARCHAR2

                                                      ,itemkey   IN VARCHAR2

                                                      ,actid     IN NUMBER

                                                      ,funcmode  IN VARCHAR2

                                                      ,resultout OUT NOCOPY VARCHAR2) IS

  bank_event_document   CLOB;

  event                 wf_event_t;

  xmldoc                xmldom.domdocument;

  parser                xmlparser.parser;

  v_bank_account_number VARCHAR2(500);

BEGIN

  IF (funcmode = 'RUN')

  THEN

    --

    event                 := wf_engine.getitemattrevent(itemtype => itemtype

                                                       ,itemkey  => itemkey

                                                       ,NAME     => 'BANK_ACC_CHANGED_PAYLOAD');

    bank_event_document   := event.geteventdata();

    v_bank_account_number := irc_xml_util.valueof(bank_event_document

                                                 ,'/ap_bank_accounts/bank_account_number');

    wf_engine.setitemattrtext(itemtype => itemtype

                             ,itemkey  => itemkey

                             ,aname    => 'BANK_ACCT_NUMBER_FROM_PAYLOAD'

                             ,avalue   => v_bank_account_number);

  END IF;

END xx_get_bank_account_number;

 

Step 9. Create a WF Function named/displayname FETCH_BANK_ACCOUNT_NUMBER and its pl/sql function will be xx_get_bank_account_number

 

 

Step 10. Complete the workflow and save it to database, as below by creating End Activity.

When testing, we will raise this event for a Bank Account Number, and will check in Workflow Status Monitor whether BANK_ACCOUNT_NUM is being extracted into our attribute.

Save this workflow into the Database now.



Source code of this Workflow WFT File?

The Workflow file wft for this exercise can be downloaded from this link here

You may right click and download this.



How do we test this workflow?

Follow the Part 3 of this article to see how this can be tested and also the results of this testing.



Before you test on your environment, follow the checklist

A. Have you created the trigger to raise event? See link

B. Have you defined the event

C. Have you saved the wft into the database?

D. Have you attached the workflow as event subscription?

E. Have you defined pl/sql

 

If you have done all the above, then we are ready to test and see this working...next part.


Anil Passi

Comments   

+1 #1 Shyam 2007-09-27 08:36
Hi Anil,

U have done a great job. It was easy for me to create and subcribe for the event using this guide.

My requirement is to invoke an ESB from the event which routes the event data to BPEL.
I m using a Java rule function for the subscription,
" oracle.apps.fnd .wf.bes.WebServ iceInvokerSubsc ription(SERVICE _WSDL_URL,SERVI CE_NAME,SERVICE _PORTTYPE,SERVI CE_OPERATION,SE RVICE_PORT)"
Th e event is getting raised but it is not invoking the ESB. I dont know how to trace the flow of the event and y it is not invoking ESB.


Also, I m sending an XML content as event data. The ESB is routing a schema element of type string to BPEL.Now the XML content from event should be mapped to the schema element of ESB. I dont know how to do it.

Can u pls guide me.

Thanks,
Sh yam
Quote
0 #2 koppella 2011-03-07 14:50
Hi Anil,

I have followed all steps mentioned in the training but my workflow was not initiated (I have put execution phase as 99). I can see data in table applsys.aq$wf_d eferred but workflow is not initiated. I have tried registering your training material (workflow, procedure and trigger) even it’s not working. Can you help me?

Thanks
Kop pella
Quote
0 #3 tapra 2011-08-10 18:28
Hi Anil, Thanks for great article.
Can you answer to my scenario below:

1. We want to notify department owner on payment of his dept invoices.
2. For this I want to use seeded business event ORACLE.APPS.AP. PROCESSPAYMENT. CONFIRM with a custom subscription to invoke a function.
3. I've configured it and also could add even parameters.
4. When I've performed the event in oracle, data is not populated.
5. When I queried in applsys.aq$wf_d eferred, it shows msg_state as processsed.
6. I'm sending screenshots also here with.
Could you let me know when it went wrong?
Quote
0 #4 tapra 2011-08-10 18:30
1.Selected Business event: ORACLE.APPS.AP. PROCESSPAYMENT. CONFIRM

2.Added a new Local Subscription:


3.Subscriptio n Details:

4.Action & Parameters:

5.Documentatio n:

6.Custom Function:
CREAT E OR REPLACE FUNCTION ask_test_event_ 01(
p_subscription_ guid IN RAW
,p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
l_check_id number;
l_org_id number;
l_user_id number;
l_amount number;
l_check_number varchar2(100);
cursor get_check_detai ls (p_check_id in number) is
select amount,check_nu mber
from ap_checks_all
where check_id = p_check_id;
BEG IN
--read the parameters values passed to this event
l_check_id := p_event.getvalu eforparameter(' CHECK_ID');
l_org_id := p_event.getvalu eforparameter(' ORG_ID');
l_user_id := p_event.getvalu eforparameter(' USER_ID');
open get_check_detai ls (l_check_id);
fetch get_check_detai ls into
l_amount,
l_check_number;
close get_check_detai ls;
INSERT INTO ask_event_resul t
(check_id
,check_number
,amount)
VALUES
(l_check_id
,l_check_number
,l_amount);
COMMIT;
RETURN 'SUCCESS';
END ask_test_event_ 01;

7.Created a Quick payment and expecting to get the custom table inserted with values. But no values inserted. Payment batch was successful.
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