Search Results validate_procedure

Overview

APPS.CSM_QUERY_PKG is the core PL/SQL package body that implements the query definition, validation, and execution framework within Oracle E-Business Suite. It belongs to the CSM (Customer Service Management / Common Self-Service Module) product family and operates in the APPS schema, providing the business logic layer above the CSM_QUERY base and access tables. In EBS 12.1.1 and 12.2.2 the package is documented as VALID and is classified as an "OTHER" API type rather than a fully published public API. Its purpose is to let administrators define parameterized queries, bind runtime variables to those queries, validate access and workflow context, execute the query, and persist results for downstream consumption or display. The package also integrates with Oracle Workflow (WF_ENGINE, WF_ITEM_TYPES, WF_ITEM_ATTRIBUTES, WF_PROCESS_ACTIVITIES) and with the FND_API, FND_GLOBAL, FND_LOG, and FND_ORACLE_USERID facilities, indicating that it participates in the standard EBS concurrency, logging, and user-identity conventions. Internally it relies on DBMS_SQL, DBMS_LOB, and DBMS_XMLGEN for dynamic SQL execution and large-object/XML handling.

Key Procedures and Functions

• INSERT_QUERY — Creates a new query definition record.
• UPDATE_QUERY — Modifies an existing query definition.
• DELETE_QUERY — Removes a query definition.
• VALIDATE_QUERY — Performs integrity checks on a query definition before persistence or execution.
• EXECUTE_QUERY — Runs the defined query using dynamic SQL and returns or stores the result set.
• INSERT_INSTANCE — Records a query execution instance.
• DELETE_INSTANCE — Removes a stored query instance.
• VALIDATE_ACCESS — Confirms that the requesting user has the required access rights to a query.
• VALIDATE_WORKFLOW — Verifies workflow-related context for the query.
• VALIDATE_PROCEDURE — Checks an associated stored procedure used by the query.
• INSERT_RESULT — Persists query output into the results table.
• SET_VALIDATE_PROCEDURE — Assigns or updates the validation procedure associated with a query.

No parameter lists are documented in the ETRM metadata; the above descriptions reflect the documented procedure names and their evident purpose only.

Tables Accessed

The package reads and writes the CSM_QUERY base and access tables through APPS synonyms. CSM_QUERY_B, CSM_QUERY_B_S, and CSM_QUERY_TL hold the query definition, its translation, and its set-based record. CSM_QUERY_VARIABLES_B and CSM_QUERY_VARIABLE_VALUES_ACC (with CSM_QUERY_VARIABLE_VAL_ACC_S) store bind-variable definitions and their access-controlled values — the object name the user searched, CSM_QUERY_VARIABLE_VAL_ACC_S, is the security/view synonym for that variable-value table. CSM_QUERY_ACC and CSM_QUERY_INSTANCES_ACC/_S govern query and instance access. CSM_QUERY_RESULTS_ACC/_S house execution output. WF_ITEM_ATTRIBUTES, WF_ITEM_TYPES, and WF_PROCESS_ACTIVITIES support workflow integration, while FND_ORACLE_USERID is used for user identity lookup.

Usage Notes

CSM_QUERY_PKG is typically invoked from EBS forms, concurrent programs, and custom PL/SQL that need to define or execute CSM queries. Because it references FND_LOG and FND_API, calls are expected to follow standard EBS error-handling and logging patterns, and because it is not referenced by any database object, it is a top-level entry point called directly by application code. The package is referenced by six other packages, so changes can propagate through dependent modules. In 12.2.2 the ETRM metadata confirms the same twelve documented procedures and table dependencies as in 12.1.1, indicating a stable interface across both releases.