Fusion Middleware Documentation
Advanced Search


Application Adapters Guide for Oracle Data Integrator
Close Window

Table of Contents

Show All | Collapse

2 Oracle E-Business Suite

This chapter describes how to work with Oracle E-Business Suite Knowledge Modules in Oracle Data Integrator.

This chapter includes the following sections:

2.1 Introduction

Oracle E-Business Suite (EBS) is a suite of integrated software applications that provides a complete solution to the business needs of Oracle customers.

2.1.1 Concepts

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.

2.1.2 Knowledge Modules

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

Knowledge Module Description

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:

  • Integrates data from any source to Interface Tables in incremental update mode.

  • Enables data control: invalid data is isolated in the Error Table and can be recycled.

In addition to loading the interface tables, it provides the following optional actions:

  • Create a Group ID for the first mapping in a batch.

  • Use this Group ID in subsequent mappings.

  • Delete this Group ID when loading the last table in the batch.

  • Execute an Open Interface program if at any point in a batch it is required to call an E-Business Suite Interface program and once all required interface tables have been loaded.

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).


2.2 Installation and Configuration

Make sure you have read the information in this section before you start working with the E-Business Suite data:

2.2.1 System Requirements and Certifications

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

http://www.oracle.com/technology/products/oracle-data-integrator/index.html.

2.2.2 Technology Specific Requirements

There are no technology-specific requirements for using E-Business Suite data in Oracle Data Integrator.

2.2.3 Connectivity Requirements

There are no connectivity requirements for using E-Business Suite data in Oracle Data Integrator.

2.3 Setting Up the Topology

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.

2.3.1 Create an Oracle Data Server

Create a data server for the Oracle technology as described in "Creating an Oracle Data Server" in the 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.

2.3.2 Create an Oracle Physical Schema

Create an Oracle physical schema using the standard procedure, as described in "Creating a Physical Schema" of the 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 Developer's Guide for Oracle Data Integratorand 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 a mapping.

2.4 Setting up an Integration Project

Setting up a project using E-Business Suite features follows the standard procedure. See "Creating an Integration Project" of the 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 Connectivity and Knowledge Modules Guide for Oracle Data Integratorfor a list of available KMs.

2.5 Creating an Oracle Model and Reverse-Engineering E-Business Suite Tables

This section contains the following topics:

2.5.1 Create an Oracle Model

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

2.5.2 Reverse-Engineer E-Business Suite Tables

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 as datastores with their columns as attributes, and their constraints (Primary and Foreign Keys).

  • 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 Developer's Guide for Oracle Data Integrator. This section details only the fields specific to EBS tables:

  1. In the Reverse Engineer tab of the Oracle Model, select the RKM E-Business Suite.

  2. Set the RKM options as follows:

    • Applications List: Enter the list of the applications' short name, for example INV.

    • 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.

  3. Specify the reverse mask in the Mask field in order to select the tables to reverse. The Mask field, in the Reverse Engineer 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 mappings.

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 are reversed as datastores and their columns as attributes, along with their constraints (Primary and Foreign keys).

  • 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.

2.6 Designing a Mapping

You can use E-Business Suite as a source and a target of a mapping.

The KM choice for a mapping determines the abilities and performance of this mapping. The recommendations in this section help in the selection of the KM for different situations concerning loading and integrating EBS data.

2.6.1 Loading Data from E-Business Suite

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 mappings 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 Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information.

2.6.2 Integrating Data in E-Business Suite through the Open Interface

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:

  1. 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.

  2. 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 Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information about the IKM Oracle Incremental Update.

The configuration of mappings for actions specific to E-Business Suite, such as Group ID handling and the execution of Open Interface programs, is detailed in the Section 2.6.2.1, "Managing Group IDs" and Section 2.6.2.2, "Executing an Open Interface Program".

2.6.2.1 Managing Group IDs

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 mapping that loads a group of interface tables in one single batch.

To create a Group ID in a mapping:

  1. Set the following in the KM options:

    • Set OA_CREATE_NEW_GROUP_ID to YES

    • 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 <SEQUENCE_NAME>.NEXTVAL

  2. In the 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.

In the following mappings belonging to a batch, you must use an existing Group ID.

Using an existing Group ID

To use an existing Group ID in a mapping:

  1. Set OA_USE_EXISTING_GROUP_ID IKM option to Yes.

  2. Provide the Group ID Name in the OA_GROUP_ID_NAME IKM option.

  3. In the mapping, select the flag UD1 for all the columns you wish to load with the Group ID value and set the mapping value to 0.

In the last mapping 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:

  1. Select the OA_REMOVE_GROUP_ID option.

  2. Provide the Group ID Name in the OA_GROUP_ID_NAME option.

  3. In the 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.

2.6.2.2 Executing an Open Interface Program

In Oracle Data Integrator mappings, 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 mapping, if the Open Interface only contains a single table. The execution of the Open Interface program is started in the last mapping of a package. This mapping populates a set of Open Interface tables and usually deletes the Group ID, if no longer needed.

To execute an Open Interface Program:

  1. Set the SUBMIT_PROGRAM option to YES.

  2. 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.

  3. 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.

  4. 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