Search Results msc_plan_refreshes_pk

Overview

MSC_PLAN_REFRESHES is a table in the MSC schema belonging to the Advanced Supply Chain Planning (ASCP) module of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to support the collection program by tracking the refresh number associated with each planning plan. Every time the collections process executes and populates data for a plan, the refresh counter maintained in this table advances, allowing the planning engine and downstream concurrent programs to determine the currency and generation of the collected data set.

The table is classified under ETRM as VALID and is documented with 13 columns. The heuristic Data Vault classification mined from its foreign-key structure is standalone. From a modeling perspective, this suggests that MSC_PLAN_REFRESHES functions as a small reference or status table rather than as a true hub, link, or satellite, because it carries no outgoing foreign keys to other MSC entities. Its grain is one row per source instance and plan combination, and its composite primary key is defined by MSC_PLAN_REFRESHES_PK on (SR_INSTANCE_ID, PLAN_ID).

Key Information Stored

The most important columns are the composite key and the refresh counter. The surrogate-style primary key is composed of SR_INSTANCE_ID and PLAN_ID, which together identify the source system instance and the specific plan being refreshed. A separate unique index, MSC_PLAN_REFRESHES_U1, is documented on (PLAN_ID, SR_INSTANCE_ID); this is effectively the same attribute combination in reverse order and represents the business-key candidate for the row.

REFRESH_NUMBER is the operational payload of the table — the incremental counter that the collection program updates for each plan. The remaining columns are standard EBS audit and concurrent-program context attributes: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN capture who created and last modified the row; REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE tie the row to the concurrent request and program that last touched it; and APPS_LRN supports the EBS logical row identifier used for change tracking and auditing.

Common Use Cases and Queries

The primary use case is diagnostic: confirming the current refresh generation for a plan before or after running collections. A typical query joins the refresh counter to the plan definition and instance metadata:

• Identifying stale plans — comparing REFRESH_NUMBER across plans to find those whose collections have not advanced.
• Reconciling collection runs — correlating REQUEST_ID and PROGRAM_ID with the concurrent manager history to trace which request incremented the counter.
• Auditing changes — using LAST_UPDATE_DATE, LAST_UPDATED_BY, and APPS_LRN to track modification history.

A representative SQL pattern selects PLAN_ID, SR_INSTANCE_ID, REFRESH_NUMBER, REQUEST_ID, and LAST_UPDATE_DATE from MSC.MSC_PLAN_REFRESHES, filtering by SR_INSTANCE_ID for a given source instance and ordering by LAST_UPDATE_DATE descending. Reporting layers frequently expose REFRESH_NUMBER alongside plan names to indicate data currency on planning dashboards.

Related Objects

Because the metadata describes MSC_PLAN_REFRESHES as standalone with no outgoing foreign keys, related objects are best identified by shared key columns rather than declared constraints. The most significant are:

• MSC_PLANS — joined on PLAN_ID to resolve the plan name and attributes.
• MSC_SR_INSTANCES (or the source instance definition table) — joined on SR_INSTANCE_ID to identify the source system.
• MSC_PLAN_REFRESHES_PK and MSC_PLAN_REFRESHES_U1 — the primary key and unique index enforcing row uniqueness.
• Concurrent program and request views (FND_CONCURRENT_REQUESTS) — joined on REQUEST_ID for run-level tracing.
• The collections program that populates MSC staging tables — the upstream process that maintains REFRESH_NUMBER.

These joins on PLAN_ID and SR_INSTANCE_ID form the practical backbone for diagnosing and reporting on plan data currency in ASCP.