Friday, January 27, 2012

In a previous post I discussed how to expose seeded APIs in Oracle EBS 12 as a Web Service.

But, as you can guess, seeded APIs are not always the easiest way to access EBS. For example, the PAY_ELEMENT.CREATE_ELEMENT_ENTRY procedure expects the programmer to take care of EBS specific arguments like element_link_id.
Our self service application has logic to submit, approve and check expenses. To do that, it uses employee ids and business rules about submitting, approving and checking steps. Users can view the state, get tasks assigned to them and can save their expenses in the application. This is setup using BPM, business rules, Java and ESB technologies. This application is blissfully ignorant about element_entry_link_ids, date tracking implementations etc. On top of that, EBS does not provide "select" APIs to get data from the application. We don't want to create SQL statements in our application, we like to hide the implementation and use APIs to communicate with EBS.

The good news is, Oracle E-Business Suite 12 provides a way to make custom code part of the integrated SOA Gateway.

This way you have the best of both worlds: monitoring and control of the services with the Integration repository and usable APIs for external applications. Let's see how that works.

Overview

The following steps have to be executed to accomplish this:

Write your custom API

Test it

Annotate Custom APIs

Validate Annotated Files

Upload Validated Files

Create Security Grants

Generate Web Services

Deploy Web Services

From: Integrated SOA Gateway Developer's Guide

Writing and testing PL/SQL code is beyond the scope of this blog and the last three steps were described in the previous post, so I will skip those steps here.

Documentation and roles
There are two roles involved: a system integration developer and a system integration administrator. The 'Integrated SOA Gateway Developer's Guide' describes how the annotations should be added to the package specification in the Chapter 'Creating and Using Custom Integration Interfaces'. Appendix A 'Integration Repository Annotation Standards' describes the annotations in detail. The 'Integrated SOA Gateway Implementation Guide' describes how to validate and upload the files.

Annotate Custom APIs
A system integration developer annotates the API that needs to be loaded in the integration repository. The integration repository parses the source files to populate itself.
So to populate the integration repository with your own Custom interfaces, you only need to annotate the package specification, not the package body. The syntax is similar to JavaDoc annotations: @NameSpace:TypeName keyStringFirst of all, you need to annotate the package, with a scope, the product name, the displayname and the category. Secondly, you annotate the procedures that you want to expose as a Web Service. The annotations for the procedure should be placed between the definition and ';'.

create or replace package xxgr_soa_pay_elements is/* $Header: xxgr_soa_pay_elements $ *//*#* This custom PL/SQL package can be used to add pay elements. It hides some particulars of the PAY_ELEMENT_ENTRY_API* @rep:scope public* @rep:product PER* @rep:displayname Create Pay element entry* @rep:category BUSINESS_ENTITY CREATE_ELEMENT_ENTRY*/procedure create_element_entry( p_employee_number in number, p_element_name in varchar2, p_element_eff_start_date in varchar2, p_element_eff_end_date in varchar2, p_entry_type in number, p_input_value_name1 in varchar2, p_entry_value1 in number, p_input_value_name2 in varchar2 default null, p_entry_value2 in number default null, p_input_value_name3 in varchar2 default null, p_entry_value3 in number default null, p_date_earned in varchar2 default null, p_subpriority in number default null, p_effective_start_date out varchar2, p_effective_end_date out varchar2, p_element_entry_id out number, p_object_version_number out number, p_create_warning out number)/*#* Use this procedure to create a single entry for an expense. NB* this procedure needs to be optimized to use an array for the flex fields.* Date formats are dd-MM-YYYY* @param employee_number employee number that the pay element applies to* @param element name name of the pay element, type of expense* @param p_element_eff_start_date effective start date of the pay element. * @param p_element_eff_end_date effective end date of the pay element* @param p_entry_type a constant 'E' for earnings* @param p_input_value_name1 label of the first entry* @param p_entry_value1 value of the first entry, the amount that is expensed* @param p_input_value_name2 label of the second entry optional* @param p_entry_value2 value of the second entry, optional* @param p_input_value_name3 label of the third entry, optional* @param p_entry_value3 value of the third entry, optional* @param p_date_earned date that the value is earned. Optional* @param p_subpriority priority of the pay element, optional* @param p_subpriority in number default null* @param p_effective_start_date returns the effective start date* @param p_effective_end_datereturns the effective end date* @param p_element_entry_id the number of the pay element (expense) that is * created* @param p_object_version_number the version number of the object* @param p_create_warning flags whether warning is created.1=true, 0=false* @rep:displayname Create a pay element (expense)* @rep:category BUSINESS_ENTITY * @rep:scope public* @rep:lifecycle active*/;end xxgr_soa_pay_elements;

