Search Results capture_index_definitions

Overview

AS_UTILITY_PVT is a private PL/SQL utility package in the Oracle E-Business Suite Applications (APPS) schema that provides shared internal services for the Oracle Sales (AS) product family and its supporting infrastructure. Its internal source header (asxvutlb.pls, version 120.2) traces the package to 1999, when it was created as AS_UTILITY and subsequently converted to and from the JTF_PLSQL_API naming convention. The package exposes a collection of low-level helper routines rather than business transactions: standardized API entry/exit logging, exception translation and message publication, dynamic ORDER BY and flexfield WHERE-clause generation, bind variable construction, diagnostic file output, and index definition capture.

The presence of the EXECUTE_IND routine, together with the CAPTURE_INDEX_DEFINITIONS function and the indexRec record type modeled on DBA_INDEXES, indicates that a principal responsibility of this package is to reconstruct and re-execute index DDL, most likely as part of concurrent-program-driven index maintenance, statistics, or performance diagnostic activity within the Sales application. The package is classified as PVT, meaning it is not a public API and Oracle does not guarantee its signature across releases, although the ETRM 12.2.2 metadata documents 26 procedures and functions. With 108 dependent packages, AS_UTILITY_PVT is a heavily reused internal building block and changes to it carry broad impact within the AS schema.

Key Procedures and Functions

The documented entry points group into several functional areas:

• API framework: START_API and END_API bracket the execution of internal APIs, establishing context and emitting standard logging; HANDLE_EXCEPTIONS consolidates exception processing, including the message-count-and-get invocation noted in the source history; GET_MESSAGES retrieves messages from the FND message stack; SET_MESSAGE places a message onto that stack.
• Dynamic SQL construction: TRANSLATE_ORDERBY converts a logical sort token into a valid ORDER BY fragment; GEN_FLEXFIELD_WHERE and BIND_FLEXFIELD_WHERE generate a key flexfield WHERE clause and its associated bind variables.
• Diagnostics and logging: DEBUG_MESSAGE, FILE_DEBUG, ENABLE_FILE_DEBUG, and DISABLE_FILE_DEBUG provide control over file-based tracing. Internally, TRANSLATE_LOG_LEVEL maps legacy FND_MSG_PUB severity codes to FND_LOG levels, and GET_DEGREE_PARALLELISM returns the value of the AS_DEGREE_PARALLELISM profile option, defaulting to 4.
• Index handling: CAPTURE_INDEX_DEFINITIONS collects index metadata, and EXECUTE_IND re-executes the captured index DDL (the indexRec record stores index name, owner, table, tablespace, storage and parallelism attributes, and the SQL text held in AS_CONC_REQUEST_MESSAGES.INDEX_TEXT).

Tables Accessed

The package reads data dictionary views DBA_INDEXES, DBA_IND_COLUMNS, and DBA_IND_EXPRESSIONS to capture index structure and expression-based columns. It references AS_CONC_REQUEST_MESSAGES and AS_CONC_REQUEST_MESSAGES_S, where index DDL text is persisted for later execution, and FND_NEW_MESSAGES for message publication. Dynamic execution relies on DBMS_SQL, and file debugging uses UTL_FILE with PLITBLM for output formatting; DBMS_TRANSACTION supports transactional control during index operations.

Usage Notes

AS_UTILITY_PVT is invoked internally by the 108 dependent packages rather than by end users directly. It is typically reached from concurrent programs and internal API layers that require standardized logging, exception handling, and dynamic SQL assembly. The EXECUTE_IND routine executes captured index DDL and should be run only through the supported concurrent process or calling API, never ad hoc. Because the package is PVT, customizations should not call it directly; Oracle may alter signatures between 12.1.1 and 12.2.2 patch levels.