Search Results csm_query_variables

Overview

APPS.CSM_QUERY_EVENT_PKG is a PL/SQL package body belonging to the Oracle EBS Channel Service Manager (CSM) module. It supports the query management infrastructure that underlies user-defined queries, query variables, query instances, and query results. Its principal business function is to maintain and synchronize the access-control layer for these query artifacts — that is, to determine which users are permitted to view or run a given query, based on the responsibility or user-level assignments recorded against the base query definition.

The package body declares several global public item constants that mirror the underlying schema objects: g_pub_item_qry ('CSM_QUERY'), g_pub_item_qvar ('CSM_QUERY_VARIABLES'), g_pub_item_qval ('CSM_QUERY_VARIABLE_VALUES'), g_pub_item_qins ('CSM_QUERY_INSTANCES'), and g_pub_item_qres ('CSM_QUERY_RESULTS'). The CSM_QUERY_VARIABLES constant corresponds directly to the variable registry that the user's search term references; it is the entity through which parameterized queries are defined and bound to values at run time.

Key Procedures and Functions

Two procedures are documented for this package body:

• REFRESH_ACC — Rebuilds or refreshes the query access records. It is declared with PRAGMA AUTONOMOUS_TRANSACTION, allowing it to commit independently of the caller's transaction. It opens a cursor over CSM_QUERY_B joined to ASG_USER, evaluating level-based grants (responsibility-level, user-level, and global), filtering for enabled users and non-deleted queries, and inserting new rows into the query access table via the CSM_QUERY_ACC_S sequence while skipping existing assignments. It also tracks run-date information from the concurrent request data source.
• REFRESH_USER — Performs the equivalent refresh scoped to a specific user, rebuilding that user's query access entries. It is typically used when a single user's responsibility or role assignments change and a full refresh is unnecessary.

Both procedures return status and message output parameters for error reporting.

Tables Accessed

The package reads and writes across the CSM query and access-control tables:

• CSM_QUERY_B — base query definitions, including level identifiers and delete flags.
• ASG_USER — the user/responsibility assignment source used to resolve which grants apply.
• CSM_QUERY_ACC and CSM_QUERY_ACC_S — the query access table and its sequence, written by the refresh routines.
• CSM_QUERY_VARIABLES_B and CSM_QUERY_VARIABLES_ACC / CSM_QUERY_VARIABLES_ACC_S — variable definitions and their access rows.
• CSM_QUERY_VARIABLE_VALUES_ACC, CSM_QUERY_INSTANCES_ACC, and CSM_QUERY_RESULTS_ACC — access rows for variable values, instances, and results.
• JTM_CON_REQUEST_DATA — concurrent request run-date metadata.
• PLITBLM — the standard PL/SQL indexed-table utility for array handling.

Usage Notes

This package is invoked by the CSM query administration forms and by concurrent programs that propagate access-control changes when queries or user assignments are modified. Because REFRESH_ACC uses an autonomous transaction, it commits its inserts independently, so callers cannot roll those changes back and should invoke it deliberately. Custom code should not call the refresh procedures directly without first understanding the access-list synchronization logic; the package is referenced by one other package in the EBS schema, indicating it forms part of a controlled dependency chain.