test

Designing with Application Adapters

Building an Oracle Applications Custom Pre-Validation Package

The Sun Oracle Application scripts provided are intended to be used as a template or skeleton. You may need to modify these scripts to satisfy your own environment or requirements. These scripts have been tested successfully in the Sun environment.

This section covers the following information for building a custom Pre-Validation Package:

To view corresponding tables and scripts used for the Pre-Validation packages, refer to the following modules:

Pre-Validation Scripts

For Open Interfaces for which pre-validation scripts have been supplied, the SQL files shipped with the Adapter are sufficient for you to create a working Project. For Open Interfaces for which pre-validation scripts are not currently available, a template XML file (templateXml.xml) is supplied. Use this file to build your own pre-validation package. The procedure for you to follow is given in To Build a Custom Pre-Validation Package for a Non-Supported Open Interface.

Disclaimer

The pre-validation scripts packaged with the Adapter for the supported modules are rudimentary and do not cover all possible scenarios. Since the requirements from Oracle occasionally change and evolve, you may be required to provide additional data and/or parameters to ensure that the Concurrent Manager request completes successfully and without errors. Pre-validation scripts have been tested in 11.5.3 only.

Pre-Validation Procedures

All pre-validation script packages (*_pkg.sql) contain the following procedures.

Table 1–1 Common Procedures

Procedure 

Purpose 

Initialize_Profile

Initializes the table for sharing to avoid running multiple instances by different users. 

TidyUp 

Updates sb_pass_or_fail columns in the staging table from INTERMEDIATE to PASS for records that were not marked as failed by the pre-validation rules. 

Validate 

The main validation procedure which calls the procedures that perform the pre-validations for the Interface Table. 

ProcedureTo Build a Custom Pre-Validation Package for a Non-Supported Open Interface

  1. Modify the Oracle Applications master configuration file in the following location to add a new module:


    <CAPS_Dir>/appserver/userdir/modules/ext/oracleappsadapter/
wizard/configs/Applications.xml

    When adding a new business function, assign the business section a new name and entry for the new open interface. Spaces are acceptable when creating a module name.


    <Module Name=”Module_Name”Definition=”moduleDefinition.xml”> </Module>

    Notice that the name attribute is an English description which appears in the OTD wizard where the definition attribute locates the open interface XML definition file (this attribute cannot have spaces).

  2. Copy and rename the template.xml file from the path to the appropriate directory:


    <CAPS_Dir>/appserver/userdir/modules/ext/oracleappsadapter/
wizard/configs/templateXml.xml

    Appropriate directory:


    <CAPS_Dir>/appserver/userdir/modules/ext/oracleappsadapter/wizard/
configs/business_function_name/moduleDefinition.xml

    where business_function_name is the name attribute value (for example, either Manufacturing or Financial).

    While the template XML file does not have an error package or error table entries, you can look at any existing module to create these entries. If you choose to modify the common sb_install.sql file to append more errors for the new module, the SB_ERRORS table needs to be removed from the database so the wizard can recreate it with new error values the next time the wizard is run.

  3. Replace the path with the correct open interface name.


    <%%ORACLE_INTERFACE_NAME%%>

    Do not use spaces when creating an ORACLE_INTERFACE_NAME.

  4. Replace the path with the correct business suite name.


    <%%ORACLE_APPLICATION_SUITE NAME%%>

  5. Replace the path with the correct module name.


    <%%MODULE_NAME%%>

    Do not use spaces when creating a MODULE_NAME.

  6. The initialize and request_status scripts are already specified in the XML file. If you do not need these, simply remove them from the XML file. If you need to customize these scripts:

    1. Copy the SQL file to the desired directory.

    2. Rename the file—the package name must be the same as the SQL file name (which is true for all entries in this XML file).

    3. Modify the scripts as needed for your own implementation.

    4. In the XML file, refer to these new customized SQL files instead of the shipped SQL script.

    Note –

    Do not change either the Initialize_Profile stored procedure name, nor its location as the entry point for the initialization packages.

  7. Replace <%%VALIDATION_PACKAGE_SQL_FILE_NAME%%> with the correct path and file name for the validation package. This validation package file name must be sql/business_function_name/<%%MODULE_NAME%%>/ <%%ORACLE_INTERFACE_NAME%%>/sb_validate_interfaceNameAbbreviation_pkg.sql, this path is relative from <CAPS_Dir>/edesigner/userdir/modules/ext/oracleappsadapter/wizard/

    This package contains the VALIDATE procedure which eventually invokes all the VALIDATE procedures for all the tables.

  8. Replace <%%UTILITY_PACKAGE_SQL_FILE_NAME%%> with the correct path and file name for the utility package. This utility package file name must be sql/business_function_name/<%%MODULE_NAME%%>/ <%%ORACLE_INTERFACE_NAME%%>/sb_interfaceNameAbbreviation_utils_pkg.sql, this path is relative from <CAPS_Dir>/edesigner/userdir/modules/ext/oracleappsadapter/wizard/

    This package contains the procedures to move, delete, and function count all interface tables and the OTD level.

  9. Replace <%%CONCURRENT_MANAGER_FUNCTION_SQL_FILE_NAME%%> with the correct path and file name for the concurrent file. This concurrent manager file name must be sql/business_function_name/<%%MODULE_NAME%%>/ <%%ORACLE_INTERFACE_NAME%%>/fn_request_<%%ORACLE_INTERFACE_NAME%%>.sql relative from <CAPS_Dir>/edesigner/userdir/modules/ext/oracleappsadapter/wizard/

    This function eventually invokes the concurrent manager.

  10. Replace <%%INTERFACE_TABLE_NAME%%> with the correct interface table name. Create more entries as needed for each interface table, either in the same level or as a child. For example, in manufacturing, order import has two interface tables with child-parent relationships, where as item import has two interface tables with sibling relationships.

  11. Replace <%%INTERFACE_TABLE_VALIDATION_PACKAGE_SQL_FILE_NAME%%> with the correct path and file name for the validation package for this particular interface table. This validation package file name must be sql/business_function_name/<%%MDULE_NAME%%>/<%%ORACLE_INTERFACE_NAME%%>/sb_validate_interface_TablenameAbbreviation_pkg.sql

    This path is relative from <CAPS_Dir>/edesigner/userdir/modules/ext/oracleappsadapter/wizard/.

    This package has the VALIDATE procedure for these interface tables.

  12. Restart the Java CAPS IDE so the OTD Wizard can load the new changes.

  13. Create and compile the SQL scripts defined in the XML definition files. When creating SQL files, make sure that they begin with CREATE AND REPLACE.

    Note –

    The new Applications.xml, moduleDefinition.xml, and SQL scripts are stored only in the Java CAPS IDE directory. It is recommended to back them up periodically.

