Understanding the New BizTalk Adapter for Oracle E-Business Suite

1. Oracle E-Business Adapter Architecture

  • Oracle E-Business Adapter Architecture

2. Introduction to Oracle EBS

  • Oracle EBS Architecture
  • Application Context
  • Interface Tables / Views
  • Getting Started with Oracle Applications
  • Oracle Apps Application Framework
  • Oracle Apps Administration
  • Concurrent Program
  • Request Sets

3. Connecting to Oracle E-Business Suite

  • Connecting to Oracle E-Business Suite
  • Connecting to underlying Oracle Database
  • Connecting to Oracle E-Business Suite using Windows Authentication

4. Setting Application Context

  • What is Application Context
  • Setting Application Context
  • Precedence Order – Binding Properties vs. Messaging Context Properties
  • Setting Application Context for various artifacts
  • Setting the language for performing operations
  • Binding properties for setting up Application Context for various artifacts

5. What operations can be performed using Oracle E-Business Adapter

  • Features supported by the Oracle E-Business Adapter
  • Key features
  • Limitations of BizTalk Adapter for Oracle E-Business

6. Performing basic operations on Oracle E-Business Suite

  • Working with basic insert operation

7. Invoking Concurrent Programs in Oracle E-Business Suite

  • How to invoke Concurrent Programs in Oracle Applications

8. Invoking Request Sets in Oracle E-Business Suite

  • How to invoke Request Sets in Oracle Applications

Oracle E-Business Adapter Architecture

Architecture of BizTalk Adapter for Oracle E-Business

The Microsoft BizTalk Adapter for Oracle E-Business Suite is a WCF custom binding. This binding contains a single custom transport binding element that enables communication with an Oracle E-Business Suite. The Oracle E-Business adapter is wrapped by the Microsoft WCF LOB Adapter SDK runtime and is exposed to applications through the WCF channel architecture. The Oracle E-Business adapter communicates with the Oracle E-Business Suite through the Oracle Data Provider for .NET 11g (ODP.NET 11g) and the Oracle client, which are part of the Oracle Data Access Components (ODAC) for Windows.

The following figure shows the end-to-end architecture for solutions that are developed by using the Oracle E-Business adapter.

The Microsoft BizTalk Adapter for Oracle E-Business Suite exposes a WCF custom binding, the Oracle E-Business Suite Binding (Microsoft.Adapters.OracleEBS.OracleEBSBinding). By default, this binding contains a single custom transport binding element, the Oracle E-Business Suite Adapter Binding Element (Microsoft.Adapters.OracleEBS.OracleEBSAdapter), which enables operations on an Oracle E-Business Suite.

Microsoft.Adapters.OracleEBS.OracleEBSBinding (the Oracle E-Business Suite Binding) and Microsoft.Adapters.OracleEBS.OracleEBSAdapter (the Oracle E-Business Suite Adapter Binding Element) are public classes and are also exposed to the configuration system. Because the Oracle E-Business Suite Adapter Binding Element is exposed publicly, you can build your own custom WCF bindings capable of extending the functionality of the Oracle E-Business adapter. For example, you could implement a custom binding to support Enterprise Single Sign-on (SSO) in a WCF channel or service model solution. The reasons for doing this would be to aggregate database operations into a single multifunction operation or to perform schema transformation between operations implemented by a custom application and operations on the Oracle E-Business Suite.

The Oracle E-Business adapter is built on top of the Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK and runs on top of the WCF LOB Adapter SDK runtime. The WCF LOB Adapter SDK provides a software framework and tooling infrastructure that the Oracle E-Business adapter uses to provide a rich set of features to users and adapter clients.

The Microsoft BizTalk Adapter for Oracle E-Business Suite implements a set of core components that uses functionality provided by the Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK and provides connectivity to the Oracle database through Oracle Data Provider for .NET 2.0 (ODP.NET).

The WCF LOB Adapter SDK serves as the software layer through which the Oracle E-Business adapter interfaces with the Windows Communication Foundation (WCF); ODP.NET serves as the layer through which the Oracle E-Business adapter interfaces with the Oracle database. The following figure shows the relationships between the internal components of the Oracle E-Business adapter and between these components and ODP.NET.

ODP.NET

The Oracle E-Business adapter connects with the Oracle E-Business Suite through the ODP.NET 11g and the Oracle client. Both of these components are part of Oracle Data Access Components (ODAC).

ODP.NET implements a data provider for the Oracle E-Business Suite that is consistent with the ADO.NET 2.0 interface. The Oracle E-Business adapter uses the classes exposed by ODP.NET to operate on the Oracle E-Business Suite.

