Search Results msc_calendar_dates




Overview
Explore in AI Chat

Overview

MSC_CALENDAR_DATES is a table in the MSC schema belonging to the Advanced Supply Chain Planning (ASCP) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores every date that falls between the calendar start date and calendar end date for a given calendar, resolved against a specified exception set. In effect, the table is the expanded, materialized form of the calendar definitions maintained in the source applications: rather than storing a calendar as a header plus a rule, ASCP flattens it into one row per working or non-working date so that planning engines can iterate dates quickly. The record carries 39 documented columns and is populated through the collections process that reads calendar and exception data from source systems, consistent with the description that the table "stores data collected from source applications."

In Data Vault terms the metadata classifies this object heuristically as standalone, meaning it does not participate in the mined foreign-key relationship graph. From a modeling perspective it is best treated as a saturating reference or dimension-style table keyed by business attributes rather than a hub or link, because its identity is derived entirely from the calendar code, exception set, and date rather than from an upstream entity reference.

Key Information Stored

The primary key MSC_CALENDAR_DATES_PK is a composite of four columns: SR_INSTANCE_ID, CALENDAR_DATE, CALENDAR_CODE, and EXCEPTION_SET_ID. These same four columns form the unique business-key index MSC_CALENDAR_DATES_U1 (ordered as SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE). There is no single surrogate column; the composite itself is the identity.

  • SR_INSTANCE_ID — identifies the source instance from which the calendar row was collected, enabling multi-source consolidation within a single MSC repository.
  • CALENDAR_CODE — the calendar identifier (for example, a manufacturing or shipping calendar) to which the date belongs.
  • EXCEPTION_SET_ID — the exception set applied when expanding the calendar, which determines which dates are treated as exceptions to the base pattern.
  • CALENDAR_DATE — the individual date represented by the row, the core fact of the table.
  • CALENDAR_START_DATE and CALENDAR_END_DATE — the bounds of the calendar, repeated on each row for range filtering.
  • SEQ_NUM, NEXT_SEQ_NUM, PRIOR_SEQ_NUM — sequence positioning within the calendar, supporting ordered traversal and interval arithmetic.
  • NEXT_DATE and PRIOR_DATE — the adjacent dates in the sequence, allowing lead/lag navigation without a self-join.
  • DESCRIPTION — a descriptive label for the date or its exception context.
  • REFRESH_NUMBER — the collection refresh cycle that produced the row.
  • LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard audit columns.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context that wrote the record.
  • ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — the standard descriptive flexfield.
  • DELETED_FLAG — soft-delete indicator, allowing stale rows to be retained through a refresh cycle.

Common Use Cases and Queries

The primary use case is date-level expansion for planning runs, so reporting typically filters by calendar and exception set before joining to planning or scheduling data. A common pattern retrieves the working dates in a window:

  • SELECT calendar_date FROM msc_calendar_dates WHERE sr_instance_id = :inst AND calendar_code = :cal AND exception_set_id = :exc AND deleted_flag = 'N' AND calendar_date BETWEEN :start AND :end ORDER BY calendar_date;
  • Sequence traversal using NEXT_DATE / PRIOR_DATE avoids a self-join when computing lead times between consecutive working days.
  • Validating source calendar collections by comparing the row count and min/max CALENDAR_DATE against the source calendar definition.
  • Detecting gaps or duplicates by grouping on CALENDAR_CODE and EXCEPTION_SET_ID and comparing SEQ_NUM continuity.
  • Joining to planning tables on CALENDAR_DATE to bucket supply and demand into valid working-day periods.

Related Objects

Because MSC_CALENDAR_DATES is classified as standalone, no foreign-key dependencies were mined from it. Related objects are therefore those that share the composite calendar identity or consume the expanded dates:

  • MSC_CALENDAR_DATES_PK / MSC_CALENDAR_DATES_U1 — the primary and unique indexes enforcing row identity on (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE).
  • MSC_CALENDARS — the calendar header table whose CALENDAR_CODE and exception set drive this detail table.
  • MSC_EXCEPTION_SETS — defines EXCEPTION_SET_ID usage referenced by each row.
  • MSC_SR_INSTANCES — resolves SR_INSTANCE_ID to the source application instance.
  • MSC_SYSTEM_ITEMS and MSC_ITEM_CALENDARS — associate items with calendars for scheduling purposes.
  • MSC_PLANNING_ENTRIES and MSC_SUPPLIES — planning records joined to CALENDAR_DATE for time-phased reporting.
  • FND_CONCURRENT_REQUESTS — resolves REQUEST_ID to the collection program that produced the rows.