Search Results bind_flexfield_where

Overview

AS_UTILITY_PVT is a public utility API owned by APPS and classified as PVT (private) in Oracle EBS 12.1.1 and 12.2.2. The package header identifies it as "a public utility API developed from Sales Core group," and its header comment carries the source control identifier $Header: asxvutls.pls 120.1 2005/07/07 23:49:25 appldev ship $. It was originally created on 08/11/99 as AS_UTILITY and subsequently reworked, most notably a 09/09/99 change to the JTF_PLSQL_API framework and a 11/12/02 modification to the Debug_Message procedure under Bug#2659173.

The package supplies the shared plumbing that Oracle Sales-family APIs depend on: consistent API start and end bracketing, standardized exception handling, message stack retrieval, debugging output, ORDER BY clause translation, and dynamic flexfield predicate generation. Its central business role is to let other PL/SQL APIs construct and execute dynamic queries against descriptive flexfield segments without hard-coding flexfield WHERE clauses, and to normalize error reporting back to a calling API's message list. The ETRM metadata records 26 documented procedures and functions and notes that the package is referenced by 108 other packages, confirming its position as foundational infrastructure rather than an end-user feature.

Key Procedures and Functions

The documented entry points cover five functional areas:

• API lifecycle: START_API and END_API bracket an API call, establishing the savepoint and status conventions used by the Oracle API standard.
• Error handling: HANDLE_EXCEPTIONS rolls back to the savepoint, processes expected, unexpected, and other exceptions, appends an error message to the API message list, and returns an error status. GET_MESSAGES and SET_MESSAGE manage the message stack, while SET_MESSAGE places a message onto the list for later retrieval.
• Flexfield support: GEN_FLEXFIELD_WHERE and BIND_FLEXFIELD_WHERE form the pair most relevant to the search term bind_flexfield_where. GEN_FLEXFIELD_WHERE generates the WHERE fragment for flexfield segment criteria; BIND_FLEXFIELD_WHERE binds the corresponding values so the generated predicate can be executed safely through dynamic SQL.
• Query and execution helpers: TRANSLATE_ORDERBY converts an abstract sort specification into a usable ORDER BY clause; GET_DEGREE_PARALLELISM determines an appropriate degree of parallelism; CAPTURE_INDEX_DEFINITIONS and EXECUTE_IND gather index metadata and execute index-related DDL/DML.
• Diagnostics: DEBUG_MESSAGE emits diagnostic text, with FILE_DEBUG, ENABLE_FILE_DEBUG, and DISABLE_FILE_DEBUG controlling file-based debug output.

Public constants include G_PVT, G_PUB, G_EXC_OTHERS, G_CREATE, and G_UPDATE. The header documents the canonical exception block pattern, in which a WHEN FND_API.G_EXC_ERROR handler calls AS_UTILITY_PVT.HANDLE_EXCEPTIONS with P_API_NAME, P_PGK_NAME, P_EXCEPTION_LEVEL, P_PACKAGE_TYPE, and the X_MSG_COUNT, X_MSG_DATA, and X_RETURN_STATUS out parameters.

Tables Accessed

The package reads and writes the following objects through APPS synonyms:

• AS_CONC_REQUEST_MESSAGES and AS_CONC_REQUEST_MESSAGES_S — storage for concurrent request message text, used when an API runs under a concurrent program and diagnostics must be associated with a request.
• FND_NEW_MESSAGES — the Oracle Application Object Library message repository consulted by message-setting and retrieval logic.
• DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS — data dictionary views read by CAPTURE_INDEX_DEFINITIONS to capture existing index structure, including function-based index expressions.
• PLITBLM — the PL/SQL table used for message and list handling.
• DBMS_SQL and DBMS_TRANSACTION — supplied packages used for dynamic SQL parsing/execution and transaction control such as savepoint management.
• UTL_FILE — file I/O for the file-debug procedures.

Usage Notes

AS_UTILITY_PVT is invoked from other PL/SQL packages rather than from Oracle Forms directly. The 108 dependent packages call it to standardize exception blocks, generate flexfield WHERE clauses with bound values, and emit debug output. Custom code following the Oracle API standard should call START_API and END_API around the body and route all exception handlers into HANDLE_EXCEPTIONS. When building dynamic flexfield queries, GEN_FLEXFIELD_WHERE and BIND_FLEXFIELD_WHERE should be used together so that generated predicates remain bind-variable safe. Because the package is declared AUTHID CURRENT_USER, privileges are evaluated as the invoking schema; callers must therefore possess the necessary grants on the underlying dictionary views and AS_ and FND_ objects. File debugging requires a valid UTL_FILE directory and server-side access.