The Oracle client provides connectivity to the Oracle E-Business Suite. You establish a connection to an Oracle E-Business Suite by providing a connection URI to the Oracle E-Business adapter. You can specify the connection URI in two ways:

  • Using tnsnames.ora. In this approach, the connection URI provided by the adapter client contains only the net service name specified in the tnsnames.ora file. The adapter extracts the connection parameters such as server name, service name, port number, etc. from the net service name entry in the file. To use this approach, the computer running the Oracle client must be configured to include the net service name for the Oracle database in the tnsnames.ora file.
  • Without using tnsnames.ora. In this approach, the adapter clients specify the connection parameters directly in the connection URI. This does not require the net service name to be present in the tnsnames.ora file on the client computer. This approach does not even require the tnsnames.ora file to be present on the client computer.

Introduction to Oracle EBS

In this session, we will try to get familiar with the Oracle EBS terminology.While working with Oracle EBS, you will come across following terms:

  • Interface Tables / Views
  • Application Context
  • Concurrent Programs
  • Request Sets

Application Context

Oracle EBS is a logical wrapper on top of Oracle DB. It consists of a large number of predefined tables, packages, and relations amongst them. Oracle EBS also has a concept of Oracle EBS username and password, which is separate from DB username and password. In fact, when one connects to an Oracle EBS system from the web based UI (called the forms UI), one needs to enter the EBS username and password. The DB username and password do not come into play, and are stored in a config file on the forms server.

Apart from username, Oracle EBS defines Responsibilities. A responsibility as it sounds is a set of permissions. For example, one can define a responsibility named ‘Contoso Finances’, and grant it permission to update the salary details of employees. Now, various users can be associated with the responsibility, or in other words, can be granted this responsibility by the super user. Any user that has been granted the responsibility ‘Contoso Finances’ will be able to invoke the operations the responsibility has permission to.

Another important concept is that of an application. Artifacts (Concurrent Programs, PL-SQL APIs, etc) have been logically grouped into categories known as Applications. For example, all artifacts relating to Payables and Receivables have been grouped into two applications by the same names.

Setting Application Context is an important activity while working with Oracle EBS. We have included separate lesson on how to set up the application context.

Interface Tables / Views

Oracle has written the "logical-layer" on top of the DB using which you can do application-logic. For example, if you want to implement an order management system, you have two options:

1. Purchase Oracle DB (or some DB) and write your own logic. Or,

2. Purchase Oracle EBS and customize the Order Management Application to suit your business needs.

The various pre-defined artifacts can be categorized into logical groups or applications - e.g. Financials, HRMS etc. Each application can use one or more modules (module maps to a schema in the DB). E.g., in a Financials logic cycle, I can use PO, GL, AR and AP modules. Each module (or schema) has a list of pre-defined artifacts like tables, views, interface tables, interface views, CPs and PL-SQL APIs. The Oracle documentation explains when/where/how each of these artifacts are used.

Typically most of the business-transactions in EBS have the following sequence:

-- push data into interface table. E.g., the orders you are getting.

-- Call Concurrent Program that validates this data and moves it to the base table E.g., the CP can see if the ordered items exist in the inventory and then place the appropriate order. Else it can raise an error. The application developer is responsible for taking the appropriate action based on the result of the CP. In some cases, PL-SQL APIs are used instead of CPs.

Essentially, these are temporary tables where data is held before CP or PL-SQL APIs validate it and move the data to the base tables.

Connecting to Oracle E-Business Suite

  • Create a new BizTalk project.
  • Right Click on the project name in the solution explorer – Add – Add Generated Items – Add Adapter Metadata.
  • Click on Add button.
  • Select WCF-OracleEBS adapter from the list and click on the Next.

On the Consume Adapter Service screen, by default oracleEBSBinding will be selected for you.

Specifying URI Properties

Using tnsnames.ora
The connection URI provided by the adapter client contains only the net service name specified in the tnsnames.ora file. The adapter extracts the connection parameters such as server name, service name, and port number from the net service name entry in the tnsnames.ora file. To use this approach, the computer running the Oracle client must be configured to include the net service name for the Oracle database in the tnsnames.ora file.

oracleebs://User=[USER_NAME];Password=[PASSWORD]@[NET_SERVICE_NAME]

Without using tnsnames.ora

The connection URI provided by the adapter clients contains the connection parameters such as server name, service name, and port number. In this case, the net service name in the tnsnames.ora file, or the actual tnsnames.ora file itself, does not need to be present on the client computer. This is helpful when you have a large number of users connecting to the Oracle database in your organization, and adding/updating servers does not lead to manually adding/updating the connection details in the tnsnames.ora file on every client computer.

oracleebs://User=[USER_NAME];Password=[PASSWORD]@[SERVER_NAME]:[PORT_NUMBER]/[SERVICE_NAME]/SERVICE_TYPE]

