|Oracle® Fusion Middleware Application Adapters Guide for Oracle Data Integrator
11g Release 1 (11.1.1)
Part Number E17466-03
This chapter describes how to work with Oracle E-Business Suite Knowledge Modules in Oracle Data Integrator.
This chapter includes the following sections:
Oracle E-Business Suite (EBS) is a suite of integrated software applications that provides a complete solution to the business needs of Oracle customers.
The EBS Knowledge Modules provide support for the following capabilities:
Reverse-engineering EBS objects: RKM E-Business Suite can be used to reverse-engineer E-Business Suite data structures
Data extraction from EBS: Standard Oracle or SQL LKMs can be used to extract data from E-Business suite using objects such as Tables, Views, and KeyFlexfields.
Data integration to EBS: IKM E-Business Suite can be used to integrate data to E-Business Suite using Open Interface tables. The “Open Interface” API encapsulates a number of Oracle-specific interfaces and ensures data integrity. An Open Interface is made up of:
Several Interface tables to be loaded. These tables are the incoming data entry points for E-Business Suite.
Several programs that validate and process the insertion of the data from the interface tables into E-Business Suite.
Oracle Data Integrator Knowledge Modules for Oracle E-Business Suite interact with the database tier to extract metadata and load data. While loading data, it also interacts with the Concurrent Processing Server of the application tier.
Oracle Data Integrator provides the Knowledge Modules listed in Table 2-1 for handling E-Business Suite data. These specific EBS KMs provide comprehensive, bidirectional connectivity between Oracle Data Integrator and E-Business Suite, which enables you to extract and load data. The Knowledge Modules support all modules of E-Business Suite and provide bidirectional connectivity through EBS objects tables/views and interface tables.
Table 2-1 EBS Knowledge Modules
IKM E-Business Suite (Open Interface)
The IKM E-Business Suite is used to load data to EBS interface tables and submit Concurrent request (which loads from interface tables to base tables).
This Integration Knowledge Module:
In addition to loading the interface tables, it provides the following optional actions:
Note that the IKM E-Business Suite (Open Interface) KM must only be used to load interface tables. Writing directly in the E-Business Suite physical tables is not supported.
RKM E-Business Suite
This KM reverse-engineers E-Business Suite data structures. It reverses EBS objects such as tables, views, flexfields and interface-tables structures in E-Business Suite (columns, primary keys and foreign keys).
Make sure you have read the information in this section before you start working with the E-Business Suite data:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
There are no technology-specific requirements for using E-Business Suite data in Oracle Data Integrator.
There are no connectivity requirements for using E-Business Suite data in Oracle Data Integrator.
This step consists in declaring in Oracle Data Integrator the data server, as well as the physical and logical schemas for the Oracle database that stores the E-Business Suite data.
Create a data server for the Oracle technology as described in "Creating an Oracle Data Server" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator. This data server must point to the Oracle database instance that stores the E-Business Suite data.
Create an Oracle physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This schema must point to the Oracle schema that contains the synonyms pointing to the E-Business Suite tables.
Note:The physical schema must represent the Oracle schema containing the synonyms pointing to the E-Business Suite tables. This schema is usually called APPS. It must not point directly to the Oracle schemas containing the Application physical tables. These are usually named after the related applications.
Note also that for reverse-engineering, the Oracle user specified in the data server to which the Physical Schema is attached, must have the privileges to select from APPLSYS tables and the Oracle Data dictionary.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
Note:The Oracle schema containing the E-Business Suite tables and the Oracle schema containing the synonyms that point to these tables should never be defined as a Work Schema in a physical schema definition. Moreover, these Oracle schemas must not be used as staging area for an interface.
Setting up a project using E-Business Suite features follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Import the following KMs into your Oracle Data Integrator project:
IKM E-Business Suite (Open Interface)
RKM E-Business Suite
In addition to these specific EBS KMs, import the standard Oracle LKMs and CKMs to perform data extraction and data quality checks with an Oracle database. See "Oracle Database" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integratorfor a list of available KMs.
This section contains the following topics:
Create an Oracle Model based on the Oracle technology and on the logical schema created when configuring the E-Business Suite Connection using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Note:There is no E-Business Suite technology defined in Oracle Data Integrator. The data model is created on top of the logical schema corresponding to the Oracle database hosting the EBS data.
The RKM E-Business Suite is able to reverse-engineer the installed E-Business Suite tables, enriching them with information retrieved from the E-Business Suite Integration repository.
The reverse-engineering process returns the following information:
The installed E-Business Suite (Modules) as sub-models
For each module sub-model, sub-models for Tables, Views, Flexfields, and Interface Tables
The tables and columns, as well as the primary and foreign keys in the datastores
Comments on the tables
To perform a Customized Reverse-Engineering of EBS tables with the RKM E-Business Suite, use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to EBS tables:
In the Reverse tab of the Oracle Model, select the RKM E-Business Suite.
Set the RKM options as follows:
Applications List: Enter the list of the applications' short name, for example
Only Installed Applications: Set this option to
YES to reverse-engineer only installed and shared applications. If this option is set to
NO, all applications are reverse-engineered.
Min Rows: Leave the default value
0, if you want to reverse-engineer all the tables. If you want to reverse-engineer only tables with a minimum number of rows, specify in this option the minimum number of rows.
Description Mask: Specify the description mask for filtering the reverse-engineered objects based on their description in E-Business Suite.
Flexfields: If this option is set to
YES, applications' flexfields are reverse-engineered.
Interface Tables: If this option is set to
YES, applications' interface tables are reverse-engineered.
Specify the reverse mask in the Mask field in order to select the tables to reverse. The Mask field, in the Reverse tab, filters reverse-engineered objects based on their name.
Note:The Mask field and the Description Mask option are implemented using SQL Like. The patterns that you can choose from are:
% the percentage symbol allows you to match any string of any length (including zero length)
_ the underscore symbol allows you to match a single character
The reverse-engineering process returns the applications and tables as sub-models and datastores. You can use Oracle Applications as a source or a target of your integration interfaces.
Features of the E-Business Suite Reverse-Engineering
Reverse-engineering E-Business Suite Tables involves the following features:
The E-Business Suite Modules are reversed as sub-models. The sub-model names correspond to the application names.
Each application sub-model is divided into sub-models for Tables, Views, Flexfields and Interface Tables.
The tables/views and columns, as well as the primary and foreign keys are reversed in the datastores.
A sub-model called Flexfield on <AppName> is created for each application. Datastores in the Flexfield sub-model correspond to Concatenated_Segment_Views of registered Key flexfields for the application. These objects are a subset of Views. The datastores in the Flexfields sub-folder are named after the flexfields.
Datastores in Interface-Table sub-model correspond to tables whose names contain the pattern INTERFACE. These objects are a subset of tables.
Note:Some of the Open Interfaces (as specified in EBS Integration Repository) may have interface tables whose names may not contain the pattern INTERFACE in their names.
Limitations of the E-Business Suite Reverse-Engineering Process
This section covers restrictions on reverse-engineering E-Business Suite Tables:
Selective reverse-engineering cannot be used with this Knowledge Module.
The Min Rows option requires Oracle statistics to be computed on all tables.
If the Oracle user defined in the Oracle Data Integrator data server is not the owner of the tables to reverse-engineer, you must define synonyms for this user on these tables.
Only KeyFlexfields are supported. Descriptive FlexFields are not supported.
You can use E-Business Suite as a source and a target of an integration interface.
The KM choice for an interface determines the abilities and performance of this interface. The recommendations in this section help in the selection of the KM for different situations concerning loading and integrating EBS data.
When using E-Business Suite as a source, you extract data from the Applications to integrate them into another system (Data warehouse, other database...).
Extracting data from E-Business Suite is performed with regular integration interfaces sourcing from an Oracle Database. The knowledge modules working with the Oracle database technology can be used for this purpose. See "Loading Data from Oracle" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information.
Oracle Data Integrator provides the IKM E-Business Suite (Open Interface) to integrate data in E-Business Suite. The integration process into E-Business Suite is as follows:
A set of Open Interface tables is loaded in a batch in a given transaction. This transaction is identified by a Group ID. Note the following concerning the Group ID:
For the first table in the batch, create a Group ID if it does not exist.
For the subsequent tables in the batch, use this Group ID when loading other tables in the batch.
When loading the last table in the batch, delete this Group ID.
If at any point in a batch it is required to call an E-Business Interface program, then you must validate and process data for the interface tables by executing an Open Interface Program. The batch is finalized by the Open Interface Program call that loads the base tables from the Open Interface tables.
These operations are supported by the IKM E-Business Suite (Open Interface). This IKM is used like the IKM Oracle Incremental Update and supports similar options to load the Open Interface tables. This section describes the options specific to Open Interfaces. See "Oracle Database" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information about the IKM Oracle Incremental Update.
The configuration of integration interfaces for actions specific to E-Business Suite, such as Group ID handling and the execution of Open Interface programs, is detailed in the Section 184.108.40.206, "Managing Group IDs" and Section 220.127.116.11, "Executing an Open Interface Program".
A transaction that integrates data into E-Business Suite is a batch identified by its Group ID. For example, if you load several interface tables to create a product in E-Business Suite, all of these loading operations as well as the calls to the validation and processing programs will use this batch's Group ID.
This section contains the following topics:
Creating a Group ID
You must force the creation of a Group ID in the first integration interface that loads a group of interface tables in one single batch.
To create a Group ID in an integration interface:
Set the following in the KM options:
Set OA_CREATE_NEW_GROUP_ID to
Provide a Group ID Name in the OA_GROUP_ID_NAME option.
Note:The Group ID Name must be unique at a given instant. You must use the OA_REMOVE_GROUP_ID option to remove a Group ID at the end of the batch processing.
Give a valid SQL expression for the Group ID value in the OA_GROUP_ID_EXPRESSION option. Use an Oracle Database sequence value, for example
In the integration interface mapping, select the flag UD1 for all the columns of the interface table you wish to load with the Group ID value and set the mapping value to
In the following integration interfaces belonging to a batch, you must use an existing Group ID.
Using an existing Group ID
To use an existing Group ID in an integration interface:
Set OA_USE_EXISTING_GROUP_ID IKM option to
Provide the Group ID Name in the OA_GROUP_ID_NAME IKM option.
In the integration interface mapping, select the flag UD1 for all the columns you wish to load with the Group ID value and set the mapping value to
In the last integration interface that loads a batch of interface tables, you may delete a Group ID that is no longer necessary.
Deleting an existing Group ID
To delete an existing Group ID:
Select the OA_REMOVE_GROUP_ID option.
Provide the Group ID Name in the OA_GROUP_ID_NAME option.
In the integration interface mapping, select the flag UD1 for all the columns of the interface table you wish to load with the Group ID value and set the mapping value to 0.
Note:The Group IDs are stored in an SNP_OA_GROUP table that is created in the work schema specified in the physical schema that points to the Oracle Applications Interface tables. The Group ID is referenced in Oracle Data Integrator by a unique Group ID Name.
In Oracle Data Integrator integration interfaces, when a set of interface tables is loaded, it is necessary to call an Open Interface program in order to validate and process the data in the E-Business Suite interface tables. You can use an existing Group ID in this call (see Using an existing Group ID), or create it (see Creating a Group ID) in the same integration interface, if the Open Interface only contains a single table. The execution of the Open Interface program is started in the last integration interface of a package. This integration interface populates a set of Open Interface tables and usually deletes the Group ID, if no longer needed.
To execute an Open Interface Program:
Set the SUBMIT_PROGRAM option to
Provide the name of the program to call in the OA_PROGRAM option.
Note:For a list of available Open Interface programs and their parameters, please refer to the E-Business Suite module API and Open Interface documentation or the E-Business Suite Integration repository.
Specify the program parameters in the OA_ARGUMENTS option. The parameters are specified in the following format:
argument_name => 'argument value', argument_name => 'argument value' ...
If one argument must take the value of the Group ID, you must then specify
argument Name => v_group_id.
You must also specify the context parameters for the session that will execute the program by setting the values of the following options:
OA_USER_NAME: E-Business Suite User Name
OA_REPONSIBILITY: E-Business Suite Responsibility Name
OA_LANGUAGE: Language used for the responsibility
OA_APPLICATION: Application to which the responsibility belongs