Search Results drop_snapshot_ei

Overview

MRP_CL_REFRESH_SNAPSHOT is an APPS-owned PL/SQL package that supports the Oracle Advanced Planning and Scheduling (APS) and Collaborative Planning collection infrastructure within Oracle E-Business Suite 12.1.1 and 12.2.2. Its principal business function is to create, populate, and maintain the database snapshot objects that represent a consistent, point-in-time view of source transactional data for planning purposes. These snapshots isolate the planning engine from live production tables, allowing long-running planning runs to operate against a stable data image. The package is registered in ETRM with a status of VALID and an API classification of OTHER, indicating it is an internal utility rather than a documented public interface. It is tightly coupled with the collection and refresh framework, and the ETRM metadata confirms it is referenced by MSC_CL_COLLECTION, MSC_CL_SETUP_ODS_LOAD, MRP_MAP_REG_SITE, and MRP_CL_FUNCTION.

Key Procedures and Functions

The ETRM metadata documents 18 procedures and functions. The core orchestration routine, REFRESH_SNAPSHOT, drives the full snapshot rebuild process, while REFRESH_SINGLE_SNAPSHOT performs the same operation scoped to a single snapshot or entity. DROP_SNAPSHOT and DROP_SNAPSHOT_EI remove existing snapshot objects and their associated entity indexes. SETUP_SOURCE_OBJECTS prepares the source definitions prior to refresh, and CREATE_SOURCE_VIEWS and CREATE_SOURCE_TRIGGERS build the supporting views and triggers on source tables. WAIT_FOR_REQUEST synchronizes the calling process against a concurrent request identifier. CHECK_MV_CONT_REF_TYPE and CHECK_ENTITY_CONT_REF_TYPE validate refresh type and container reference metadata. CHECK_DB_LINK and CHECK_USAF_FLAG verify database connectivity and the application’s planning flag setting. PURGE_OBSOLETE_DATA clears stale rows. LOG_DEBUG and LOG_ERROR provide diagnostic and error logging. The remaining documented entry points complete the administrative and validation surface of the package.

Tables Accessed

The package accesses several dictionary and setup tables through APPS synonyms. FND_APPLICATION and FND_ID_FLEX_SEGMENTS, FND_ID_FLEX_STRUCTURES, FND_PROFILE_OPTIONS, FND_PROFILE_OPTION_VALUES, and FND_USER are read to resolve application context, key flexfield structure, profile option values, and user identity. MRP_AP_APPS_INSTANCES_ALL, MRP_AP_REFRESH_S, and MRP_REGION_SITES hold the planning instance, refresh schedule, and region-site configuration that governs which snapshots are created. ALL_SNAPSHOT_LOGS, ALL_TABLES, and DBMS_MVIEW supply snapshot log status, object existence checks, and materialized view operations. DBMS_LOCK, DBMS_UTILITY, and DUAL are used for concurrency control, session utilities, and singleton queries. FND_PROFILE and MSC_UTIL are recorded as direct package dependencies in the documented dependency listing.

Usage Notes

MRP_CL_REFRESH_SNAPSHOT is invoked indirectly rather than by end users. It is called by MRP_CL_FUNCTION and MSC_CL_COLLECTION during collection and refresh cycles, and by MSC_CL_SETUP_ODS_LOAD and MRP_MAP_REG_SITE during setup and mapping operations. In practice these calls are triggered from the planning collection concurrent programs and from the Advanced Supply Chain Planning setup flows that stage operational data stores. Because the package is classified as OTHER and is not a public API, customizations should not call its procedures directly; instead, custom code should invoke the documented collection or planning concurrent programs that in turn drive the refresh. In 12.2.2 the online patching (adop) model means snapshot and materialized view objects created by this package must be regenerated after patching cycles, and any database link validated by CHECK_DB_LINK must remain accessible for refresh to complete successfully.