Search Results csm_query_instances

Overview

CSM_QUERY_INSTANCE_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM metadata as an "OTHER" API type. The package serves the Oracle Field Service / Enterprise Territory Management synchronization infrastructure, specifically supporting the upward synchronization of the CSM_QUERY_INSTANCES publication item. Its guiding purpose, as stated in the header comment, is to "Update Query Instance changes on Handheld to Enterprise database." In the disconnected or mobile deployment model of EBS, field technicians working on handheld devices capture query instance data offline. When those devices reconnect and synchronize with the central enterprise database, this package provides the server-side entry point that reconciles, validates, and applies the changes originating from the client. The package is declared with AUTHID CURRENT_USER, meaning its procedures execute with the privileges of the invoking user rather than the package owner, which is consistent with synchronization APIs that must respect row-level security and operating unit context. The most recent documented revision carries the header identifier csmuqis.pls 120.1.12020000.2, dated 2012/08/10, placing it in the 12.1.1 and 12.2.2 release lineage.

Key Procedures and Functions

The ETRM metadata documents a single public program unit within this package:

• APPLY_CLIENT_CHANGES — The sole documented procedure and the functional core of the package. As its name indicates, it applies a batch of client-originated changes to the enterprise database. Its parameters, as reflected in the package specification, include a user name, a transaction identifier, a debug level, a sync-origin indicator, and an output return status. The x_return_status OUT parameter communicates success or failure back to the synchronization framework. The procedure is explicitly described in the source header as the function "to be called by CSM_QUERY_INSTANCE_PKG, for upward sync of publication item CSM_QUERY_INSTANCES," confirming that it is invoked by the Mobile Field Service synchronization engine rather than by interactive users. No other procedures or functions are publicly declared in the specification.

Tables Accessed

The package reads and writes the following tables, referenced through APPS synonyms, reflecting its role in resolving query definitions into per-user query instances:

• CSM_QUERY_B — The base table holding query definitions; read to validate that incoming instances reference legitimate queries.
• CSM_QUERY_INSTANCES_ACC and CSM_QUERY_INSTANCES_ACC_S — The _ACC and _ACC_S suffixed tables represent the "ACC" (accumulator/staging) layer used by the synchronization framework; the procedure typically processes pending rows here before promoting them.
• CSM_QUERY_INSTANCES_INQ — The inquiry or interface table that receives inbound client changes prior to validation and merge.
• CSM_QUERY_VARIABLES_B — Base table of query variables, read to resolve variable definitions associated with incoming query instances.
• CSM_QUERY_VARIABLE_VALUES_ACC, CSM_QUERY_VARIABLE_VALUES_INQ, and CSM_QUERY_VARIABLE_VAL_ACC_S — These tables store the variable values bound to each query instance; the procedure writes the values synchronized from the handheld so that saved queries execute with the same filters on the enterprise side.
• ASG_USER — The resource/user assignment view or table used to resolve the p_user_name argument to a valid application user and resource context.
• DUAL and PLITBLM — Utility references; PLITBLM is the standard EBS PL/SQL index-by table type used for bulk processing within synchronization APIs.

Usage Notes

APPLY_CLIENT_CHANGES is not designed for direct invocation from forms or concurrent programs. It is called programmatically by the Mobile Field Service / CSM synchronization runtime whenever a handheld device performs an upload of the CSM_QUERY_INSTANCES publication item, and the ETRM metadata records that the package is referenced by one other package in the synchronization stack. The p_from_sync parameter, defaulting to 'N', allows the caller to distinguish a genuine sync invocation from other execution paths. The p_debug_level parameter supports diagnostic logging during troubleshooting of failed uploads. Custom code should invoke this API only through the documented synchronization framework and should always inspect x_return_status before committing, since a failure status indicates that client changes were not applied and the device-side copy must not be considered reconciled. Direct DML against the underlying CSM_QUERY_INSTANCES tables should be avoided, as the ACC and INQ staging layers assume the package maintains their state.