Search Results insert_instance

Overview

CSM_QUERY_INSTANCE_PKG is a PL/SQL package body owned by APPS that supports the Oracle E-Business Suite CRM "Query Instance" framework, part of the CSM (Customer Support Management / telephony and interaction) module. Its core responsibility is to apply client-side changes to query instance and query variable value records that have been captured in the inbound staging tables and persist them into the corresponding accumulator and base tables. The package exists to synchronize records maintained by the desktop or agent-facing client with the E-Business Suite database, ensuring that query definitions, execution results, and variable bindings originate in a governed, server-side process rather than through direct table writes.

In Oracle EBS 12.1.1 and 12.2.2 the package is registered under the ETRM technical repository with an API classification of "OTHER" and is referenced by one other package, indicating it is consumed internally by the CSM application framework rather than exposed as a standalone public API. The header comment ($Header: csmuqib.pls 120.8.12020000.2 2012/08/10) reflects maintenance through the 12.2 release family, and its naming conventions (CSM_QUERY_*) align it with the broader CSM query infrastructure used by the Customer Support application.

Because the user's search term was "insert_instance," the relevant entry point is the internal APPLY_INSERT processing path, which handles newly inserted staged rows and converts them into committed query instance data. This reflects the package's primary business function: fielding insert events from the client and materializing them as valid server records.

Key Procedures and Functions

The documented procedures and functions for this package are limited to a single entry point:

• APPLY_CLIENT_CHANGES — The documented procedure in this package. Its purpose is to process changes pushed from the client side, including inserted query instance records, and apply them to the underlying E-Business Suite tables. The internal logic is driven by the APPLY_INSERT procedure, which the package body comment describes as "called by APPLY_CLIENT_CHANGES when an inserted record is to be processed." APPLY_INSERT selects from the CSM_QUERY_INSTANCES_INQ staging inquiry view, validates the query definition against CSM_QUERY_B, retrieves variable definitions from CSM_QUERY_VARIABLES_B, and reads the associated variable values captured in CSM_QUERY_VARIABLE_VALUES_INQ. The procedure returns an error message and a return status indicating success or failure, consistent with standard EBS API conventions. No additional parameter details are documented beyond those described here.

Tables Accessed

The package reads and writes the following documented tables (through APPS synonyms):

• CSM_QUERY_INSTANCES_INQ — The inbound staging view for query instance records; read by the APPLY_INSERT cursor to identify inserted records to process.
• CSM_QUERY_B — The base query definition table; read to validate QUERY_ID, LEVEL_ID, LEVEL_VALUE, and QUERY_TYPE and to confirm the query is active (DELETE_FLAG = 'N').
• CSM_QUERY_VARIABLES_B — The query variable definition table; read to obtain VARIABLE_ID, VARIABLE_VALUE_CHAR, VARIABLE_VALUE_DATE, and VARIABLE_TYPE.
• CSM_QUERY_VARIABLE_VALUES_INQ — The inbound staging view for variable values; read to obtain captured variable bindings for the instance.
• CSM_QUERY_INSTANCES_ACC and CSM_QUERY_INSTANCES_ACC_S — The accumulator tables used to stage instance inserts and updates prior to final persistence.
• CSM_QUERY_VARIABLE_VALUES_ACC and CSM_QUERY_VARIABLE_VAL_ACC_S — The accumulator tables for variable values.
• ASG_USER — The assignment/agency user table, referenced for user context associated with incoming changes.
• DUAL and PLITBLM — Utility references; PLITBLM is the standard EBS PL/SQL index-by table type used by the package's internal global collection (g_seqno_lst CSM_INTEGER_LIST).

Usage Notes

CSM_QUERY_INSTANCE_PKG is invoked internally by the CSM client-change synchronization framework, not by end users directly. The documented entry point, APPLY_CLIENT_CHANGES, is called when staged rows in CSM_QUERY_INSTANCES_INQ require processing — typically when an agent inserts a query instance on the client and that change must be propagated to the EBS database. The APPLY_INSERT procedure then evaluates the query definition, confirms the query is active, gathers variable values, and writes to the accumulator tables so the record is materialized server-side.

Callers should treat this package as an internal implementation detail of CSM rather than a public API. As it carries an ETRM classification of "OTHER" and is referenced by only one other package, custom code should not invoke it directly; instead, customizations should follow the standard client-change handling flow. Any diagnostic work should account for the package's debug global (g_debug_level) and its dependency on the CSM staging, accumulator, and base tables listed above. Because the package touches accumulator and base instance tables, administrators should ensure the relevant CSM transactions are consistent and that no concurrent client synchronization is in progress before troubleshooting staged insert records.