Results for “msc_resource_changes”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• DEPARTMENT_ID — the owning department (resource group) for the affected resource; the sole foreign key to BOM_DEPARTMENTS.
• RESOURCE_ID — the specific resource whose capacity is being modified.
• SR_INSTANCE_ID — identifies the source (ERP) instance, enabling multi-instance consolidation.
• SIMULATION_SET — the named planning simulation to which the change belongs; isolates scenario data from the baseline.
• ACTION_TYPE — distinguishes the nature of the change (for example, add, override, or delete) applied to capacity.
• SHIFT_NUM, FROM_DATE, TO_DATE, FROM_TIME, TO_TIME — the shift and effective time window over which the capacity change applies.
• CAPACITY_CHANGE — the numeric capacity adjustment (the principal measure of the table).
• REFRESH_NUMBER — ties the change to a specific planning refresh/run cycle.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the WHO-audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the concurrent-program context that produced the rows.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS flex/descriptive segment block.

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

• BOM_DEPARTMENTS — parent of the DEPARTMENT_ID foreign key; resolves department/resource-group attributes.
• MSC_RESOURCES — supplies resource definitions for RESOURCE_ID.
• MSC_SIMULATION_SETS — defines the SIMULATION_SET values used for scenario isolation.
• MSC_SR_INSTANCES — source-instance registry keyed by SR_INSTANCE_ID.
• MSC_SHIFTS / shift calendars — reference shift and time-window definitions.
• FND_CONCURRENT_PROGRAMS / FND_CONCURRENT_REQUESTS — resolve PROGRAM_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID audit context.
• MSC_RESOURCE_CAPACITIES — the base capacity table against which these changes are applied.