Oracle EBS Service Invocation Framework (Business Events – SIF)
In this tutorial, I am going to invoke an external test web service with SIF.
SIF mechanism is based on events, therefore we are going to define business events and subscriptions. After that, we can raise events in pl/sql.
You can find detailed information in the following link: https://docs.oracle.com/cd/E18727_01/doc.121/e12169/T511175T513090.htm
We need to define business events for our request and response.
Request Business Event
1. Login to E-Business Suite. Select “Business Events” function under “Workflow Administrator Web (New) -> Administrator Workflo” menu.
2. Click to “Create Event” button.
3. Fill the event information and click to “Apply” button.
4. Search for your event and click to “Subscription” icon.
5. Click to “Create Subscription” button.
6. Fill the subscription information and click to “Next” button. We are going to use this event subscription for invoking web service. “Phase” parameter specifies whether the invoke process is going to be synchronised or not. However, as far as I know, this parameter is only working for the events which are raised in java. In this example, we are going to raise event in pl/sql, therefore it is going to be queued in wf_java_deferred table. Data in message queue table is processed by an agent listener periodically. “Rule” parameter should be “Message” since we are going to send a request message to external service.
7. Type WSDL url for the external web service and click to “Next” button. SIF is going to parse WSDL and display available services, ports, operations in web service.
8. Select “Service” and click to “Next” button.
9. Select “Port” and click to “Next” button.
10. Select “Operation” and click to “Next” button.
11. Fill “Owner Name” and “Owner Tag” for your subscription. “Java Rule Function” specifies invoking java function. You can extend this class and write your own invoker if you need. Click to “Apply” button.
12. We need to create a second subscription in request event. This is need for request errors and it is going to launch WFERROR workflow which will send a notification about error details to the SYSADMIN. Click to the “Create Subscription” button.
13. Choose “Source Type” as external since it is going to get the error for external service. Choose “Rule Data” as “Key”. Click to the “Next” button.
14. Choose “Workflow Type” as “WFERROR” and “Workflow Process” as “DEFAULT_EVENT_ERROR2”. Type owners and click to the “Apply” button.
Final view for request event:
Response Business Event
1. We are going to create a second event for service response. This event will capture service response message. Click to the “Create Event” button.
2. Fill the event information and click to the “Apply” button.
3. Search for event and click to “Subscription” icon.
4. Click to “Create Subscription” button.
5. Choose “Rule Data” as message and this action time will be “Custom”. Click to the “Next” button.
6. You can process response in java or pl/sql. We are going to create a pl/sql function which will fetch event data. Pl/sql function should have a pre-defined skeleton. We will go into detail at next steps. Click to the “Apply” button.
At this point, defining events and subscription process is completed.
EBS Service Agents
Make sure that following agents are up and running properly.
- Web Services IN Agent
- Web Services OUT Agent
- Workflow Java Deferred Agent Listener (This is for JMS queue. Messages are going to be fetched from WF_JAVA_DEFERRED table)
TABLE: xxanil_service_response
We are going to use this table for insering response of web service.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
drop table xxanil_service_response; create table xxanil_service_response ( id number primary key, event_key varchar2(80), event_name varchar2(80), response clob, creation_date date ); create sequence xxanil_service_response_s start with 1; |
PROCEDURE: get_service_response
We are going to use this procedure for dml.
|
1 2 3 4 5 6 7 |
create or replace procedure insert_service_reponse(p_response in clob, p_event_key in varchar2, p_event_name in varchar2) is begin insert into xxanil_service_response values (xxanil_service_response_s.nextval, p_event_key, p_Event_name, p_response, sysdate); commit; end; |
FUNCTION: get_service_response
Pay attention to the skeleton of function. It must take two parameters subscription_guid in raw type and event for in out nocopy wf_event_t type. We are going to specify response event name before raising request event. After listeners finished their jobs, this function will be triggered by response event with event data. We can fetch event key, event name, paramaters and event data from object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
create or replace function get_service_response ( p_subscription_guid in raw, p_event in out nocopy wf_event_t ) return varchar2 is l_node varchar2(30); l_version integer; l_from varchar2(2000); l_eventName varchar2(80); l_eventkey varchar2(80); l_paramlist wf_parameter_list_t; l_eventData clob; l_messageHandle varchar2(100); begin l_eventkey := p_event.GetEventKey(); l_eventName := p_event.getEventName(); l_paramList := p_event.getParameterList(); l_eventData := p_event.getEventData(); insert_service_reponse (l_eventData, l_eventKey, l_eventName); return 'SUCCESS'; end get_service_response; |
Create password key for secure services
If external service has security, then we need to pass wsse parameters in header of the request. SIF does not allow us to modify wsse header in code. We need to create a password key (not password) in database. To do this, we are going to give an application, key name and password.
Execute following script with your own credentials:
|
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 |
declare sysResp number; appResp number; l_module varchar2(30) := 'SQLAP'; --MODULE l_key varchar2(30) := 'ANIL_KEY'; --KEY NAME l_value varchar2(500) := '111111'; --PASSWORD begin -- get SYSTEM_ADMINISTRATOR resp and app ids for global init .. begin select RESPONSIBILITY_ID, APPLICATION_ID into sysResp, appResp from FND_RESPONSIBILITY where RESPONSIBILITY_KEY = 'SYSTEM_ADMINISTRATOR'; fnd_global.apps_initialize( 0, sysResp, appResp); exception when others then -- fail back to seeded values fnd_global.apps_initialize(0,20420,1); end; fnd_vault.put(l_module, l_key, l_value); end; / commit; |
Raising the request event
We are going to raise request event in pl/sql with wf_event.raise api. We need to specify some neccessary parameters.
Important Note: If your body and header xml requests using namespaces, you must move them to body and header in a proper manner.
Variables
- l_parameters: wf_parameter_list_t table type parameter. We are going to add parameters in this variable with another API.
- l_request_body: request body xml
- l_request_header request header xml
- l_event_key: raise key
|
1 2 3 4 5 6 |
l_parameters wf_parameter_list_t := wf_parameter_list_t (); l_request_body clob; l_request_header clob; l_event_key varchar2 (50) := 'ANIL-TEST-5'; |
Adding Parameters
| Parameter Name | Description |
|---|---|
| WFBES_SOAP_USERNAME | Standard parameter for wsse (security) username. |
| WFBES_SOAP_PASSWORD_MOD | Standard parameter for our password key module. |
| WFBES_SOAP_PASSWORD_KEY | Standard parameter for our password key name. |
| WFBES_CALLBACK_EVENT | Callback event which will trigger response function. |
| WFBES_CALLBACK_AGENT | Standard parameter for your agent. We are going to use "Web Services IN Agent". |
| WFBES_INPUT_taicsheader | Standard parameter for header. |
| WFBES_INPUT_header | Standard parameter for header. |
| WFBES_INPUT_tAICSHeader | Standard parameter for header. |
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
wf_event.addparametertolist ( p_name => 'WFBES_SOAP_USERNAME', p_value => 'ANIL', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_SOAP_PASSWORD_MOD', p_value => 'SQLAP', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_SOAP_PASSWORD_KEY', p_value => 'ANIL_KEY', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_CALLBACK_EVENT', p_value => 'XX_TEST_EVENT_RESPONSE', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_CALLBACK_AGENT', p_value => 'WF_WS_JMS_IN', p_parameterlist => l_parameters ); l_request_header := '<xx:SOAHeader xmlns:xx="http://xmlns.oracle.com/apps/xx/soaprovider/plsql/xx_test_service/"> <xx:Responsibility>TR_PAYABLES_MANAGER</xx:Responsibility> <xx:RespApplication>SQLAP</xx:RespApplication> <xx:SecurityGroup>STANDARD</xx:SecurityGroup> <xx:NLSLanguage>TURKISH</xx:NLSLanguage> <xx:Org_Id/> </xx:SOAHeader>'; wf_event.addparametertolist ( p_name => 'WFBES_INPUT_taicsheader', p_value => l_request_header, p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_INPUT_header', p_value => l_request_header, p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_INPUT_tAICSHeader', p_value => l_request_header, p_parameterlist => l_parameters ); |
Adding body and raise
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
l_request_body := '<test:InputParameters xmlns:test="http://xmlns.oracle.com/apps/xx/soaprovider/plsql/xx_test_service/test_function/"> <test:PARAM>123</test:PARAM> </test:InputParameters>'; wf_event.raise (p_event_name => 'XX_TEST_EVENT_REQUEST', p_event_key => l_event_key, p_event_data => l_request_body, p_parameters => l_parameters, p_send_date => sysdate ); commit; |
Complete code:
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
declare l_parameters wf_parameter_list_t := wf_parameter_list_t (); l_request_body clob; l_request_header clob; l_event_key varchar2 (50) := 'ANIL-TEST-5'; begin wf_event.addparametertolist ( p_name => 'WFBES_SOAP_USERNAME', p_value => 'ANIL', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_SOAP_PASSWORD_MOD', p_value => 'SQLAP', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_SOAP_PASSWORD_KEY', p_value => 'ANIL_KEY', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_CALLBACK_EVENT', p_value => 'XX_TEST_EVENT_RESPONSE', p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_CALLBACK_AGENT', p_value => 'WF_WS_JMS_IN', p_parameterlist => l_parameters ); l_request_header := '<xx:SOAHeader xmlns:xx="http://xmlns.oracle.com/apps/xx/soaprovider/plsql/xx_test_service/"> <xx:Responsibility>TR_PAYABLES_MANAGER</xx:Responsibility> <xx:RespApplication>SQLAP</xx:RespApplication> <xx:SecurityGroup>STANDARD</xx:SecurityGroup> <xx:NLSLanguage>TURKISH</xx:NLSLanguage> <xx:Org_Id/> </xx:SOAHeader>'; wf_event.addparametertolist ( p_name => 'WFBES_INPUT_taicsheader', p_value => l_request_header, p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_INPUT_header', p_value => l_request_header, p_parameterlist => l_parameters ); wf_event.addparametertolist ( p_name => 'WFBES_INPUT_tAICSHeader', p_value => l_request_header, p_parameterlist => l_parameters ); l_request_body := '<test:InputParameters xmlns:test="http://xmlns.oracle.com/apps/xx/soaprovider/plsql/xx_test_service/test_function/"> <test:PARAM>123</test:PARAM> </test:InputParameters>'; wf_event.raise (p_event_name => 'XX_TEST_EVENT_REQUEST', p_event_key => l_event_key, p_event_data => l_request_body, p_parameters => l_parameters, p_send_date => sysdate ); commit; end; |
Check following link for raising event from Workflow:
https://blogs.oracle.com/ebusinesssuiteintegration/entry/invoking_web_service_from_orac_1
Check following link for raising event from OAF:
https://blogs.oracle.com/ebusinesssuiteintegration/entry/r121_-_invoking_web_service_fr
Hi Anil,
Very good in detail explanation. We have the same requirement that we are going to implement by referring this blog.
Thanks,
Pullareddy