Invoking Web Service from Oracle Workflow process

Introduction

Integrated SOA Gateway (ISG) in Oracle EBusiness Suite 12.1 provides Service Invocation Framework (SIF) to natively consume and invoke web services. SIF uses Oracle Workflow Business Event System to provide necessary infrastructure to consume an abstract Web Service and dynamically invoke the Web Service without requiring any design-time code generation. In this blog post we will see a specific use case of how to invoke web services from an Oracle Workflow process that is designed using Oracle Workflow Builder and executed in the database by the Oracle Workflow Engine.

SIF setup

Please refer to 12.1 ISG Developer Guider topic "Defining Web Service Invocation Metadata" under chapter "Working With Oracle Workflow Business Event System to Invoke Web Services" for detailed information on creating Business Event System meta-data. A summary of the steps are given below.


  1. Create an Invoker Business Event.
  2. Create a Local Subscription of Action Type "Invoke Web Services" to the invoker event.
  3. Create an Error subscription to the invoker event to handle any errors during invocation is also required.

Once the above Business Event System meta-data is created, raise the invoker Business Event to invoke the web service consumed within the Local event subscription. A Business Event can be raised either using a PLSQL API or a Java API. Hence, it can be assumed that SIF can be used to invoke web services from PLSQL or Java.

The PLSQL API is WF_EVENT.Raise and the Java API is oracle.apps.fnd.wf.bes.BusinessEvent.raise. SIF run-time code executes only on Java tier (OACORE or Concurrent Tier). Raising the invoker event using Java API that already runs on middle tier causes the web service to be invoked synchronously and the response available to the caller immediately. Whereas if the invoker event is raised in PLSQL, the web service is invoked asynchronously by Workflow Java Deferred Agent Listener in Concurrent Tier. To receive the web service response back in the database in asynchronous mode following set up is required.


  1. Create a callback business event and set it as part of the "Invoke Web Services" Local subscription.
  2. Specify a callback agent such as WF_JMS_IN to which the callback event can be enqueued. You can create and use any workflow agent to receive the response.
  3. Create an External subscription to the callback event to handle the web service response from WF_JMS_IN.

After successful invocation of the web service, the Java Deferred Agent Listener sets the response to callback event's payload and enqueues to the callback agent. Running event listener on the callback agent executes the External subscription to perform the specified action. With above setup we are now ready to invoke a web service by raising the invoker business event. The invocation can be tested using business event test page.

Invoking from Workflow Process

Workflow processes include standard event activities such as Raise event and Receive event. Since Workflow processes execute within the database we will use the PLSQL message pattern discussed above to invoke the web service from workflow processes. Following steps are required to successfully invoke web service from workflow process using SIF.

Create Raise Event activity


Use Raise Event activity to raise the invoker event. A function activity can be used to create input message required for the web service and set to the invoker event payload.

Create a Receive Event activity


Use a Receive Event activity to receive the callback event in the workflow process. Callback event is raised after successful invocation of web service by Java Deferred Agent Listener.

Create an External Subscription to the callback event


Create an External subscription to the callback event with Workflow Type and Workflow Process set to the workflow process waiting to receive the web service response. The External subscription can also be used to simply execute a rule function to process the web service response.

Code Sample

Above use case is explained using a sample Workflow Process with required Business Event System meta-data. You may download the sample code for testing.

Files Uploaded



  1. WFINVTSTE.wfx - Invoker Event and Callback (Receive) Event

  2. WFINVTSTS.wfx - Local "Invoke Web Service" Subscription to Invoker Event, Error Subscription to Invoker Event, External Subscription to Callback Event

  3. WFINVTST.wft - The Workflow process that raises the Invoker Event and Receives the Callback Event

  4. WFINVTSTS.pls - PLSQL spec for function activities used in above Workflow Process

  5. WFINVTSTB.pls - PLSQL body for function activities used in above Workflow Process

