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:

Insert data into the staging table.
- Get a count of the data in the staging table.
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.
Move the valid records to the Open Interface table.
Calling the initialize function.
Calling the concurrent manager.
Getting the Request status.
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.

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();

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();

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();

Calling the initialize function.

OraOTD.getSB_INITIALIZE_INITIALIZE_PROFILE().setP_ORGID( org_ID );

OraOTD.getSB_INITIALIZE_INITIALIZE_PROFILE().execute();

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();

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() );

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();

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