Note: Data Source string shall not bemore than 39 characters long when transaction is being used. If it is greater than 39 characters (default behavior for non-tnsnames.ora connection) AND transaction is being used, the Adapter will suspend the message and log the error in the event log.

By default, the Oracle E-Business adapter throws an exception when the Oracle credentials are specified in the connection URI. This is because these credentials are represented as plain text in the connection URI, and this poses a security risk. You can set the AcceptCredentialsInUri binding property to control whether the connection URI can contain credentials for the Oracle database.

If the AcceptCredentialsInUri property is false, which is the default, the Oracle E-Business adapter throws an exception if the connection URI contains Oracle credentials; if the property is true, no exception is thrown.

Due to the security risks posed by passing credentials in strings as plain text, you should avoid specifying Oracle database connection credentials in the connection URI. This property is surfaced because there are certain programming scenarios that require the credentials to be present in the connection URI. This should never be the case when you are configuring a send port or a receive location, or when you are using the Add Generated Items Add-in to retrieve message schemas from the Oracle E-Business adapter. It is recommended that you do not set AcceptCredentialsInUri to true.

The AcceptCredentialsInUri binding property is not available in BizTalk Server in the Binding tab while configuring a WCF-Custom or WCF-OracleEBS receive or send port.

Specifying Client Credentials

The Oracle E-Business adapter exposes the ClientCredentialType binding property that allows you to specify the set of credentials (Oracle E-Business Suite or Oracle database) that will be used to connect to Oracle E-Business Suite.


  • To connect using the Oracle database credentials, specify the ClientCredentialType binding property as Database, and then, on the Security tab, specify the database credentials in the User name and Password text boxes. If you will be performing operations on any of the Oracle E-Business Suite artifacts (interface table, interface view, concurrent program, request set, or Oracle E-Business Suite PL/SQL APIs), you must also provide the Oracle E-Business Suite credentials in the OracleUserName and OraclePassword binding properties.
  • To connect using Oracle E-Business Suite credentials, specify the ClientCredentialType binding property as EBusiness, and then specify Oracle E-Business Suite credentials in the User name and Password text boxes on the Security tab. You must also specify the Oracle database credentials for the OracleUserName and OraclePassword binding properties.
  • To connect using Windows Authentication, you must do the following:
    If the ClientCredentialType property is set to Database, specify “/” for the user name and leave the password blank to connect to the Oracle E-Business Suite.
    If the ClientCredentialType property is set to EBusiness, specify the Oracle E-Business Suite credentials to connect. Also, you must specify “/” for the OracleUserName binding property and leave the OraclePassword binding property blank.

Use this / To do this
To connect using Oracle database credentials / Specify the ClientCredentialType binding property to Database and specify database credentials for User name and Password text boxes.
To connect using Oracle E-Business Suite credentials / Specify the ClientCredentialType binding property to EBusiness and specify Oracle E-Business Suite credentials for User name and Password text boxes. In this case, you must also specify Oracle database credentials for OracleUserName and OraclePassword binding properties.
To connect using Windows Authentication if ClientCredentialType is set to “Database” / Specify a “/” for the User name text box and leave the Password text box blank.
To connect using Windows Authentication if ClientCredentialType is set to “EBusiness” / Specify Oracle E-Business Suite credentials for User name and Password text boxes. You must also specify a “/” for the OracleUserName binding property and leave the OraclePassword binding property blank.

After providing Security and URI properties on the configure Adapter screen click OK and on the Consume Adapter Service screen click Connect.

The Oracle E-Business adapter enables adapter clients to browse interface tables, interface views, concurrent programs, and request sets in Oracle E-Business Suite and tables, views, stored procedures, functions, and packages in the underlying database. As part of the metadata browse operation, the adapter also surfaces the operations that can be performed on the Oracle database, including some custom operations supported by the adapters. These operations are available from Consume Adapter Service Add-in, Add Adapter Metadata Wizard, and Add Adapter Service Reference Plug-in.

The Oracle E-Business adapter surfaces most of the operations under the following three nodes:

  • Application-Based View: Contains the operations grouped by each application for the Oracle E-Business Suite artifacts.
  • Artifact-Based View: Contains the operations grouped by artifact type (such as Interface Tables, Interface Views, and so on) in Oracle E-Business Suite and the underlying database.
  • Schema-Based View: Contains the operations grouped by each schema for the underlying database artifacts.

Client (Outbound operations)

Service (Inbound operations)

The following figure shows the Consume Adapter Service Add-in. The root node (/) is selected, and the general category nodes available under the root node are listed in the Available categories and operations box. The inbound operation, Notification, is available at the root level.

For Polling, you need to select one of the categories and select an operation. Here are a few examples: