Designing: Pre-Validation Procedures

Pre-Validation Procedures

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

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.

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

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).
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.
Replace the path with the correct open interface name.
```
<%%ORACLE_INTERFACE_NAME%%>
```
Do not use spaces when creating an ORACLE_INTERFACE_NAME.
Replace the path with the correct business suite name.
```
<%%ORACLE_APPLICATION_SUITE NAME%%>
```
Replace the path with the correct module name.
```
<%%MODULE_NAME%%>
```
Do not use spaces when creating a MODULE_NAME.
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.
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.
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.
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.
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.
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.
Restart the Java CAPS IDE so the OTD Wizard can load the new changes.
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.

Installing Upgrading Designing Configuring Deploying Monitoring Administering Troubleshooting Reference JBI Components
Close Print View
Designing: Pre-Validation Procedures