Skip Headers
Oracle® Fusion Middleware Application Adapters Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E17466-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 JD Edwards EnterpriseOne

This chapter describes how to work with JD Edwards EnterpriseOne Knowledge Modules in Oracle Data Integrator.

This chapter includes the following sections:

3.1 Introduction

JD Edwards (JDE) EnterpriseOne is an integrated applications suite of comprehensive ERP software that combines business value, standards-based technology, and deep industry experience into a business solution with a low total cost of ownership.

3.1.1 Concepts

The JDE Knowledge Modules for Oracle Data Integrator use mature database-level integration methods for JDE EnterpriseOne, in order to:

  • Reverse-Engineer JDE EnterpriseOne data structures

  • Read data from JDE EnterpriseOne (Direct Database Integration)

  • Write data through the Z-tables to an JDE Application (Interface Table Integration)

3.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules listed in Table 3-1 for handling JDE EnterpriseOne data. These specific JDE KMs provide connectivity and integration of the JDE EnterpriseOne platform with any database application through Oracle Data Integrator.

Table 3-1 JDE Knowledge Modules

Knowledge Module Description

RKM JDE Enterprise One Oracle

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE EnterpriseOne installed on an Oracle database.

RKM JDE Enterprise One SQL Server

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE EnterpriseOne installed on SQL Server.

RKM JDE Enterprise One DB2 UDB

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE EnterpriseOne installed on IBM DB2 UDB database.

RKM JDE Enterprise One DB2 AS400

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE EnterpriseOne installed on IBM DB2 for iSeries server.

IKM JDE Enterprise One Control Append (UBE)

Integrates data from any source to JDE EnterpriseOne. Integrates data in EnterpriseOne Z-table in control append mode.

  • Data can be controlled: invalid data is isolated in the Error Table and can be recycled

  • The KM performs integration into JDE Enterprise One with a RunUBE batch command


3.2 Installation and Configuration

Make sure you have read the information in this section before you start working with the JDE EnterpriseOne data:

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

3.2.2 Technology Specific Requirements

In order to use the IKM JDE Enterprise One Control Append (UBE), the Oracle Data Integrator run-time agent must be installed on the JDE Server where the RunUBE utility is installed.

In order to use the RKM JDE Enterprise One DB2 UDB to reverse-engineer tables and Z-tables, the IBM DB2 UDB database should be able to access data stored in different DB2 databases. The following steps describe how to configure the access to DB2 family data sources:

  1. Set up and configure the federated server and database. Configuring the federated server to access DB2 data sources involves supplying the server with information about the DB2 data sources and objects you want to access. You can configure access to DB2 data sources two ways:

    • Through the DB2 Control Center

    • Through the DB2 Command Center or command line processor (CLP)

  2. Add a DB2 data source to a federated server:

    1. Catalog a node entry in the federated node directory.

      For example, if TCP/IP is your communication protocol issue the following command:

      CATALOG TCPIP NODE <db2node> REMOTE <system> SERVER <server_name>

    2. Catalog the remote database in the federated system database director using the following command:

      CATALOG DATABASE <db_name> AS <alias_name> AT NODE <db2_node> AUTHENTICATION SERVER

    3. Create the wrapper using the following command:

      CREATE WRAPPER DRDA

      DRDA is the default wrapper name to access the DB2 family of products. Every DB2 Server Edition (Enterprise, Personal, Workgroup) includes the DRDA wrapper.

    4. Create the server definition.

      CREATE SERVER <server_name> TYPE <type> VERSION <version> WRAPPER <wrapper_name> AUTHORIZATION <user> PASSWORD <password> OPTIONS (DBNAME <db_name>)

      where:

      AUTHORIZATION <user>

      Is the authorization ID at the data source. This ID must have BINDADD authority at the data source. This value is case-sensitive.

      PASSWORD <password>

      Is the password associated with the authorization ID at the data source. This value is case-sensitive.

      DBNAME <db_name>

      The alias for the DB2 database that you want to access. You defined this alias when you cataloged the database using the CATALOG DATABASE command. This value is case-sensitive.

      Although the database name is specified as an option in the CREATE SERVER statement, it is required for DB2 data sources.

    5. Create the user mappings. If a user's authorization ID to access the federated database differs from the user's authorization ID to access a data source, you need to define a user mapping between the two authorization IDs.

      CREATE USER MAPPING FOR <db2user> SEVER <server_name> OPTIONS (REMOTE_AUTHID <remote_user> REMOTE_PASSWORD <remote_password>)

      Note that the REMOTE_AUTHID is the connect authorization ID at the DB2 family data source server to which you are mapping the db2user, not the bind authorization ID.

    6. Test the connection to the DB2 data source server.

    7. Create the nicknames for every table object that resides on the different database that you want to access. The federated database relies on catalog statistics for nicknamed objects to optimize query processing. Create a nickname for each data source object using the following command:

      CREATE NICKNAME <nickname> FOR <server_name>.<schema_name>.<table_name>

      Create the nicknames as described in Table 3-2.

      Table 3-2 Nicknames for Table Objects

      Nickname Table Name Schema

      F9860

      F9860

      JDE_OBJECT_LIBRARIAN

      F98710

      F98710

      JDE_CENTRAL_OBJECTS

      F98711

      F98711

      JDE_CENTRAL_OBJECTS

      F98712

      F98712

      JDE_CENTRAL_OBJECTS

      F98713

      F98713

      JDE_CENTRAL_OBJECTS

      F9802

      F9802

      JDE_DATA_DICTIONARY


      Note:

      Even if the table names and schemas might be different between JDE version 8 and 9 and differ from the values listed in Table 3-2, the nicknames should stay the same.