Instructions to use sample



  1. Upload *.wfx files using Workflow Business Event System loader program oracle.apps.fnd.wf.WFXLoad
  2. Upload *.wft file using Workflow Definitions Loader $FND_TOP/bin/WFLOAD
  3. Compile *.pls files to APPS schema
  4. Verify Workflow Java Deferred Agent Listener is up and running
  5. Launch Workflow using LAUNCH.sql script. The workflow process sends a notification with the web service response to a specified recipient after successful invocation of web service.
  6. Wait until Workflow Java Deferred Agent Listener completes execution of the "Invoke Web Service" subscription and enqueues response to WF_JMS_IN agent.
  7. Configure an Agent Listener to run against WF_JMS_IN or another appropriate Callback Agent that you want to use to listen to callback events. An easy way to run listener is exececute PLSQL API WF_EVENT.Listen from sqlplus connected to APPS schema.

You may also launch the Workflow Process from Developer Studio and monitor the Workflow Process from Status Monitor functions available from Workflow Administrator Web Applications responsibility.

Comments:

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.

Posted by Lucile Redfox on January 12, 2010 at 04:54 PM IST #

We need to approve/reject the oracle EBS notification using smart phone.
We are able to publish web services of oracle EBS using PL/SQl API for workflow notification.
(when we typed in web browser http://frsdev.moe.gov.sa:8007/webservices/SOAProvider/?testquery
The output is -------------> TEST RESPONSE)
Do we need to use the same API to approve/reject the workflow notification in APEX 4.2?
( Once we are successful , we will use it for smartfone.)
What is the exact steps to achieve this solution.

Posted by guest on January 18, 2013 at 02:52 AM IST #

We need to approve/reject the oracle EBS notification using smart phone.
We are able to publish web services of oracle EBS using PL/SQl API for workflow notification.
(when we typed in web browser http://frsdev.moe.gov.sa:8007/webservices/SOAProvider/?testquery
The output is -------------> TEST RESPONSE)
Do we need to use the same API to approve/reject the workflow notification in APEX 4.2?
( Once we are successful , we will use it for smartfone.)
What is the exact steps to achieve this solution.

Posted by guest on January 18, 2013 at 02:53 AM IST #

After you have exposed your custom PLSQL APIs in Integration Repository, you can generate and deploy them as SOAP web services. Once these services are available, you can access them from the smartphone app. Can you please elaborate on the custom PLSQL APIs you have created?

Posted by Vijay Shanmugam on October 05, 2013 at 12:27 AM IST #

When a BE invoke a webservice, what header parameters should provide? Do you have a example?

Posted by guest on August 06, 2014 at 09:18 PM IST #

How to Invoking Web Service from PL/SQL(Via Business Event)? DO you have an example?

Posted by guest on August 06, 2014 at 09:21 PM IST #

Have you reviewed the sample at http://blogs.oracle.com/ebusinesssuiteintegration/resource/INVDEMO.zip?

Invoking a web service from BE in PLSQL is as simple as raising that business event using WF_EVENT.Raise. The flow is,

SETUP

Invoker Event and Subscription...
1) Create business event (or use an existing one)
2) Create a Local subscription with Action Type = Invoke Web Service
3) Create an Error subscription to notify SYSADMIN in case of errors

Callback Event and Subscription...
1) Create a business event to callback response from above web service
2) Create an "External" subscription since the response is coming from an external system
3) Implement a rule function for this External subscription to process the web service response

RUNTIME

1) Raise the event using API WF_EVENT.Raise
2) Pass following event parameters to ensure response processing is done
WFBES_CALLBACK_EVENT=<callback event>
WFBES_CALLBACK_AGENT=WF_WS_JMS_IN
2) Invoker event is enqueued to WF_JAVA_DEFERRED agent
3) Java Deferred Agent Listener processes event and invokes the web service
4) The response is again included into the response event and enqueued back into the Inbound Agent WF_WS_JMS_IN.
5) Make sure agent listener is running for WF_WS_JMS_IN agent
6) Response payload is processed by Rule function to the External subscription and save to EBS tables.

All the required steps are explained in ISG Dev Guide.

Posted by Vijay Shanmugam on August 06, 2014 at 10:17 PM IST #

