Search Results update_query

Overview

CSM_QUERY_PUB is the public PL/SQL API for the Oracle E-Business Suite "CSM" (Customer Service Manager / TeleService-style mobile) query and instance management framework. In the 12.1.1 and 12.2.2 code lines, the package body is the transactional entry point used by the Mobile Admin page and related runtime components to persist, maintain, and execute user-defined queries and their generated result instances. The header comment identifies its primary purpose explicitly: "The function to be called by Mobile Admin page to store defined queries." The package follows the standard EBS API conventions — an API version parameter, an initialization message list flag, a commit flag, and paired OUT parameters (x_return_status and x_error_message) — so that callers can participate in FND_API compliant transaction control and error handling.

Query definitions stored through this API carry substantial metadata: query text fragments, level and hierarchy identifiers, output format and MIME type, retention policy and retention days, template and template file, execution mode, e-mail enablement, restricted/disabled flags, and collection-typed bind variable metadata (names, types, character values, date values, hidden flags, and default flags, using the CSM_VARCHAR_LIST and CSM_DATE_LIST types).

Key Procedures and Functions

• INSERT_QUERY — Creates a new query definition. It opens the CSM_INSERT_QUERY_PUB savepoint, logs entry through CSM_UTIL_PKG.LOG at FND_LOG.LEVEL_ERROR, and delegates the actual insert to CSM_QUERY_PKG.INSERT_QUERY, passing the full query attribute set including the bind variable collections.
• UPDATE_QUERY — Modifies an existing query definition, following the same logging, savepoint, and delegation pattern as INSERT_QUERY.
• DELETE_QUERY — Removes a stored query definition, allowing the Mobile Admin page to retire queries that are no longer maintained.
• EXECUTE_QUERY — Runs a previously defined query, applying its stored text, level context, execution mode, and bind variable values.
• INSERT_INSTANCE — Persists an execution instance of a query, capturing the runtime context associated with a stored definition.
• DELETE_INSTANCE — Removes a query execution instance. This is the procedure surfaced when users search for "delete_instance"; it is the supported public path for cleaning up an instance record rather than issuing direct DML.
• INSERT_RESULT — Stores the result set produced by a query execution, completing the definition → instance → result lifecycle.

Tables Accessed

The ETRM metadata for this object does not enumerate the underlying base tables; access is performed indirectly through APPS synonyms via CSM_QUERY_PKG, which owns the actual DML. The referenced tables correspond to the CSM query framework: a query definition table, an instance/history table, and a result table, together with the bind variable and template/retention attributes carried by the INSERT_QUERY signature. Because this package is classified as a PUB API, callers should treat all table access as encapsulated and never write to those tables directly.

Usage Notes

CSM_QUERY_PUB is invoked from the Mobile Admin page for query maintenance and from runtime components that execute queries and persist their instances and results. The ETRM record shows zero other packages referencing it, so it is invoked from forms/UI layers, concurrent programs, or custom code rather than from within other PL/SQL packages. Callers should pass p_API_VERSION, control transaction boundaries via p_INIT_MSG_LIST and p_COMMIT, and always inspect x_return_status and x_error_message. DELETE_INSTANCE should be used in preference to direct DELETEs so that CSM_QUERY_PKG business rules and logging are honored.