Search Results msc_period_start_dates_u1

Overview

MSC.MSC_PERIOD_START_DATES is a workday calendar definition table in the MSC (Advanced Supply Chain Planning) schema of Oracle E-Business Suite. It stores the resolved period start dates for planning calendars, together with the sequencing metadata required to navigate forward and backward between consecutive planning periods. The table is fundamental to time-bucketed planning in Oracle Advanced Supply Chain Planning (ASCP), where plan output, demand and supply buckets, forecast spreading, and capacity windows are all anchored to a calendar-derived period boundary rather than to the literal transaction date.

Physically, the object resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and is uniquely indexed in APPS_TS_TX_IDX. The ETRM metadata classifies this object heuristically as standalone under the Data Vault model. From a dimensional modeling perspective, this suggests treating it as a reference or hub-style entity owned by the calendar dimension rather than as a transactional fact or link table. Its rows are generated and refreshed by collection and planning concurrent programs rather than entered by users.

Key Information Stored

The documented primary key is MSC_PERIOD_START_DATES_PK, composed of CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, and PERIOD_START_DATE. The unique index MSC_PERIOD_START_DATES_U1 covers the same four columns in the order SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, PERIOD_START_DATE, and represents the business-key candidate for the row. The most significant columns are:

• SR_INSTANCE_ID — identifies the source application instance, allowing calendars from multiple source systems (for example, multiple EBS or legacy instances) to coexist.
• CALENDAR_CODE — the workday calendar identifier, typically matching the manufacturing or workday calendar used in the source instance.
• EXCEPTION_SET_ID — the exception set that modifies the base calendar for a specific plan or organization.
• PERIOD_START_DATE — the first date of the workday calendar period; the anchor for all bucket assignments.
• PERIOD_SEQUENCE_NUM — ordinal position of the period, enabling ordering and relative-period arithmetic.
• PERIOD_NAME — the display name of the period, dependent on the quarterly calendar type selected.
• NEXT_DATE and PRIOR_DATE — the period start dates for the next and previous sequence numbers, allowing self-relative navigation without a window function.
• REFRESH_NUMBER — the refresh identifier assigned by the collection program, used to isolate the rows produced by a given plan refresh cycle.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent Who columns recording which request and program populated the rows.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Who audit columns.

Common Use Cases and Queries

Typical uses include validating that a plan's calendar is loaded for the correct source instance, mapping transaction dates to planning buckets, and reporting period coverage gaps. A representative query returning the ordered period sequence for a calendar and exception set:

• SELECT period_sequence_num, period_name, period_start_date, next_date, prior_date FROM msc.msc_period_start_dates WHERE calendar_code = :calendar_code AND sr_instance_id = :instance AND exception_set_id = :exception_set ORDER BY period_sequence_num;
• Determining the bucket a date falls into: select the row with the greatest PERIOD_START_DATE less than or equal to the transaction date.
• Detecting stale calendar data by comparing the maximum REFRESH_NUMBER against the current plan refresh.
• Auditing which concurrent request last rebuilt the calendar using REQUEST_ID and PROGRAM_UPDATE_DATE.

Related Objects

The ETRM metadata states that MSC.MSC_PERIOD_START_DATES does not reference any database object, confirming its standalone classification. Join relationships are therefore logical rather than enforced by foreign keys. The most significant related objects are MSC.MSC_CALENDAR_DATES, joined on CALENDAR_CODE and SR_INSTANCE_ID for underlying workday definitions; MSC.MSC_EXCEPTION_SETS, joined on EXCEPTION_SET_ID; MSC.MSC_PLANNING_CALENDARS and the plan-level tables that reference CALENDAR_CODE; the collection concurrent programs recorded in PROGRAM_ID and REQUEST_ID; and planning bucket tables in MSC that resolve dates through PERIOD_START_DATE, NEXT_DATE, and PRIOR_DATE.