See the IBM DB2 Universal Database - Federated Systems Guide for more information.

3.2.3 Connectivity Requirements

Oracle Data Integrator connects to the database hosting the JDE data using JDBC connectivity. For detailed information on JDBC connectivity with Oracle database, Microsoft SQL Server, IBM DB2 UDB, and IBM DB2 for iSeries, see the following sections in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator:

3.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 that will be used to store the JDE data.

3.3.1 Create a Data Server

Depending on the underlying technology, the JDE tables can be stored in an Oracle schema, a Microsoft SQL Server database, an IBM DB2 UDB schema or in an IBM DB2 for iSeries library.

Create a data server for the technology hosting the JDE tables. For more information, see the following sections in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator:

This data server must point to the instance, schema, database, or library (in the subsequent sections, the term schema will be used for all technologies) that stores the JDE data.

3.3.2 Create a Physical Schema

Create a physical schema under the data server that you have created in Section 3.3.1, "Create a Data Server". Use 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 schema that contains the JDE tables that you want to reverse-engineer.

Note:

The schema storing the JDE tables should never be defined as a work schema in the physical schema definition. Moreover, this schema must not be used as staging area of an interface.

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.

3.4 Setting up an Integration Project

Setting up a project using JDE 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:

In addition to these specific JDE KMs, import the standard LKMs for the technology hosting your JDE tables. For a list of available KMs, see the following sections in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator:

3.5 Creating and Reverse-Engineering a Model

This section contains the following topics:

3.5.1 Create a Model

Create a Model based on the technology hosting the JDE tables and on the logical schema created when configuring the JDE 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 JDE EnterpriseOne technology defined in Oracle Data Integrator. The data model is created with the logical schema corresponding to the Oracle database hosting the JDE data.

3.5.2 Reverse-Engineer JDE Tables

The JDE RKMs are able to reverse-engineer JDE tables. These RKMs retrieve metadata from JDE objects such as tables and interface tables.

To perform a Customized Reverse-Engineering of JDE tables with the JDE RKMs, 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 JDE tables:

  1. In the Reverse tab of the Model, select the RKM JDE Enterprise One <database>. In this chapter, <database> refers to the technology containing the JDE data.

  2. Set the RKM options as follows:

    • JDE_CENTRAL_OBJECTS: Specify the Oracle Schema or Microsoft SQL Server Database storing the JDE Central objects

    • JDE_DATA_DICTIONARY: Specify the Oracle Schema or Microsoft SQL Server Database storing the JDE data dictionary

    • JDE_OBJECT_LIBRARIAN: Specify the Oracle Schema or Microsoft SQL Server Database storing the JDE Object librarian

    • JDE_CONTROL_TABLES: Specify the Control Tables schema

      Note:

      To find the schema required in the options JDE_CENTRAL_OBJECTS, JDE_DATA_DICTIONARY, JDE_OBJECT_LIBRARIAN, and JDE_CONTROL_TABLES you can either ask your application manager or query the table F98611(Data Source Master).

    • JDE_DATA_TABLES: Set this option to YES to reverse-engineer data tables

    • JDE_Z_TABLES: Set this option to YES to reverse-engineer interface tables (Z-tables)

    • JDE_MODULES: Indicate the JDE System Short Name, for example 00 for Foundation Environment, 01 for Address Book, and 02 for Electronic Mail

      Note:

      You can also specify a list of modules. In the list, the modules must be separated by commas and enclosed within single-quote characters, for example:

      '01','02','04'

    • JDE_LANGUAGE: Indicate the language used for retrieving object descriptions and comments, for example E for English, F for French, and S for Spanish

  3. 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. The Mask field must not be empty and must contain at least the percentage symbol (%).

The reverse-engineering process returns the datastores grouped per module. You can use these datastores as a source or a target of your integration interfaces.

3.6 Designing an Interface

You can use JDE data tables as a source of an integration interface. JDE Z-tables can be used as the 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 JDE data.

3.6.1 Loading Data from JDE

After performing a reverse-engineering using the RKM JDE Enterprise One <database>, you can use JDE data tables as a source of an integration interface to extract data from the JDE application and integrate them into another system (Data warehouse, other database and so forth).

Using JDE as a source in these conditions is the same as using an Oracle, Microsoft SQL Server, DB2/400 or an IBM DB2 UDB datastore as a source in an integration interface. The generic SQL, Oracle Database, or Microsoft SQL Server, IBM DB2 for iSeries, and IBM DB2 UDB KMs can be used for this purpose. See the following chapters in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information:

3.6.2 Integrating Data in JDE

