Monday, January 2, 2012

Using SOA Gateway in EBS 12

A lot of organizations use a packaged application for their supporting processes, like Human Resources and Finances. Often you need to integrate with these systems. There are several use cases for this:
  • Use data from the HR system to determine hierarchy;
  • Use functionality of an ERP module in another process;
  • Expose services for self service purposes.
Oracle E-Business suite has built-in support for these types of integration. In this post we will look at an example of the third use case: a self service application for expense reports, that stores the result (a pay element) in the E-Business Suite.
There is a lot of documentation available, so far I have used:
  1. Oracle® Integration Repository User's Guide
  2. Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide
  3. Oracle® E-Business Suite Integrated SOA Gateway User's Guide
  4. Integrated SOA Gateway Implementation Guide
  5. Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide
  6. Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide
This can make it rather overwhelming (to put it mildly ;) ) This blog gives an overview of important steps and the documents that contain information about these steps.

Like any other feature in the Oracle EBS, you need to have the right privileges to use the SOA Gateway.

Therefore, you have to create a user that has the correct permissions to generate and deploy a web service. The following table can be found in theOracle® E-Business Suite Integrated SOA Gateway User's Guide:

System Integration Analyst
System Integration Developer
Integration Repository Administrator
View public interfacesYesYesYes
View private/internal interfacesNoYesYes
Generate/regenerate Web Services (WSDL)NoNoYes
Deploy/redeploy Web ServicesNoNoYes
Undeploy Web ServicesNoNoYes
Subscribe to business eventsNoNoYes
Create GrantsNoNoYes
Download Composite servicesNo (configurable)YesYes

In my experience it is easiest to just use the Integration Repository Administrator role in the development environment. If you want to limit the number of people that can actually create the web services you can do that in the testing, staging and production environment.

In the Integrated SOA Gateway Implementation Guide it is explained in "Setting up Oracle E-Business Suite Integrated SOA Gateway" how you can set up these roles.

Generate the WSDL
To generate the WSDL, navigate to the PL/SQL API in the Integration Repository.  In the project we generated a WSDL for PAY_ELEMENT_ENTRY_API, the function CREATE_ELEMENT.

Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide describes in "Using PL/SQL as Web services" how you can accomplish this. In the screen shot below, you see what it looks like for the HR_EMPLOYEE_API for the function CREATE_EMPLOYEE.  (NB: I use the HR_EMPLOYEE_API example in the screen shots, because in our environment we already generated the WSDL for the PAY_ELEMENT_ENTRY, so the screens look different).

Now you can view the WSDL in your browser, by selecting: "View WSDL".  The Web Service status is added and is set to "Generated"

Deploy the WSDL
Once you have generated the WSDL, you need to actually deploy the webservice. This will install the required files on the Oracle Application Server. 
In this example we use username tokens as authentication mechanism, but SAML tokens are also supported. 

Grant Access 
Now you have to grant access to the webservice by using the "Create Grant" option. The best way to handle this, is to create one user for example "selfsvc" that has access to the webservices on behalf of the users that use the self service application. 

Call the Web Service
SQLJUTL is used when generating the web services from PL/SQL. This is the same technology that is used by the Oracle Database adapter and Eclipselink DBWS.  This requires some specific knowlegde. For example a PL/SQL boolean is generated as an integer:
<element name="P_VALIDATE" type="int" db:index="1" db:type="INTEGER" minOccurs="0" nillable="true" /> 
Documentation about the mapping form PL/SQL to XML can be found in  Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide: "Acceptable values for the original BOOLEAN parameter now that it is an INTEGER are 1 for true and 0 for false. Any value other than 1 is considered false. The generated wrapper procedure uses APIs from the SYS.SQLJUTL package to convert from INTEGER to BOOLEAN and vice-versa."

Calling the webservice requires both knowledge of the implementation of the EBS module and knowledge of XML and Web Services. For example, it expects an assignment_id and an element_link_id as a parameter, it uses date-tracking and has the option to validate the call, instead of actually executing (committing) the data. 
Information about this can be found in the "Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide". This guide explains for example the assignment concept: 
"The assignment is the central concept that relates employees to the structures in which they work, and the compensation and benefits for which they are eligible.
In Oracle HRMS, many of the activities you undertake in human resource management, such as vacancy management and budget planning, are based around assignments and not people. In particular, you enter all earnings, deductions, and other pay-related elements for the employee assignment, rather than the employee. This makes it possible to give an employee two or more assignments when this is necessary.
For example, if an employee has more than one role within your enterprise, he or she can have a separate assignment for each role."

You can test the web service using SOAPUI, or the standard test client on the Oracle Application server.
To test it with SOAP UI, you need to import the WSDL. You can find the address of the WSDL when you click on "View WSDL". Just copy the url in your browser into SOAP UI. It will look something like this:

To test the web service in your browser, put the link in your browser, without the '?wsdl'

This will give you a webpage to test the service with:

We needed to install a number of patches before we were able to successfully use the SOA Gateway, but once the whole thing is setup (patches, users privileges etc), creating the web service is trivial. A very important benefit is that it is visible in the integration repository that a Web Service is generated, based on the PL/SQL API. This is much harder to keep track of when you use a Database adapter, Eclipselink DBWS or the Oracle Apps adapter. 
Knowledge of the PL/SQL APIs in EBS is a must in your project. In our project we worked closely with the EBS functional and technical people to figure out how to use the PL/SQL APIs correctly and what APIs we needed. The biggest challenge in this type of project is to combine knowledge of EBS and PL/SQL with knowledge of Web Services and integration.  
What is seriously lacking in the PL/SQL APIs are simple 'read functions'. So if the first use case applies (using data from HR), you need to write your own APIs or call the database tables directly. The latter is a bad idea in my opinion. This mean that you always have to write some APIs yourself when interfacing with EBS.  In the Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide you can find a description how you can add these custom interfaces to the repository and generate WSDLs from them in chapter "Creating and using custom integration interfaces".

1 comment: