Installing Upgrading Designing Configuring Deploying Monitoring Administering Troubleshooting Reference JBI Components
Close Print View
Designing: Using the Oracle Applications Object Type Definition
 

Classic Java CAPS

Developing Java CAPS Projects

Using SOAP Message Handlers

Creating a Runtime Environment

Designing Business Processes in the Sun Business Process Manager

Working with TCP/IP HL7 Collaborations

Developing Sun Master Indexes (Repository)

Developing Sun Master Patient Indexes

Developing OTDs for Application Adapters

Developing OTDs for Communication Adapters

Developing OTDs for Database Adapters

Developing OTDs for Web Server Adapters

Designing with Application Adapters

Designing with Communication Adapters

Designing with Web Server Adapters

SWIFT Integration Projects

Java EE Based Components

Designing with Sun JCA Adapters

Technical Overview for Sun JCA Adapters

Inbound JCA Resource Adapter Client Code

Outbound JCA Resource Adapter Client Code

Object Type Definition Wizards

Installation of Netbeans Modules

Installing the Modules Pack

Installation of Base Components for Sun JCA Adapters

To Install Base Components

Configuring Runtime Components in an EJB/JCA Application

Configuring Connector Pools for File Adapter

Configuring Connector Pools for Oracle Adapter

Deployment of Sun JCA Adapters

To Deploy JCA Adapters via Command Line

To Deploy JCA Adapters via the Admin Console

Using the Oracle Wizard and JCA Adapter Tooling with an EJB Project

To implement the Oracle JCA Adapter with an EJB Project

Using the Oracle Applications Wizard and JCA Adapter Tooling with an EJB Project

To implement the Oracle Applications JCA Adapter with an EJB Project

Using the Oracle Applications Object Type Definition

About the TCP/IP JCA Adapter

Defining Constants and Variables

Using Database Operations

Developing Sun Master Indexes

Using the JMS JCA Wizard

Using the JAXB Wizard and Code-Seeder Pallete

Using the Oracle Applications Object Type Definition

Each of the Oracle Applications Object Type Definition modules is divided into submodules, representing a logical division of workflow. Each submodule uses at least one corresponding set of tables and scripts. For a high level hierarchy of the Manufacturing and Financial modules and further details on their submodules, see Building an Oracle Applications Custom Pre-Validation Package in Designing with Application Adapters

Once an Oracle Applications OTD is generated, all of the staging tables and stored procedures for the corresponding submodules are generated in the Oracle database. These staging tables and procedures can be invoked through the OTD.

In general, a user can take the following steps to invoke operations in a database:

  1. Insert data into the staging table.

    • Get a count of the data in the staging table.

  2. Run a pre-validation script to check data integrity.

    • Count invalid records, if any.

    • Find error codes, if any.

    • Count the valid records in the staging table.

  3. Move the valid records to the Open Interface table.

  4. Calling the initialize function.

  5. Calling the concurrent manager.

  6. Getting the Request status.

  7. Cleaning the staging table.

For what follows we will use the Financial -> General Ledger —> Budget module as a sample. Assume the Oracle Applications OTD is called oraOTD.

  1. Insert data into the staging table:

    oraOTD.getSB_GL_BUDGET_INTERFACE().insert();

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setBUDGET_ENTITY_ID(new java.math.BigDecimal("1000"));

oraOTD.getSB_GL_BUDGET_INTERFACE().setBUDGET_ENTITY_NAME("RP_Opnames");

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setBUDGET_VERSION_ID(new java.math.BigDecimal("1002"));

oraOTD.getSB_GL_BUDGET_INTERFACE().setBUDGET_NAME("CORPORATE 1996");

oraOTD.getSB_GL_BUDGET_INTERFACE().setCURRENCY_CODE("USD");

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setFISCAL_YEAR(new java.math.BigDecimal("1996"));

oraOTD.getSB_GL_BUDGET_INTERFACE().setUPDATE_LOGIC_TYPE("A");

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setSET_OF_BOOKS_ID(new java.math.BigDecimal("1"));

oraOTD.getSB_GL_BUDGET_INTERFACE().setPERIOD_TYPE("Month");

oraOTD.getSB_GL_BUDGET_INTERFACE().setDR_FLAG("Y");

oraOTD.getSB_GL_BUDGET_INTERFACE().setACCOUNT_TYPE("A");

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD1_AMOUNT(new java.math.BigDecimal("100"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD2_AMOUNT(new java.math.BigDecimal("200"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD3_AMOUNT(new java.math.BigDecimal("300"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD4_AMOUNT(new java.math.BigDecimal("400"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD5_AMOUNT(new java.math.BigDecimal("500"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD6_AMOUNT(new java.math.BigDecimal("600"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD7_AMOUNT(new java.math.BigDecimal("700"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD8_AMOUNT(new java.math.BigDecimal("800"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD9_AMOUNT(new java.math.BigDecimal("900"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD10_AMOUNT(new java.math.BigDecimal("1000"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD11_AMOUNT(new java.math.BigDecimal("1100"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setPERIOD12_AMOUNT(new java.math.BigDecimal("1200"));

oraOTD.getSB_GL_BUDGET_INTERFACE().
        setCODE_COMBINATION_ID(new java.math.BigDecimal("17378"));

oraOTD.getSB_GL_BUDGET_INTERFACE().setSEGMENT1("01");

oraOTD.getSB_GL_BUDGET_INTERFACE().setSEGMENT2("760");

oraOTD.getSB_GL_BUDGET_INTERFACE().setSEGMENT3("7420");

oraOTD.getSB_GL_BUDGET_INTERFACE().setSEGMENT4("0000");

oraOTD.getSB_GL_BUDGET_INTERFACE().setSEGMENT5("000");

oraOTD.getSB_GL_BUDGET_INTERFACE().setSB_EWAY_ID(eWayID);

oraOTD.getSB_GL_BUDGET_INTERFACE().setSB_GROUP_ID(GroupID);

oraOTD.getSB_GL_BUDGET_INTERFACE().setSB_OBJECT_ID(ObjectID);

oraOTD.getSB_GL_BUDGET_INTERFACE().insertRow();

    Get a count of the data in the staging table.

    oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_GROUP_ID(GroupID);

oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_OBJECT_ID(ObjectID);

oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_EWAY_ID(eWayID);

oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_CODE("A");

oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().execute();

budget_count = oraOTD.getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().
        getRETURN_VALUE1().intValue();

  2. Run a pre-validation script to check data integrity.

    oraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_VALIDATE_GL_BUDGET_PKG_VALIDATE().setP_GROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_VALIDATE_GL_BUDGET_PKG_VALIDATE().setP_OBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_VALIDATE_GL_BUDGET_PKG_VALIDATE().setP_EWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_VALIDATE_GL_BUDGET_PKG_VALIDATE().execute();

    • Count invalid records, if any.

      OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_GROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_OBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_EWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_CODE( "F" );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().execute();

                    budget_error_count = OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().getRETURN_VALUE1().intValue();

    • Find error codes, if any.

      if (budget_error_count > 0) {

OraOTD.getSB_GL_BUDGET_INTERFACE().
qqgetGET_INVALID_ROWS_GL_BUDGET_INT().setEWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
qqgetGET_INVALID_ROWS_GL_BUDGET_INT().setGROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
qqgetGET_INVALID_ROWS_GL_BUDGET_INT().setOBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
qqgetGET_INVALID_ROWS_GL_BUDGET_INT().executeQuery();

                        if (OraOTD.getSB_GL_BUDGET_INTERFACE().
        getGET_INVALID_ROWS_GL_BUDGET_INT().resultsAvailable()) {

                            while (OraOTD.getSB_GL_BUDGET_INTERFACE().
        getGET_INVALID_ROWS_GL_BUDGET_INT().
        get$GET_INVALID_ROWS_GL_BUDGET_INTResults().next()) {

                                JMS_1.sendText( "     SB_ERROR_CODE: ".
        concat( OraOTD.getSB_GL_BUDGET_INTERFACE().
        getGET_INVALID_ROWS_GL_BUDGET_INT().
        get$GET_INVALID_ROWS_GL_BUDGET_INTResults().
        getSB_ERROR_CODE() ).concat( "     SB_ERROR_MESSAGE: ".
        concat( OraOTD.getSB_GL_BUDGET_INTERFACE().
        getGET_INVALID_ROWS_GL_BUDGET_INT().
        get$GET_INVALID_ROWS_GL_BUDGET_INTResults().getSB_ERROR_MESSAGE() ) ) );

                            }

                        }

                    }

    • Count the valid records in the staging table.

      OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_GROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_OBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_EWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().setP_CODE( "P" );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().execute();

                    budget_count = OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_FN_CNT_GL_BUDGET_INT().getRETURN_VALUE1().intValue();

  3. Move the valid records to the Open Interface table.

    OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_MOV_GL_BUDGET_INT().setP_GROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_MOV_GL_BUDGET_INT().setP_OBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_MOV_GL_BUDGET_INT().setP_EWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_MOV_GL_BUDGET_INT().setP_CODE( "P" );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_MOV_GL_BUDGET_INT().execute();

  4. Calling the initialize function.

    OraOTD.getSB_INITIALIZE_INITIALIZE_PROFILE().setP_ORGID( org_ID );

OraOTD.getSB_INITIALIZE_INITIALIZE_PROFILE().execute();

  5. Calling the concurrent manager.

    OraOTD.getFN_REQUEST_BUDGET().setP_USER_NAME( "OPERATIONS" );

OraOTD.getFN_REQUEST_BUDGET().setP_RESPONSIBILITY( "GENERAL_LEDGER_SUPER_USER" );

OraOTD.getFN_REQUEST_BUDGET().setP_ORGID( org_ID );

OraOTD.getFN_REQUEST_BUDGET().setAPPLICATION( "SQLGL" );

OraOTD.getFN_REQUEST_BUDGET().setPROGRAM( "GLBBSU" );

OraOTD.getFN_REQUEST_BUDGET().setDESCRIPTION( "Budget Spreadsheet Upload" );

OraOTD.getFN_REQUEST_BUDGET().execute();

request_ID = OraOTD.getFN_REQUEST_BUDGET().getRETURN_VALUE1().intValue();

OraOTD.commit();

  6. Getting the Request status.

    if (request_ID > 0) {

    OraOTD.getFN_REQUEST_STATUS().
        setINP_REQUEST_ID( new java.math.BigDecimal( Integer.toString( request_ID ) ) );

    OraOTD.getFN_REQUEST_STATUS().
        setINP_INTERVAL_SEC( new java.math.BigDecimal( "15" ) );

    OraOTD.getFN_REQUEST_STATUS().
        setINP_MAXIMUM_SEC( new java.math.BigDecimal( "30" ) );

OraOTD.getFN_REQUEST_STATUS().execute();

Result_status = OraOTD.getFN_REQUEST_STATUS().getRETURN_VALUE1() );

  7. Cleaning the staging table.

    OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_DEL_GL_BUDGET_INT().setP_GROUP_ID( GroupID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_DEL_GL_BUDGET_INT().setP_OBJECT_ID( ObjectID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_DEL_GL_BUDGET_INT().setP_EWAY_ID( eWayID );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_DEL_GL_BUDGET_INT().setP_CODE( "A" );

OraOTD.getSB_GL_BUDGET_INTERFACE().
        getSB_BUDGET_UTILS_PKG_SP_DEL_GL_BUDGET_INT().execute();