Search Results msc_resource_changes




Overview
Explore in AI Chat

Overview

MSC_RESOURCE_CHANGES is a table within the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores resource capacity modifications — that is, planned adjustments to the available capacity of a production resource over a defined time window and shift. Each row captures a delta (or overriding value) applied against the standard resource capacity, scoped to a specific simulation set, source instance, and action type, which together allow planners to model "what-if" scenarios without disturbing the base production plan.

Because the table's only outgoing foreign key (DEPARTMENT_ID → BOM_DEPARTMENTS) links it to a parent dimension while the resource-capacity measure itself is keyed by a composite of instance, simulation set, action type, department, resource, and time attributes, the heuristic Data Vault classification reported in the metadata is standalone. From a modeling perspective, this suggests treating MSC_RESOURCE_CHANGES as a self-contained fact-style structure rather than as a dependent satellite hung off a single hub, though the DEPARTMENT_ID reference can be modeled as a straightforward link to a department hub if dimensional separation is desired.

Key Information Stored

The 37 columns divide into a composite business key, a capacity measure, planning-scope controls, and the standard EBS audit/descriptive block. The most significant columns are:

The unique index MSC_RESOURCE_CHANGES_U1 (SR_INSTANCE_ID, SIMULATION_SET, ACTION_TYPE, DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM, FROM_DATE, TO_DATE, FROM_TIME, TO_TIME) is the business-key candidate; it is a composite natural key rather than a single surrogate primary key, which is consistent with a large planning fact table.

Common Use Cases and Queries

Typical uses involve reviewing simulation-scoped capacity overrides, reconciling changes to the baseline plan, and reporting planned capacity by department or resource.

-- Capacity changes for a given simulation and instance
SELECT department_id, resource_id, shift_num,
       from_date, to_date, capacity_change
FROM   msc.msc_resource_changes
WHERE  sr_instance_id = :instance_id
AND    simulation_set = :sim_set
AND    action_type    = :action_type
ORDER  BY department_id, resource_id, from_date;

-- Net capacity adjustment by department over a window
SELECT department_id, SUM(capacity_change) AS net_change
FROM   msc.msc_resource_changes
WHERE  from_date >= :start_date
AND    to_date   <= :end_date
AND    refresh_number = :refresh
GROUP  BY department_id;

Planners also join to BOM_DEPARTMENTS to resolve department names, and filter on REFRESH_NUMBER to analyze a single plan run.

Related Objects