Search Results csm_system_items_acc_s

Overview

APPS.CSM_SYSTEM_ITEM_EVENT_PKG is a PL/SQL package body in the Oracle E-Business Suite Applications schema that belongs to the Customer Service (CSM) product family and, more specifically, to the service / install-base side of the application that manages system items and item instances. Its role is to serve as the event and refresh layer for the CSM "access" or ACC objects — notably CSM_SYSTEM_ITEMS_ACC and its shadow table CSM_SYSTEM_ITEMS_ACC_S — which together form a denormalized, query-optimized view of inventory system items as they relate to customers, instances, and inventory organizations. The package receives notifications (events) when underlying data changes — for example when on-hand balances shift or when a user's responsibility assignments change — and it re-derives and re-populates the ACC structures accordingly. This abstracted layer lets service agents, install-base users, and downstream applications query item information without repeatedly joining the much larger base tables such as MTL_SYSTEM_ITEMS, MTL_ONHAND_QUANTITIES_DETAIL, and CSI_ITEM_INSTANCES.

Key Procedures and Functions

The package body exposes seven documented procedures and functions, each supporting a distinct phase of the refresh and event cycle:

• REFRESH_MTL_ONHAND_QUANTITY — Recomputes and repopulates on-hand quantity information into the CSM_MTL_ONHAND_QTY_ACC and CSM_MTL_ONHAND_QTY_ACC_S access structures, drawing from MTL_ONHAND_QUANTITIES_DETAIL.
• REFRESH_ACC — The general access-layer refresh routine; it re-derives the contents of the CSM access tables so that service-facing queries see current system item, instance, and location-assignment data.
• GET_NEW_USER_SYSTEM_ITEMS — Identifies system items newly associated with a user (via responsibility and user-access assignments) and returns them for insertion into the access layer.
• SYSTEM_ITEM_MDIRTY_I — The "make dirty" / insert handler that flags or inserts a system item as needing refresh when a create-type event arrives.
• SYSTEM_ITEM_MDIRTY_D — The corresponding delete-side handler; it processes removals so that stale system item rows are purged from the access structures.
• DELETE_SYSTEM_ITEMS — Physically removes system item records from the ACC tables, typically following the dirty-delete signal.
• RECEIVED_MTL_ONHAND — Processes received on-hand transactions and propagates the resulting quantity changes into the access tables.

Tables Accessed

The package reads and writes a broad set of base and access tables. System item master data is sourced from MTL_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS_B, and MTL_SYSTEM_ITEMS_TL. Instance and install-base data come from CSI_ITEM_INSTANCES and CSI_INSTANCE_STATUSES. On-hand quantities are read from MTL_ONHAND_QUANTITIES_DETAIL. Access-layer writes target CSM_SYSTEM_ITEMS_ACC, CSM_SYSTEM_ITEMS_ACC_S, CSM_MTL_ONHAND_QTY_ACC, CSM_MTL_ONHAND_QTY_ACC_S, CSM_ITEM_INSTANCES_ACC, and CSM_INV_LOC_ASS_ACC. User and responsibility context is drawn from ASG_USER, ASG_USER_PUB_RESPS, CSM_USER_INVENTORY_ORG, and CSP_INV_LOC_ASSIGNMENTS. Auxiliary lookups include CS_BILLING_TYPE_CATEGORIES and JTM_CON_REQUEST_DATA, with DUAL used for trivial queries.

Usage Notes

CSM_SYSTEM_ITEM_EVENT_PKG is not an end-user API; it is invoked internally by other CSM packages, most directly CSM_ITEM_INSTANCE_EVENT_PKG, and coordinates with CSM_ACC_PKG, CSM_PROFILE_PKG, CSM_UTIL_PKG, and CSM_WF_PKG. The metadata records it as referenced by six other packages, confirming it is a shared internal utility rather than a top-level entry point. It is typically triggered when system item or on-hand events occur in the service data model, when a user's inventory-organization or responsibility access changes, or through scheduled refresh activity. In 12.1.1 and 12.2.2 the object remains VALID and its structure is unchanged. Customizations should call the documented procedures rather than modifying the ACC tables directly, and any extension should preserve the insert/delete symmetry of the MDIRTY handlers.