Custom API

For the Custom API activity, the metadata that defines the package is not contained in Oracle Integration Repository. The plug-in instead uses Oracle Database Dictionary to get detailed information about this API. If you create a Custom API and register it into the Integration Repository, it becomes a regular Oracle E-Business Suite API and is treated as such.

Note: A comparison between Oracle API and Custom API is provided in Oracle API and Custom API Comparison.

The Custom API activity provides the following functions:

At design time
- Loads the PL/SQL procedure information from Oracle Database Dictionary.
- Saves this information to a file.
At run time
- Loads the PL/SQL procedure information from the file.
- Generates a calling SQL statement.
- Calls the PL/SQL procedure.

Note: Custom API can be used in Oracle E-Business Suite 12.0.x, 12.1.x, and 12.2.x.

General

In the General tab, you can establish a connection to an Oracle E-Business Suite server, specify the API package name and procedure name, and set the timeout time for calling a procedure.

The General tab contains the following fields:

Field	Module Property?	Description
Name	No	The name of the resource. The default is CustomAPI.
Shared Connection	Yes	A shared configuration resource containing the Oracle E-Business Suite connection information. To select a shared connection resource, click the Choose/Create Default Resource icon. All usable connections are listed in the opened dialog. Select one to apply to your current activity. If no matching connection resource is found, click Create Shared Resource to create one. See Oracle E-Business Suite Connection for more details. Note: For Oracle E-Business Suite 12.2.x, the apps user is used to run the Custom API activities at run time; therefore, before running the Custom API activity, ensure that the apps user name that you specified in the Shared Connection is correct. For Oracle E-Business Suite 12.1.x and 12.0.x, the plug-in user is used to run the Custom API activities at run time; therefore, before running the Custom API activity, ensure that the plug-in user name that you specified in the Shared Connection is correct.
API Package Name	No	A business service interface that is exposed by Oracle E-Business Suite. Click Search Package or the text field. Then, enter a part of the package name in the opened dialog or leave the text field empty, and click Search. Select a package in the opened dialog, and click OK.
API Procedure Name	No	A procedure from the API Package. Click Search Procedure or the text field. Then, enter a part of the procedure name in the opened dialog or leave the text field empty, and click Search. Select a procedure in the opened dialog, and click OK.
Wrapper Package Name	No	The name given to a package of wrapper procedures or functions. This field is displayed only when a wrapper package is required. Click Search Package or the text field. Then, enter a part of the package name in the opened dialog or leave the text field empty, and click Search. Select a package in the opened dialog, and click OK. Some business objects in the Oracle database cannot be accessed through the Oracle JDBC driver directly. In this case, you must create the wrapper package first by using Oracle JPublisher. See Introduction to Oracle Database JPublisher for details about how to create a wrapper package by using JPublisher.
Wrapper Procedure Name	No	The name given to a wrapper procedure or function that is used to delegate an API procedure or function. This field is displayed only when a wrapper procedure or function is required. Click Search Procedure or the text field. Then, enter a part of the procedure name in the opened dialog or leave the text field empty, and click Search. Select a procedure in the opened dialog, and click OK.
Call Procedure Timeout(s)	Yes	The maximum waiting time for calling the procedure. If the procedure call exceeds this limit, the attempt is stopped. The default is 60. Entering 0 results in an unlimited waiting time.
Generate SQL Script	No	After configuring the Custom API activity, click Generate SQL Script. The scripts to be generated are displayed in the opened dialog. Click Generate, and the following two SQL scripts are created in the `TIBCO_HOME`\bw\palettes\oracleebs\`version_number`\sql directory: `ProcessName`_`ActivityName`_`APIPackageName`.`APIProcedureName`.sql `ProcessName`_`ActivityName`_`APIPackageName`.`APIProcedureName`_undo.sql Run the `ProcessName`_`ActivityName`_`APIPackageName`.`APIProcedureName`.sql script in the connected Oracle database before starting the process. For Oracle E-Business Suite 12.2.x, the plug-in uses the apps user to run the Custom API activity at run time; therefore, you do not have to generate and run the SQL scripts. For Oracle E-Business Suite 12.1.x and 12.0.x, the plug-in uses the plug-in user to run the Custom API activity at run time; therefore, you must generate and run the `ProcessName`_`ActivityName`_`APIPackageName`.`APIProcedureName`.sql script to grant privileges to the plug-in user so that the plug-in user can access the objects being configured. The plug-in uses the apps user entered in the Oracle E-Business Suite Connection resource to get the information to generate this script. After this script is executed, the plug-in user can access the tables and procedures configured for the activity. The script with the `_undo` suffix reverses the granted privileges and undoes the changes to the database. With this operation, you can cleanse the privileges for objects that are no longer used by obsolete or unwanted activities.

Description

In the Description tab, you can enter a short description for the Custom API activity.

Prerequisite

The Prerequisite tab is used to call some Set Client Info and Initialization procedures before calling the main procedure selected in the General tab. You can add new procedure calls and their corresponding package information in the Prerequisite tab. When the procedure must be wrapped, you must enter a wrapper package, and select a procedure for the wrapper package.

Note: If the procedure name for the wrapper package contains a single dollar sign ($), the $ in the procedure name is converted to a hyphen (-) in the Input and Output tabs.

PLSQL API Details

In the PLSQL API Details tab, you can view the details of the API procedure and wrapper procedure (if wrapper procedure exists), which are configured in the General tab. The details are listed in the Parameter Name, Parameter Type, and Parameter Direction columns.

The following figure shows the PLSQL API Details tab:

If the API procedure contains parameters of PLSQL data types, such as PLSQL record type, PLSQL table type which is defined in the PLSQL package, and BOOLEAN type, the plug-in requires the related wrapper package and procedure generated from Oracle JPublisher. You must execute the wrapper SQL generated by JPublisher under the apps user.

The Parameter Direction column shows whether the parameter is an input, output, or both for the API procedure.

Input

In the Input tab, you can view the input data for the Custom API activity. All parameters of the API procedure or wrapper procedure with the Parameter Direction IN or IN/OUT in the PLSQL API Details tab are listed in the CustomAPI-input column.

Note: The parameters listed in the CustomAPI-input column can be configured manually or automatically by defining a module property. For how to define module properties, see "Using Process and Module Properties" in TIBCO ActiveMatrix BusinessWorks Samples.

Output

In the Output tab, the Arguments node lists all parameters of the API procedure or wrapper procedure with the Parameter Direction OUT or IN/OUT in the PLSQL API Details tab, and the Error_Messages node lists the FND (Oracle Application Object Library) error messages when the API encounters errors in the Oracle E-Business Suite system.

Fault

In the Fault tab, you can find the error messages of the Custom API activity.

See Plug-in Error Codes for more information about error messages and corrective actions to take.

The Fault tab lists the following error messages:

Fault	Thrown When ...
`OracleEBSPaletteAQConnectionException`	The AQ Connection does not initialize.
`OracleEBSPaletteConnectionNotFoundException`	The connection cannot be created.
`OracleEBSPaletteSQLException`	An SQL Exception occurs.
`OracleEBSPaletteTimeoutException`	Calling the procedure for PLSQL API.