Search Results csm_query_pkg




Overview
Explore in AI Chat

Overview

APPS.CSM_QUERY_PKG is a server-side PL/SQL package in Oracle E-Business Suite that forms the core data-access and persistence layer for the Oracle Common Search Management (CSM) query framework. Within the CSM architecture, a "query" is a configurable, reusable search definition that end users and administrators build through the user interface and that the runtime engine later executes to retrieve records — commonly service requests, contacts, or other CRM entities in the Service/Debriefing modules. CSM_QUERY_PKG encapsulates the create, read, update, delete, validation, and execution logic for these saved query definitions and their associated instances and results. It is classified under API classification OTHER in the ETRM 12.2.2 metadata, and it is marked VALID in the APPS schema, meaning it is a supported, active object rather than an obsolete or invalidated one. The package depends on several CSM collection types (CSM_DATE_LIST, CSM_INTEGER_LIST, CSM_VARCHAR_LIST) and on FND_API, confirming that it participates in the standard EBS API conventions and is intended for programmatic invocation rather than end-user direct execution.

Key Procedures and Functions

The documented interface exposes twelve procedures and functions organized around three concerns:

  • Query definition maintenance — INSERT_QUERY, UPDATE_QUERY, and DELETE_QUERY create, modify, and remove saved query definitions along with their translated and variable-related attributes; VALIDATE_QUERY verifies that a query definition is internally consistent and executable before it is persisted or run.
  • Execution and results — EXECUTE_QUERY runs a saved query against its target data set; INSERT_RESULT stores the output rows produced by an execution. This pairing separates the evaluation of a query from the materialization of its results.
  • Instance, access, and workflow control — INSERT_INSTANCE and DELETE_INSTANCE manage saved query instances and their access records; VALIDATE_ACCESS enforces who may see or run a given query; VALIDATE_WORKFLOW and VALIDATE_PROCEDURE plus SET_VALIDATE_PROCEDURE govern workflow- and procedure-based validation behaviour associated with a query. As with all EBS development, the standard parameter lists are documented in the package specification and should be referenced there rather than assumed.

Tables Accessed

The package reads and writes a defined set of CSM base, translation, and access tables through APPS synonyms. Query definitions and their translated text are held in CSM_QUERY_B, CSM_QUERY_B_S, and CSM_QUERY_TL. Access and authorization rows reside in CSM_QUERY_ACC and in the instances and results access tables CSM_QUERY_INSTANCES_ACC, CSM_QUERY_INSTANCES_ACC_S, CSM_QUERY_RESULTS_ACC, and CSM_QUERY_RESULTS_ACC_S. Query parameters and their user-supplied values are stored in CSM_QUERY_VARIABLES_B, CSM_QUERY_VARIABLE_VALUES_ACC, and CSM_QUERY_VARIABLE_VAL_ACC_S. Supporting lookups include FND_ORACLE_USERID for database account resolution, and the Oracle Workflow tables WF_ITEM_TYPES, WF_ITEM_ATTRIBUTES, and WF_PROCESS_ACTIVITIES, which support workflow-driven validation.

Usage Notes

CSM_QUERY_PKG is primarily an internal engine, not a user-facing API. It is invoked indirectly through higher-level callers: the ETRM dependencies show it referenced by CSM_QUERY_PUB, CSM_QUERY_INSTANCE_PKG, CSM_EMAIL_QUERY_PKG, CSM_HTML5_PKG, CSM_MOBILE_QUERY_PKG, and CSM_SR_EVENT_PKG, indicating that it backs the HTML and mobile query interfaces, the public CSM query API, and service-request event processing. Custom code should generally call CSM_QUERY_PUB rather than this package directly. When extending validation logic, SET_VALIDATE_PROCEDURE and VALIDATE_PROCEDURE should be used to register and invoke custom validation. Because the package participates in the FND_API error-handling framework, callers must inspect the returned API status rather than relying on exceptions alone. On both EBS 12.1.1 and 12.2.2 the same APPS-owned specification is shipped; the online edition (12.2.2) retains identical table and package dependencies.