After performing a reverse-engineering using the RKM JDE Enterprise One <database>, you can use JDE Z-tables as a target of an interface to load data from any system to the JDE application with the IKM JDE Enterprise One Control Append (UBE).

The integration of data into JDE Enterprise One is performed in two phases:

  • During the first phase data is integrated into a set of Z-tables using several interfaces, without calling the RunUBE command. These interfaces can use the IKM JDE Enterprise One Control Append (UBE) with the JDE_RUNUBE option set to No.

  • During the second phase the RunUBE command is launched to integrate the data from these Z-tables into JDE Enterprise One. This is typically done in the interface loading the last required Z-table. This interface also uses the IKM JDE Enterprise One Control Append (UBE) with the JDE_RUNUBE option set to Yes.

These interfaces should be sequenced in a package.

Oracle Data Integrator can automatically call the RunUBE command to write to JDE. The RunUBE call should be activated in the IKM only after loading all the required Z-table for populating JDE. The capability to load the Z-Tables, as well as the call of the RunUBE command is provided by the IKM JDE Enterprise One Control Append (UBE).

To create an interface targeting JDE:

  1. Create an integration interface with Z-tables as target datastores.

  2. Create joins, filters, and mappings as usual.

  3. In the Flow tab select the IKM JDE Enterprise One Control Append (UBE).

  4. Set the standard KM options (INSERT, COMMIT, FLOW_CONTROL).

  5. If this interface launches the RunUBE command, specify the KM options as follows:

    1. Set the JDE_RUNUBE option to Yes.

    2. Specify the JDE_DIRECTORY in which the RunUBE command is executed.

    3. If you want to create a password file, set the password related options as shown in Table 3-3.

      Table 3-3 Password Related KM Options

      Option Value Notes

      JDE_CREATE_PWD_FILE

      Yes

      To enhance RunUBE security in a Unix or iSeries environment, when the RunUBE command is submitted, the system reads the text file specified in the JDE_PWD_FILE option and uses the JD Edwards EnterpriseOne user ID and password as indicated in the text file.

      JDE_PWD_FILE

      Absolute path of the password security file

      This file contains the user id and password specified in the JDE_USER_ID and JDE_PWD options.

      JDE_DELETE_PWD_FILE

      E|D

      Enter D to delete the password file.

      Enter F to keep the password file.

      Note that even if the password file is removed after the execution of the command, the file should be kept in a secure location on the server file system

      JDE_USER_ID

      JDE EnterpriseOne user ID

      The user must have permissions to run the report.

      JDE_PWD

      JDE EnterpriseOne password

      The EnterpriseOne password that corresponds to the user ID.


    4. Set the parameters for the RunUBE command as shown in Table 3-4.

      Table 3-4 RunUBE Command related KM Options

      Option Value Notes

      JDE_ENVIRONMENT

      The JDE EnterpriseOne environment

       

      JDE_ROLE

      The JDE EnterpriseOne role

       

      JDE_REPORT

      The system name of the report that you want to process

      For example: APS Outbound Processor (R34A400) and APS Inbound Processor (R34A410) for flat files, and the APS SCBM 2.0 Outbound Processor (R34A700) and APS Master Inbound Processor (R34A820) for XML files

      JDE_VERSION

      The name of the version of the report that you want to process

      For example: XJDE0001. Note that you must enter a version name; you cannot submit the template of a report.

      JDE_JOB_QUEUE

      The name of the job queue to which the system should route the batch job

      For example: QBATCH

      JDE_PROCESSING_MODE

      The processing mode

      Enter B to use batch processing. In this case, the system uses the Job Control Status Master table (F986110) to assign the report a place in the queue.

      Enter I to use the interactive mode. This mode runs the report immediately outside of the JDE EnterpriseOne queuing mechanism

      JDE_HOLD_CODE

      The hold code

      Enter P to send the output to a printer immediately after the job completes.

      Enter H to hold the processed file without printing. You can print the job later using the Work With Servers program (P986116) that is accessible from the System Administration Tools menu (GH9011).

      JDE_SAVE_CODE

      The save code

      Enter S to save the file after processing is complete.

      The delete option (D) is reserved for future use. Currently, the delete option is disabled.


Limitations of the IKM JDE Enterprise One Control Append (UBE)

  • The TRUNCATE option cannot work if the target table is referenced by another table (foreign key).

  • When using the RECYCLE_ERRORS option, an Update Key must be set for your interface.

  • When using this module with a journalized source table, data is automatically filtered to not include source deletions.

  • The FLOW_CONTROL and STATIC_CONTROL options call the Check Knowledge Module to isolate invalid data (if no CKM is set, an error occurs). Both options must be set to No when an integration interface populates a TEMPORARY target datastore.

  • The RunUBE command must be executed on the JDE server.

  • The Oracle Data Integrator run-time agent must be installed on this server.

  • Besides the information whether the RunUBE command has been started or not, the RunUBE command does not give any further details about the execution of the program. To know more about the execution of the program you can either view the log file created by the JDE server or connect to your JDE application and look for the application View Job Status (Application = P986110, Form = W986116A).