Search Results csm_query_pub

Overview

CSM_QUERY_PUB is a public PL/SQL package owned by the APPS schema that supports Oracle EBS's query framework used across Customer Service (CSM) and related modules. It exposes a set of procedures that allow callers to persist query definitions, manage query instances, and capture execution results. In Oracle EBS 12.1.1 and 12.2.2, packages with the _PUB suffix are intended as the supported API surface: they encapsulate business logic, validate inputs, and insulate callers from changes to underlying tables. CSM_QUERY_PUB conforms to this convention and is classified as a public API in the ETRM registry with a status of VALID. Its role is to provide the stored, reusable query definitions and results that customer service agents and administrators interact with through the Query Manager-style functionality and related forms. Because the package is documented with the FND_API dependency, its routines follow the standard EBS API pattern, including error handling via the FND message stack.

Key Procedures and Functions

The ETRM metadata documents seven callable routines, all procedures:

• INSERT_QUERY — Creates a new query definition, persisting the query header and associated criteria supplied by the caller.
• UPDATE_QUERY — Modifies an existing query definition, allowing changes to the stored query attributes.
• DELETE_QUERY — Removes a query definition previously created through the package API.
• EXECUTE_QUERY — Executes a saved query definition and produces the result set associated with it.
• INSERT_INSTANCE — Records an instance of a query, capturing the context of a specific execution or run of a defined query.
• DELETE_INSTANCE — Removes a stored query instance.
• INSERT_RESULT — Persists individual results generated by EXECUTE_QUERY, linking outcome rows to their parent query or instance.

Parameter lists are not enumerated in the documented metadata and therefore are not reproduced here. The procedures collectively implement the full lifecycle of a query: definition, execution, and result storage.

Tables Accessed

The ETRM excerpt does not enumerate the base tables read or written by CSM_QUERY_PUB. However, the dependency list identifies the following supporting PL/SQL types and utilities:

• APPS.CSM_DATE_LIST — a collection type used to pass date values, indicating that query criteria or results may include date ranges.
• APPS.CSM_INTEGER_LIST — a collection type for numeric values, used for integer-based query parameters.
• APPS.CSM_VARCHAR_LIST — a collection type for character values, used for text-based query parameters.
• FND_API — the standard Oracle Application Object Library API utility, supporting error and message handling.
• SYS.STANDARD — the Oracle-supplied standard package.

The presence of the three CSM collection types indicates the package uses array-style parameter passing for query criteria and results, a common pattern for queries with variable numbers of columns or filter values. The package is also self-referencing, which is typical of public API wrappers. Because the ETRM dependency section does not name the base CSM tables, callers should not assume any table structure beyond what is exposed through these APIs.

Usage Notes

CSM_QUERY_PUB is referenced by no other EBS packages according to the ETRM registry, meaning it is invoked directly by external clients rather than being called internally by another API. Typical invocation points include Oracle Forms event handlers or buttons in Customer Service query screens, concurrent programs that generate or refresh saved query results, and custom PL/SQL or Java code extending CSM functionality. Callers should use the _PUB procedures exclusively rather than writing to underlying query tables directly, preserving validation, concurrency handling, and FND_API-based error reporting. Because the package depends on FND_API, callers should also check the FND message stack after each call. Identical behavior applies in both 12.1.1 and 12.2.2; no upgrade-specific changes are documented.