Search Results msc_cal_year_start_dates_u1

Overview

MSC.MSC_CAL_YEAR_START_DATES is a transactional staging table in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema, MSC. It stores the year start dates associated with a workday calendar and exception set, as collected from one or more source applications through the MSC data collection (refresh) process. Within the planning data model, the table provides the calendar anchor points that the planning engine uses to align planning buckets, exception handling windows, and workday calculations across the supply chain planning horizon.

The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10 and is defined in FND Design Data as MSC.MSC_CAL_YEAR_START_DATES. Its status is VALID in the ETRM 12.2.2 physical schema, and it is functionally identical in the 12.1.1 and 12.2.2 releases. The table is populated by concurrent collection programs and is periodically refreshed; the REFRESH_NUMBER column and the standard concurrent WHO columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID) support refresh-cycle traceability. The heuristic Data Vault classification derived from its foreign-key structure is standalone, meaning the object carries no declared FK dependencies. From a modeling perspective, it behaves most like a satellite attached to the calendar and exception-set business keys, since it records descriptive calendar-year attributes keyed by those identifiers rather than representing an independent hub.

Key Information Stored

The table contains 14 documented columns. The most significant are:

• CALENDAR_CODE (VARCHAR2(14)) — the workday calendar identifier from the source application.
• SR_INSTANCE_ID (NUMBER) — the source application instance identifier, distinguishing data collected from multiple source systems (for example, multiple Oracle EBS instances or legacy systems).
• EXCEPTION_SET_ID (NUMBER) — the exception set unique identifier associated with the calendar.
• YEAR_START_DATE (DATE) — the calendar year start date itself, the primary business fact of the row.
• REFRESH_NUMBER (NUMBER) — the refresh cycle identifier populated during collection.
• Standard WHO columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY.
• Concurrent WHO columns: REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

The documented primary key is MSC_CAL_YEAR_START_DATES_PK on (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, YEAR_START_DATE). The unique index MSC_CAL_YEAR_START_DATES_U1 covers (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, YEAR_START_DATE) in the APPS_TS_TX_IDX tablespace — the same column set as the PK, presented in a different ordinal order. Because the PK is composed entirely of business attributes, there is no separate surrogate key column; the composite of calendar, instance, exception set, and year start date serves as the natural key.

Common Use Cases and Queries

Typical scenarios include validating that calendar year boundaries were collected correctly, diagnosing planning bucket misalignment, and auditing refresh consistency. A straightforward lookup for a given calendar and instance:

• SELECT CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, YEAR_START_DATE FROM MSC.MSC_CAL_YEAR_START_DATES WHERE CALENDAR_CODE = :cal AND SR_INSTANCE_ID = :inst ORDER BY YEAR_START_DATE;
• Identifying rows produced by a specific collection run: SELECT ... WHERE REQUEST_ID = :req_id;
• Detecting duplicate or conflicting year start dates across exception sets for the same calendar: grouping by CALENDAR_CODE, SR_INSTANCE_ID, and YEAR_START_DATE and counting distinct EXCEPTION_SET_ID values.
• Reporting the most recent refresh per source instance using MAX(REFRESH_NUMBER) or MAX(LAST_UPDATE_DATE).

Related Objects

The ETRM dependency data indicates that MSC.MSC_CAL_YEAR_START_DATES does not reference any database object and is referenced only by its synonym MSC.MSC_CAL_YEAR_START_DATES#. Joins with other MSC planning tables are therefore logical rather than enforced by foreign keys — for instance, calendar attributes align with workday calendar definitions via CALENDAR_CODE, and exception-set context aligns via EXCEPTION_SET_ID. Related objects of interest include the MSC workday calendar and exception-set planning tables that share CALENDAR_CODE and EXCEPTION_SET_ID, the MSC collection/refresh control structures keyed by SR_INSTANCE_ID and REFRESH_NUMBER, and the standard concurrent request views (FND_CONCURRENT_REQUESTS) joined on REQUEST_ID for tracing the collection program that populated each row.