Search Results msc_resource_shifts




Overview
Explore in AI Chat

Overview

MSC_RESOURCE_SHIFTS is a table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). It stores resource shift information — the definition of working shifts assigned to production resources within a department, together with the capacity units those shifts contribute. In ASCP, resource capacity is a fundamental planning input: the planning engine consumes shift definitions to derive available capacity for constrained and unconstrained plans, and to distribute resource load across time buckets. MSC_RESOURCE_SHIFTS therefore acts as a reference (staging/collection) table that mirrors shift data collected from source systems such as Oracle Manufacturing (BOM/CRP) or external ERPs through the collections process.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault model suggestion, meaning it is not modeled as a hub, link, or satellite through foreign-key dependencies. This classification is a modeling suggestion only, derived from the absence of mined FK relationships; the table nonetheless participates logically in the resource-department domain through its composite business key.

Key Information Stored

The physical schema documents 31 columns. The most significant are:

The primary key is the composite constraint MSC_RESOURCE_SHIFTS_PK, defined on (DEPARTMENT_ID, SHIFT_NUM, RESOURCE_ID, SR_INSTANCE_ID). A unique index, MSC_RESOURCE_SHIFTS_U1, covers (SR_INSTANCE_ID, DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM) and serves as the business-key candidate. In this design there is no single surrogate key column; uniqueness is enforced by these composite keys.

Common Use Cases and Queries

Typical uses include validating collected shift capacity, reconciling ASCP capacity to source manufacturing definitions, and reporting resource availability by department and source instance. A common query joins shifts to their resources and departments:

  • Capacity rollup by department and resource: 
    SELECT department_id, resource_id, SUM(capacity_units)
FROM   msc_resource_shifts
WHERE  sr_instance_id = :instance
GROUP  BY department_id, resource_id;
  • Detecting duplicate or conflicting shift definitions for a resource across a refresh cycle by comparing REFRESH_NUMBER values.
  • Auditing which concurrent program populated shift data using REQUEST_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Related Objects

The metadata documents no mined FK relationships (standalone classification); the following are the logical dependencies implied by its key columns:

Because the table is treated as standalone, joins must be constructed explicitly on the composite key columns rather than inherited from declared constraints.