Search Results msc_period_start_dates_pk

Overview

MSC_PERIOD_START_DATES is a table owned by the MSC schema and validated in Oracle E-Business Suite release 12.1.1 and 12.2.2. It belongs to the Advanced Supply Chain Planning (ASCP) product family. The table stores workday calendar period start dates, and it exists to anchor planning buckets — the discrete time periods into which the planning engine aggregates supply, demand, and on-hand quantities. Every planned item and every plan scenario is bucketed against a calendar, so this table effectively defines the time axis over which ASCP computations run.

The metadata classifies the object heuristically as standalone within a Data Vault model. This is a modeling suggestion rather than a physical property: the table has no documented foreign keys to parent objects, so a Data Vault practitioner would likely treat it as a reference or satellite-style structure keyed by calendar and instance, rather than as a hub or link. Practitioners should treat the classification as advisory when designing downstream integration or warehousing layers.

Key Information Stored

The table is documented with eighteen columns. The most significant are the following.

• CALENDAR_CODE — identifies the calendar to which the period sequence belongs (for example, a workday manufacturing calendar).
• SR_INSTANCE_ID — the source instance identifier, allowing data from multiple source systems to coexist in one planning schema.
• EXCEPTION_SET_ID — associates the period definitions with an exception set used in planner workbench processing.
• PERIOD_START_DATE — the calendar date on which the bucket begins; the primary temporal attribute.
• PERIOD_SEQUENCE_NUM and PERIOD_NAME — the ordinal position of the bucket and its display label, used in bucket-level reporting.
• NEXT_DATE and PRIOR_DATE — adjacent bucket boundaries, enabling interval logic without self-joins.
• REFRESH_NUMBER — identifies the collection or refresh run that produced the row.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — concurrent program context for audit and traceability.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY — standard EBS who-columns.

The primary key MSC_PERIOD_START_DATES_PK is the composite (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, PERIOD_START_DATE). A unique index, MSC_PERIOD_START_DATES_U1, covers (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, PERIOD_START_DATE) and is the documented business-key candidate; notice it carries the same columns in a different order, which matters for index range scans that lead with SR_INSTANCE_ID.

Common Use Cases and Queries

Typical uses include generating bucket calendars for plan output, converting between dates and bucket sequence numbers, and validating that a given planning horizon is fully covered by a calendar. A frequent pattern returns the bucket containing a transaction date:

• SELECT calendar_code, period_name, period_sequence_num FROM msc_period_start_dates WHERE sr_instance_id = :instance AND calendar_code = :cal AND period_start_date <= :txn_date AND (next_date IS NULL OR next_date > :txn_date);
• Report buckets in sequence: SELECT period_sequence_num, period_name, period_start_date, next_date FROM msc_period_start_dates WHERE calendar_code = :cal ORDER BY sr_instance_id, period_sequence_num.
• Identify gaps or overlaps by comparing NEXT_DATE of one row against PERIOD_START_DATE of the next sequence number.

Because the table is relatively small and stable, it is commonly extracted in full into a data warehouse and joined to planning facts on the bucket start date.

Related Objects

The documented metadata records no foreign keys, so relationships are logical rather than enforced. The most relevant companions are MSC_CALENDARS and MSC_CALENDAR_DATES, which hold calendar definitions and individual working days that feed these period boundaries. MSC_PLANS and MSC_PLAN_ORGANIZATIONS reference calendars directly. Planning bucket facts such as MSC_SUPPLIES, MSC_DEMANDS, and MSC_SYSTEM_ITEMS carry calendar or bucket references that resolve against this table. Concurrent program context connects to FND_CONCURRENT_REQUESTS via REQUEST_ID, and the source instance identifier aligns with MSC_SR_INSTANCES used by ASCP data collections.