Exposed Oracle Applications OTD Nodes

Staging Table Node

This node represents the Sun Staging Table created inside the Oracle database. All columns in the table are exposed, and can be dragged and dropped in the Java Collaboration. The node has a name of the form SB_<Oracle_Interface_Table_name> having a maximum length of 30 characters.

The Staging Table is created from the Interface Table with the following six extra fields used to support the pre-validation process:

• SB_EWAY_ID

• SB_GROUP_ID

• SB_OBJECT_ID

• SB_PASS_OR_FAIL

• SB_ERROR_CODE

• SB_ERROR_MESSAGE

All of the ID fields (the first three fields shown above) are used for pre-validation purposes within CAPS.

COUNT

Description

Stored procedures for both the OTD level and the interface tables level are defined in the utility package.

• If it is located at the OTD root level, the data is counted from all Staging Tables.

• If it is located at the Staging Table level, the data is counted only for that specific Staging Table.

Parameters

Depends upon specific implementation. Typically, it contains at least four input VARCHAR parameters corresponding to:

• sb_eway_id

• sb_group_id

• sb_object_id

  ---

  Note –

  If you do not assign a value (including the null value) to the above parameters, the procedure acts on all associated records.

  ---

• sb_pass_or_fail

  This parameter accepts the following values:

  • P– records that have passed

  • F– for records that have failed

  • I– all records

Requirements

The stored procedure name is derived from the Open Interface name or the Staging Table name, according to the following convention:

• At the root level:

  If the OPEN_INTERFACE tab has a Util_Name attribute, then this value is used: FN_CNT_<UTILNAME>. Otherwise, the value of the attribute Name is used: FN_CNT_<Open_Interface_Name>.

  For example:

  • Customer Item: FN_CNT_CUSTITEMS

  • Item Import: FN_CNT_ITEM_IMPORT

  At the staging table level:

  If the Interface_Table tag has a Util_Name attribute, then this value is used: FN_CNT_<UTILNAME>. Otherwise, the short name of the Name attribute is used: FN_CNT_<Short_Table_Name>.

  For example:

  • Customer Item: FN_CNT_MTL_CI_INTERFACE_INT

  • Item Import: FN_CNT_MTL_SYSTEM_ITEMS_INT

DELETE

Description

Stored procedures for both OTD level and interface level are defined in the utility package.

• If it is located at the OTD root level, the data from all Staging Tables is deleted.

• If it is located at the Staging Table level, only the data for that specific Staging Table is deleted.

Parameters

Depends upon the specific implementation. Typically, it contains at least four input VARCHAR parameters corresponding to:

• sb_eway_id

• sb_group_id

• sb_object_id

  ---

  Note –

  If you do not assign a value (including the null value) to the above parameters, the procedure acts on all associated records.

  ---

• sb_pass_or_fail

  This parameter accepts the following values:

  • P– records that have passed

  • F– for records that have failed

  • I– all records

Requirements

The stored procedure name is derived from the Open Interface name or the Staging Table name, according to the following conventions:

• At the root level:

  If the OPEN_INTERFACE tab has a Util_Name attribute, then this value is used: SP_DEL<UTILNAME>. Otherwise, the value of the attribute Name is used: SP_DEL_<Open_Interface_Name>.

  For example:

  • Customer Item: SP_DEL_CUSTITEMS

  • Item Import: SP_DEL_ITEM_IMPORT

  At the staging table level:

  If the Interface_Table tag has a Util_Name attribute, then this value is used: SP_DEL_<UTILNAME>. Otherwise, the short name of the Name attribute is used: SP_DEL_<Short_Table_Name>.

  For example:

  • Customer Item: SP_DEL_MTL_CI_INTERFACE_INT

  • Item Import: SP_DEL_MTL_SYSTEM_ITEMS_INT

INITIALIZE

Description

This optional packaged stored procedure is used to initialize the user’s profile for Oracle Applications.

Parameters

Depends upon the specific implementation. Typically, it accepts the Organization ID as a parameter.

Requirements

Inside the script package, this stored procedure must have the name Initialize_Profile.

MOVE

Description

Stored procedures for both OTD level and interface tables level are defined in the utility package.

• If it is located at the OTD root level, it copies the data from all Sun Staging Tables to the corresponding Oracle Interface Tables.

• If it is located at the Interface Table level, then the data for only that specific Staging Table is copied to its corresponding Open Interface Table.

This procedure acts only on records with the ID values specified.

Parameters

Depends upon the specific implementation. Typically, it contains at least four input VARCHAR parameters corresponding to:

• sb_eway_id

• sb_group_id

• sb_object_id

• sb_pass_or_fail

  This parameter accepts the following values:

  • P– records that have passed

  • F– for records that have failed

  • I– all records

Requirements

The stored procedure name is derived from the Open Interface name or the Staging Table name, according to the following convention:

• At the root level:

  If the OPEN_INTERFACE tab has a Util_Name attribute, then this value is used: SP_MOV<UTILNAME>. Otherwise, the value of the attribute Name is used: SP_MOV_<Open_Interface_Name>.

  For example:

  • Customer Item: SP_MOV_CUSTITEMS

  • Item Import: SP_MOV_ITEM_IMPORT

  At the staging table level:

  If the Interface_Table tag has a Util_Name attribute, then this value is used: SP_MOV_<UTILNAME>. Otherwise, the short name of the Name attribute is used: SP_MOV_<Short_Table_Name>.

  For example:

  • Customer Item: SP_MOV_MTL_CI_INTERFACE_INT

  • Item Import: SP_MOV_MTL_SYSTEM_ITEMS_INT

REQUEST

Description

Concurrent Manager request function. This function is used to submit the concurrent management request to Oracle Applications.

Parameters

Depends upon specific implementation.

Requirements

The function name is derived from the Open Interface name specified in the SML, and has the form FN_REQUEST_<ORACLE_INTERFACE_NAME>.

REQUEST_STATUS

Description

Function used to retrieve the status of the Concurrent Manager request.

Parameters

• INp_request_id IN NUMBER

  Request Id for the concurrent Manager; basically, the return value from REQUEST.

• INp_interval_sec IN NUMBER

  The interval in seconds for the program to query for the result of a Concurrent Manager request.

• INp_maximum_sec IN NUMBER

  The maximum allowed interval (in seconds) for the program to time out. This parameter must have a non-zero value.

• OUTp_detailed_status OUT VARCHAR2

  Output parameter having the detailed description of the concurrent request.

Requirements

In order for Request_Status to correctly retrieve the Concurrent Manager request, you must call commit after the Request stored procedure call; otherwise, Request_Status always returns Pending status after a time-out.

VALIDATE

Description

This packaged stored procedure is used to perform the pre-validation of data in the Staging Table.

• If it is located at the OTD root level, the data in all Staging Tables is validated.

• If it is located at the Staging Table level, only the data in that specific Staging Table is validated.

Parameters

Depends upon specific implementation. By default, it contains three input VARCHAR parameters corresponding to:

• sb_eway_id

• sb_group_id

• sb_object_id

• sb_pass_or_fail

Requirements

Inside the script package, this stored procedure must have the name VALIDATE.