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 18 Oracle Applications OTD

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 statistics in the data dictionary could produce a long running query.

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

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

   _table_lookup_prefetch_size=0

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

   1. Start SQL*Plus

   2. Connect as the 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, and so on). 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.

   ---

To Create an Oracle Applications OTD

1. In the projects window, right-click your Project, point to 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.

   The Specify Database Connection Information page appears.

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

4. Click Next.

   The Select Oracle Applications Module window appears.

   
   
5. Select the Business Function and Module and choose whether to use fully-qualified names and to replace existing stored procedures.
   

   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.

   ---

6. Click Next to continue.

   The Specify the OTD Name window appears.

   
   
7. Enter a name for the OTD.
8. Click Next.

   The Review Your Selections dialog box appears.

   
   
9. View the summary of the OTD. If you find any errors, click Back and correct the information.
10. If you are satisfied with the OTD information, click Finish to generate the OTD.

    ---

    Caution - If the Oracle Applications DDL Scripts have not been properly installed, an error message occurs 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.

    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.