Validate Annotated Files
The integration repository administrator validates the annotated files before uploading them to the repository. This is done using the Integration Repository Parser, a standalone design time tool. While executing the parser, the annotated source files are validated based on the interface type supported for customization. If no error occurs, an Integration Repository loader file (iLDT) will be created. However, before you can start, you need to setup the environment: install Perl modules and possibly install some patches (depending on the EBS12.x version you are using).
Once you installed the necessary Perl modules, you execute the command:

Upload Validated Files
If the annotated files were valid, they can be uploaded to the integration repository by someone with the rol integration repository administrator.

Not that for an object (or class) that is already present in the Integration Repository, the Integration Repository Loader program reloads the new definition of that object ONLY if the new version is greater than the current version. Make sure you increment the Header version of the target source file.
Note that there is no GUI for uploading validated files. You can yse Telnet to have command access to the Oracle E-Business Suite Release 12 instanc and use the following command to upload the iLDT file:

Conclusion
We showed a PL/SQL example here, but the same procedure applies to Java classes, or other supported interface types. Check the developers guide and the implementation guide for a list of supported interface types.

As I stated in my previous post, in order to successfully interface with an Oracle E-Business Suite instance, it is critical to cooperate with the developers and administrators of the Oracle E-Business Suite. That way you ensure that the interfaces are both manageable for the people responsible for running and maintaining the Oracle E-Business suite implementation and maintainable and usable for the developers of the self service application. When executed this way, Oracle E-Business Suite becomes a first class citizen of the 'SOA-World' we live in nowadays.

Tuesday, January 24, 2012

There are many ways you can publish or consume Web Services or XML Documents in an Oracle environment. The other day an organization asked me to evaluate the solution they had chosen to expose PL/SQL packages as Web Service and to consume Web Services from PL/SQL. Not because they experienced problems, but because they wanted to know the 'current state of affairs' in Oracle and XML and evaluate if their way of solving this was the most efficient way.

Not surprisingly, the possibilities are endless, depending on your environment. In this blog, I have listed the possibilities I know of for PL/SQL and added some details about when you would like to use the feature.

Publishing Web services
The following frameworks are available to publish a PL/SQL procedure or function as a web service:

Framework

Infrastructure

Features

Native Database web services

Oracle Database 11g

Oracle XML DB 11g

PL/SQL Webservices

Oracle Applicatieserver

JPublisher, SQLUTL

Eclipselink-DBWS

JEE Applicatieserver

DBWSBuilder (DesignTime)

JAX-WS (Runtime)

Oracle DB Adapater

Oracle SOA Suite

Toplink

Integrated SOA Gateway

Oracle E-Business Suite 12

Oracle SOA Suite 10g

The Integrated SOA Gateway is only a viable option, if you are exposing PL/SQL APIs that are part of EBS 12.

If you don't have an JEE Application server (for example because you are in a .NET programming environment), Native Database web services are a viable option.

Of course, you can also program a web service in .NET or Java and call the PL/SQL procedure or function using Object Relational mappings.

Consuming Web services
The other way around, calling a Web Service from the database is also a common use case if you have a lot of logic in your database.

Framework

Infrastructure

Features

PL/SQL

Oracle Database 11g

UTL_HTTP

Java

Oracle Database 11g

Java stored procedures, JAX-WS

JPublisher

Oracle Database11g

UTL_DBWS

Although I think that BPEL, Java and .Net frameworks are a better fit for this type of thing, it sometimes is not feasible to change the entire software architecture. Using a utility like UTL_DBWS and JPublisher is very convenient and less error prone than coding all the XML by hand in the database.
If you are planning to move to JEE or SOA, using PL/SQL Webservices, EclipseLink or the Oracle database adapter is a good step to start this endeavor.

Monday, January 2, 2012

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:

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.

Preparation
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:

Privileges

System Integration Analyst

System Integration Developer

Integration Repository Administrator

View public interfaces

Yes

Yes

Yes

View private/internal interfaces

No

Yes

Yes

Generate/regenerate Web Services (WSDL)

No

No

Yes

Deploy/redeploy Web Services

No

No

Yes

Undeploy Web Services

No

No

Yes

Subscribe to business events

No

No

Yes

Create Grants

No

No

Yes

Download Composite services

No (configurable)

Yes

Yes

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 WSDLTo 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 WSDLOnce 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 ServiceSQLJUTL 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:http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/?wsdl

To test the web service in your browser, put the link in your browser, without the '?wsdl'http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/

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

ConclusionWe 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".