Thanks for your response.
How to provide array list in input parameters part when using BE test?
We are copied the soupUI <soapenv:Body></soapenv:Body> part and updated as following:
<v1:CimsResultReturnService xmlns:v1="http://www.midea.com/cims/CimsResultReturnService/v1">
<v1:resultReturn>
<v1:request_id>GERP</v1:request_id>
<v1:data_source>TEST</v1:data_source>
<v1:source_record>
<v1:source_record_item>
<v1:source_id1>1</v1:source_id1>
<v1:source_id2>2</v1:source_id2>
<v1:source_id3>3</v1:source_id3>
<v1:status>y</v1:status>
<v1:error_code>99</v1:error_code>
<v1:error_message>999</v1:error_message>
<v1:target_id1>1</v1:target_id1>
<v1:target_id2>2</v1:target_id2>
<v1:target_id3>3</v1:target_id3>
</v1:source_record_item>
</v1:source_record>
</v1:resultReturn>
</v1:CimsResultReturnService>

But we get following error:

[357]:ERROR:[fnd.wf.bes.WebServiceInvokerSubscription.invokeWebService]:WSDL operation resultReturnoutput does not have any parts defined.
[359]:ERROR:[fnd.wf.bes.WebServiceInvokerSubscription.onBusinessEvent()]:Exception when invoking web service - oracle.apps.fnd.wf.bes.InvokerException: WSDL operation resultReturnoutput does not have any parts defined.

Posted by guest on August 08, 2014 at 10:01 AM IST #

Thanks for your response.
How to provide array list in input parameters part when using BE test?
We are copied the soupUI <soapenv:Body></soapenv:Body> part and updated as following:
<v1:CimsResultReturnService xmlns:v1="http://www.midea.com/cims/CimsResultReturnService/v1">
<v1:resultReturn>
<v1:request_id>GERP</v1:request_id>
<v1:data_source>TEST</v1:data_source>
<v1:source_record>
<v1:source_record_item>
<v1:source_id1>1</v1:source_id1>
<v1:source_id2>2</v1:source_id2>
<v1:source_id3>3</v1:source_id3>
<v1:status>y</v1:status>
<v1:error_code>99</v1:error_code>
<v1:error_message>999</v1:error_message>
<v1:target_id1>1</v1:target_id1>
<v1:target_id2>2</v1:target_id2>
<v1:target_id3>3</v1:target_id3>
</v1:source_record_item>
</v1:source_record>
</v1:resultReturn>
</v1:CimsResultReturnService>

But we get following error:

[357]:ERROR:[fnd.wf.bes.WebServiceInvokerSubscription.invokeWebService]:WSDL operation resultReturnoutput does not have any parts defined.
[359]:ERROR:[fnd.wf.bes.WebServiceInvokerSubscription.onBusinessEvent()]:Exception when invoking web service - oracle.apps.fnd.wf.bes.InvokerException: WSDL operation resultReturnoutput does not have any parts defined.

Posted by guest on August 08, 2014 at 10:04 AM IST #

Hi Vijay,
Thanks for the post and pretty useful information. We have this requirement and hope you could help answer some questions.

Requirement: Whenever a contract purchase agreement is created or updated in the external system, the information need to be interfaced into Oracle EBS as new or update to P2P Purchase agreement.

Questions:
1. Which method is best interms of best practices to interface between Oracle EBS Integrated SOA gateway and external systems - business events, BPEL composite web service (does it require a separate license to implement BPEL WS), expose java or plsql or concurrent program as web services and have external system make a call etc?
2. This is inbound interface to Oracle EBS. How can external system notify Oracle EBS that a new / update transaction is ready for processing - need guidance as best practices to implement - should we have a temporary message store in between systems like queue or topic so as to loosely couple 2 systems?
3. Are both inbound and outbound business events use Oracle queueing to ensure that messages do not get lost - say after system goes down for patching or network is down or something

Posted by guest on September 19, 2014 at 11:49 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Straight from Oracle EBS Integrated SOA Gateway and EBS Adapter Development Team.

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today