Search Results msc_cal_week_start_dates_u1

Overview

MSC.MSC_CAL_WEEK_START_DATES is a collection-planning table in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema. It stores the weekly workday calendar start dates used by the planning engine to derive valid working dates within each calendar. Because ASCP planning runs depend on a consistent, horizontally sliced calendar per source instance, this table materializes pre-computed week boundaries and their adjacent working dates, allowing the plan engine to avoid recursive date arithmetic at runtime.

The object is owned by the MSC schema and resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10. Its data is populated by the collections program, as indicated by the REFRESH_NUMBER column and the concurrent Who columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

Under a heuristic Data Vault classification mined from the FK structure, this table is treated as standalone, which suggests modeling it as a standalone satellite or reference dimension rather than as a hub or link. Its grain is one row per calendar, per source instance, per exception set, and per week start date.

Key Information Stored

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

• CALENDAR_CODE (VARCHAR2 14) — Workday calendar identifier; the business calendar this row belongs to.
• SR_INSTANCE_ID (NUMBER) — Source application instance identifier; isolates data by source system.
• EXCEPTION_SET_ID (NUMBER) — Exception set unique identifier; distinguishes calendar variants.
• WEEK_START_DATE (DATE) — The start date of the week being described.
• NEXT_DATE (DATE) — The next working date following the week start.
• PRIOR_DATE (DATE) — The prior working date preceding the week start.
• SEQ_NUM (NUMBER) — Sequence number for working days.
• REFRESH_NUMBER (NUMBER) — Refresh number populated by the collection program, used for incremental refresh identification.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent Who columns identifying the collection program run.

The surrogate primary key is MSC_CAL_WEEK_START_DATES_PK on (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, WEEK_START_DATE). The unique index MSC_CAL_WEEK_START_DATES_U1 covers the same four columns in the order (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, WEEK_START_DATE) and serves as a business-key candidate, supporting instance-first access paths.

Common Use Cases and Queries

Typical usage includes validating calendar coverage before a plan run, reconciling workday sequences, and reporting on which days are treated as working days by the planner. A representative pattern joins the calendar to itself or to planning tables filtered by instance:

• Enumerating week starts for a calendar: SELECT WEEK_START_DATE, NEXT_DATE, PRIOR_DATE, SEQ_NUM FROM MSC.MSC_CAL_WEEK_START_DATES WHERE SR_INSTANCE_ID = :instance AND CALENDAR_CODE = :cal AND EXCEPTION_SET_ID = :excl ORDER BY WEEK_START_DATE;
• Detecting stale collections by comparing REFRESH_NUMBER across runs.
• Auditing planner output using the concurrent Who columns to trace a row back to its originating request.

Related Objects

The table is referenced by MSC_PHUB_DATES_MV, the materialized view used by the planning engine to resolve working dates. It is also referenced internally by the object MSC_CAL_WEEK_START_DATES#. The ETRM metadata records no other dependency references; the table does not itself reference any database object. Joins to planning calendars are performed on the four key columns (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, WEEK_START_DATE), which is the recommended join path for downstream reporting.