Concurrent Manager Request Function

The default SQL script template for Concurrent Manager Request has 100 identical input parameters for FND_REQUEST.SUBMIT_REQUEST. If you need to customize the input parameters for your generated script, the following tags can be used to specify the input parameters.


<Concurrent_Manager>path/file_name.sql</Concurrent_Manager>
<Request_Param>parameter1</Request_Param>
<Request_Param>parameter2</Request_Param>
<Request_Param>parameter3</Request_Param>

Template DTD

The Data Type Definition (DTD) associated with the template XML file is shown:


<!ELEMENT OPEN_INTERFACE (Initialize_Script?, 
Validation_Script?, Utility_Script, Concurrent_Manager, 
Request_Param*, Concurrent_Manager_Status?, Interface_Table+, 
Error_Handle?, 
Pre_Required_Script*)>
<!--  Open interface name -->
<!ATTLIST OPEN_INTERFACE
    Name CDATA #REQUIRED
>
<!-- Oracle Manufacturing Version -->
<!ATTLIST OPEN_INTERFACE
    Version CDATA #REQUIRED
>
<!-- Application Suite  -->
<!ATTLIST OPEN_INTERFACE
    Application-Suite CDATA #REQUIRED
>
<!-- Module Name -->
<!ATTLIST OPEN_INTERFACE
    Module CDATA #REQUIRED
>
<!-- utiltiy store procedure name at root level, this attribute is optional
   if this attribute is specified, it overrides the default
   name convention, which is derived from Name attribute of OPEN_INTERFACE
-->
<!ATTLIST OPEN_INTERFACE
    Util_Name CDATA #IMPLIED
>
<!-- relative path to the sql script file for initialize  package-->
<!ELEMENT Initialize_Script (#PCDATA)>
<!-- relative path to the sql script file for validation package -->
<!ELEMENT Validation_Script (#PCDATA)>
<!-- relative path to the sql script file for Utility package -->
<!ELEMENT Utility_Script (#PCDATA)>
<!-- relative path to the sql script file for concurrent manager 
request function -->
<!ELEMENT Concurrent_Manager (#PCDATA)>
<!-- parameter description for concurrent manager request function  -->
<!ELEMENT Request_Param (#PCDATA)>
<!-- relative path to the sql script file for function to retrieve 
concurrent manager request status -->
<!ELEMENT Concurrent_Manager_Status (#PCDATA)>
<!-- Oracle Open Interface table definition -->
<!ELEMENT Interface_Table (Validation_Script?, Interface_Table*)>
<!-- name for the oracle open interface table -->
<!ATTLIST Interface_Table
    Name CDATA #REQUIRED
>
<!-- name for SB staging table, this attribute is optional
    if this attribute is set, the program directly uses
    its value as the SB staging table for this specific
    interface
-->
<!ATTLIST Interface_Table
    SB_Name CDATA #IMPLIED
>
<!-- utility store procedure name at specific interface level, 
this attribute is optional
   if this attribute is specified, it overrides the default name convention, 
which is
   derived from the specific interface name
-->
<!ATTLIST Interface_Table
    Util_Name CDATA #IMPLIED
>
<!-- Error handling definition -->
<!ELEMENT Error_Handle (Error_Table?, Error_PKG?)>
<!-- relative path to the sql script for creating error table -->
<!ELEMENT Error_Table (#PCDATA)>
<!-- relative path to the sql script for error handling -->
<!ELEMENT Error_PKG (#PCDATA)>
<!-- relative path to the sql script that needs to be compiled in order for 
other stored procedure to be compiled successfully -->

Naming Conventions for the Oracle Interface

The specific name for the Open Interface, which is specified in the XML file.

Short Table Name

The name used for the generated utility stored procedures, for each interface table. It is derived from the corresponding Oracle Interface Table name as follows:

If the Oracle Interface Table name is longer than 23 characters, replace INTERFACE or IFACE_ALL with INT to form the short table name.

(Since there is a limitation of 30 characters placed on the function/stored procedure name in Oracle, the Open Interface name specified in the XML file must be no more than 23 characters).

Stored Procedures

The utility stored procedure names are derived from the Short Table name, according to the following convention:

Sb_XXX_<SHORT_TABLE_NAME>

Staging Table Node

The name of the staging table node is derived from the corresponding Oracle Interface Table name by adding the prefix SB_ and truncating it to 30 characters, if necessary.

SQL File Name

If the SQL file is used to create a packaged stored procedure, the file name must be the same as the package name. The file name is case sensitive and must be less than or equal to 30 character due to the rules of Oracle procedure/function name.

Concurrent Manager Request Function

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