Search Results load_alternate_lookup

Overview

OTAFRUDT is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports Oracle Training Administration (OTA) and the broader Oracle Learning Management (OLM) product family. Its central purpose is to manage the metadata that defines user-defined, configurable data structures—commonly referred to as "flexfield-style" user tables, columns, rows, and lookup-driven value sets. These structures are stored in the PAY_USER_* family of tables and provide the framework by which administrators extend the standard application data model without database-level schema changes.

Functionally, the package serves as a low-level utility for programmatically creating and populating the user-table definition records. Whereas the standard application user interface (typically the "Define User Tables," "Define Columns," and "Define Rows" forms) allows interactive maintenance, OTAFRUDT exposes the same operations as callable PL/SQL APIs so custom code, migration scripts, or concurrent processes can create the same metadata in bulk or in automatic fashion. This is particularly relevant for the 12.1.1 and 12.2.2 releases, where such extensibility continues to rely on the underlying PAY_USER_COLUMNS, PAY_USER_ROWS, and PAY_USER_TABLES entities.

Key Procedures and Functions

The package exposes six documented procedures. Care should be taken not to confuse or corrupt argument ordering, since several procedures share similar parameter names distinguished only by suffixes.

• insert_table_data — Inserts data into a user table definition. It accepts the business group, legislation code, application identifier, range-or-match flag, user key units, user table name, and row title.
• create_table — Creates a new user table definition. It takes the business group, application identifier, range-or-match flag, user key units, user table name, and row title.
• create_column — Adds a column to an existing user table. It receives the business group, user table name, and user column name.
• create_row — Adds a row (value) to an existing user column. Parameters include business group, table name, column name, row low range or name, display sequence, and value.
• create_from_lookup — Derives a user column definition from an existing Oracle lookup type. It takes the business group, required defaults flag, default value, lookup type, user column name, and user key units.
• load_alternate_lookup — Loads alternate lookup values associated with a given business group.

Tables Accessed

The package reads and writes the core metadata tables that describe user-defined structures. The primary entities are:

• PAY_USER_TABLES — The header definition of each user table.
• PAY_USER_COLUMNS — The column definitions belonging to a user table.
• PAY_USER_COLUMN_INSTANCES_F — Effective-dated instances of user columns.
• PAY_USER_ROWS_F and PAY_USER_ROWS_S — The date-tracked row (value) definitions and their translated/secure counterparts.
• DUAL — Used for simple single-row evaluations or sequence lookups within the package logic.

These accesses are performed through APPS synonyms, meaning the package relies on the standard synonym layer and the privileges granted to APPS rather than fully qualified schema references.

Usage Notes

OTAFRUDT is a pl/sql API intended for programmatic invocation rather than direct end-user execution. It is typically called from custom PL/SQL scripts, Oracle Forms-based configuration screens, or concurrent program wrappers that manage user-table metadata. The package is referenced by no other packages (reference count is zero), so it is treated as a standalone utility with no downstream dependents.

When invoking create_column, the caller must ensure the target user table already exists and that the business group and column name are valid. Because the procedure only manages metadata, the actual underlying physical storage is created elsewhere by the application's dynamic table management. Administrators should also be aware that changes to PAY_USER_* records can affect existing flexfield data validation and reporting. Always operate within the correct business group context and back up affected definitions before running bulk creation routines. The package header is version 120.1 with a 2005 copyright header, and no documented commit behavior is described, so callers should manage transaction control explicitly.