test

Developing OTDs for Application Adapters

Creating an Oracle Applications OTD

The Oracle Applications Adapter uses a wizard-based OTD builder to create OTDs based on your Oracle tables. The wizard queries the Oracle tables to determine the hierarchies of the interface tables for a particular module, and creates a corresponding OTD. It also sets up the necessary staging table and the stored validation procedures to be run against the table.

Figure 1–18 Oracle Applications OTD

Oracle Apps Builder

When building an OTD, the wizard calls many JDBC APIs (for example, getProcedureColumns()) which in turn queries the database and returns the resultset. While the Adapter itself doesn’t issue the queries directly, it is the Oracle driver that translates the API into multiple queries. In a situation where there is a lot of data in the database, it may take a while to return all the resultsets to the wizard. The performance of the queries is dependent on the execution path which is formulated when a SQL call is prepared. Not having good stats in the data dictionary could produce a long running query.

Oracle recommends doing the following to gather vital stats to improve performance:

  1. Set the following in either the init.ora file or spfile (whichever is appropriate for their install):

    _table_lookup_prefetch_size=0

  2. Analyze the SYS schema for the system as follows:

    1. Start sqlplus

    2. Connect as sys user

    3. exec dbms_stats.gather_schema_stats(’SYS’);

    Keep in mind that significant changes to the database would affect the data dictionary (like new tables, indexes, etc). You should consider running the analysis regularly.

    Note –

    Please consult your Database Administrator or Oracle before taking these steps as it may impact other applications.

The following steps are required to create a new OTD for the Oracle Applications adapter.

Select Wizard Type

Select the type of wizard required to build an OTD in the New Object Type Definition Wizard.

ProcedureTo Select the Oracle Applications OTD Wizard

  1. In the Enterprise Designer Project Explorer, right-click your Project, click New, and then click Object Type Definition.

    The Object Type Definition Wizard appears.

  2. From the New Object Type Definition Wizard window, select the Oracle Applications Wizard and click Next.

    Figure 1–19 OTD Wizard Selection

    OTD Wizard Selection

Connect To Database

Enter the Oracle Applications connection information in the Connection Information frame.

Database Connection Information

ProcedureTo Connect to the Database

  1. Specify the applicable connection information for your database including:

    • Host Name - The server where Oracle Applications resides.

    • Port ID- The port number of Oracle Applications.

    • SID - The name of the Oracle instance (equivalent to the database name).

    • User Name - The user name that the Adapter uses to connect to the database.

    • Password - The password used to access the database.

  2. Click Next.

    The Select Oracle Applications module window appears.

Select Oracle Applications Module

Select the type of Oracle Applications Module you want included in the OTD.

ProcedureTo Select the Oracle Applications Module

  1. In the Select Oracle Applications Module dialog box (shown below), select the Business Function and Module and choose whether to use fully-qualified names and to replace existing stored procedures.

    Select Database Objects

    Module Information 

    Description 

    Business Function 

    Currently Financial and Manufacturing are the only supported business functions. 

    Module 

    The available modules in the Financial business function are: 

    • Auto Invoice

    • Auto Lock

    • Bank Statement

    • Budget

    • Customers

    • Daily Rates

    • Fixed Assets Categories

    • Fixed Assets Mass Additions

    • Journal

    • Payable

      The available modules in the Manufacturing business function are:

    • Customer Item

    • Customer Item Cross-Reference

    • Cycle Count Entries

    • Item Import

    • Item Transactions

    • Order Requisition

    • Order Import

    • Order Receiving

    • Replenishment

    Use fully-qualified names in the Java code 

    Specifies whether the generated Java code uses fully-qualified names. 

    Replace Existing Stored Procedures 

    Specifies to replace any existing stored procedures or stop the wizard if any stored procedures exist. You must select this option in order to continue with the wizard. 

    Take care to back up any stored procedures you have modified before continuing with this wizard. 

    Note –

    A set of stored procedures are installed with the Oracle Applications Adapter. Unless these stored procedures are somehow deleted, the only way to create the OTD is by selecting Replace Existing Stored Procedures, otherwise, the existing stored procedures are not overwritten, an error appears, and the wizard stops.

  2. Click Next to continue.

    The Specify the OTD Name window appears.

Specify the OTD Name

Specify the name that your OTD will display in the Java CAPS IDE.

ProcedureTo Specify the OTD Name

  1. Enter a name for the OTD.

    Figure 1–20 Naming an OTD

    Naming an OTD

  2. Click Next.

    The Review Your Selections dialog box is displayed.

Review Selections

Review the selections made for the new OTD.

ProcedureTo Review Your OTD Selections

  1. View the summary of the OTD. If you find you have made a mistake, click Back and correct the information.

  2. If you are satisfied with the OTD information, click Finish to begin generating the OTD.

    Caution – Caution –

    If the Oracle Applications DDL Scripts have not been properly installed, an error message will be generated before the OTD can be successfully generated.

    The resulting OTD appears in the Java CAPS IDE. The time it takes the OTD to generate depends on the module you selected and your system performance.

    Figure 1–21 Database Wizard - Summary

    Database Wizard - Summary

    The generated OTD appears in the OTD Editor. Nodes and methods for your OTD depend on the module you selected and the configuration of your tables.

    Figure 1–22 OTD Editor

    Generated OTD