Search Results msc_calendar_dates_u1

Overview

MSC.MSC_CALENDAR_DATES is a supply chain planning table in the MSC schema that stores every date between a calendar's start date and end date for a given calendar, resolved against a specified exception set. It is populated by the data collection program that gathers calendar and exception information from source applications, and it serves as the time-dimension backbone for planning engines such as Oracle Advanced Supply Chain Planning and Demand Planning. Calendars represented here include manufacturing, shipping, receiving, and general workday calendars, with exception sets overriding the default working-day pattern for holidays, shutdowns, and other non-working periods.

The table resides in the APPS_TS_TX_DATA tablespace with a PCTFree of 10, while all four of its indexes live in APPS_TS_TX_IDX. Its primary key, MSC_CALENDAR_DATES_PK, is defined on (SR_INSTANCE_ID, CALENDAR_DATE, CALENDAR_CODE, EXCEPTION_SET_ID). From a Data Vault modeling perspective, the metadata's mined classification is standalone, meaning no foreign key dependencies were detected in the physical structure; the table behaves as a self-contained reference/satellite-style entity keyed by its natural business compound rather than participating in an explicit hub-and-link topology.

Key Information Stored

The table contains 39 documented columns. The most operationally significant are:

• CALENDAR_DATE — the individual date row generated between the calendar boundaries.
• CALENDAR_CODE — identifies which calendar the date belongs to (for example a workday or shipping calendar).
• EXCEPTION_SET_ID — the exception set applied when resolving working versus non-working days.
• SR_INSTANCE_ID — the source application instance from which the calendar was collected, enabling multi-source consolidation.
• SEQ_NUM — sequence number assigned to working days only.
• NEXT_SEQ_NUM / PRIOR_SEQ_NUM — sequence numbers of the adjacent working days.
• NEXT_DATE / PRIOR_DATE — the actual dates corresponding to the next and prior sequence numbers.
• CALENDAR_START_DATE / CALENDAR_END_DATE — the boundary dates of the calendar definition.
• DESCRIPTION — calendar description text.
• REFRESH_NUMBER — populated by the collection program to identify the refresh cycle that produced the row.
• DELETED_FLAG — soft-delete indicator.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent Who columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

MSC_CALENDAR_DATES_U1 is the unique index on (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE), making it the strongest business-key candidate. The three non-unique indexes — MSC_CALENDAR_DATES_N1 on (NEXT_DATE, CALENDAR_CODE, EXCEPTION_SET_ID, SR_INSTANCE_ID), N2 on the PRIOR_DATE equivalents, and N3 on (SEQ_NUM, CALENDAR_DATE, CALENDAR_CODE, EXCEPTION_SET_ID, SR_INSTANCE_ID) — support forward/backward navigation and sequence-based lookups.

Common Use Cases and Queries

Typical scenarios include determining the next working day for a plan, counting working days in a period for capacity calculations, and joining planned orders or schedules to a valid calendar date. A representative lookup for the next working day is:

• SELECT NEXT_DATE FROM MSC.MSC_CALENDAR_DATES WHERE SR_INSTANCE_ID = :instance AND CALENDAR_CODE = :cal AND EXCEPTION_SET_ID = :exc AND CALENDAR_DATE = :dt;
• Counting working days: SELECT COUNT(*) FROM MSC.MSC_CALENDAR_DATES WHERE CALENDAR_CODE = :cal AND EXCEPTION_SET_ID = :exc AND CALENDAR_DATE BETWEEN :start AND :end AND SEQ_NUM IS NOT NULL;
• Nth working day from a start date: use SEQ_NUM ordering to retrieve the row whose sequence equals start sequence plus N.
• Reporting must always constrain on SR_INSTANCE_ID, CALENDAR_CODE, and EXCEPTION_SET_ID together, since a single calendar date exists once per instance/calendar/exception-set combination.

Because the table is refreshed by a collection program, queries in reporting should filter on REFRESH_NUMBER or account for superseded rows. Enforcement of the unique index through the U1 columns guarantees that no duplicate date row can exist for the same compound key.

Related Objects

The metadata classifies the table as standalone with no documented foreign keys, so related objects are driven by shared business keys rather than declared referential constraints. Practical relationships include:

• MSC.MSC_CALENDARS — joined on CALENDAR_CODE and SR_INSTANCE_ID to obtain calendar header definitions and start/end dates.
• MSC.MSC_EXCEPTIONS / exception set tables — joined on EXCEPTION_SET_ID and SR_INSTANCE_ID to resolve holiday and shutdown patterns.
• MSC.MSC_SR_INSTANCES — joined on SR_INSTANCE_ID to identify the source application instance.
• MSC.MSC_SYSTEM_ITEMS / MSC.MSC_ITEM_ORG_CATEGORIES — calendars are referenced during item-org planning to derive lead times and workdays.
• MSC.MSC_SUPPLIER_CAPACITIES and resource capacity tables — capacity buckets align to working days defined here.
• MSC.MSC_PLANNED_ORDERS / MSC.MSC_DEMANDS — planning dates are validated against and shifted using this calendar.
• Collection program APIs — the calendar/exception collection concurrent programs that insert, refresh, and set DELETED_FLAG on rows in this table.

Because no FK constraints are documented, cross-table integrity is maintained by the collection program rather than by the database, and analysts should validate joins on the full compound key (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE) to avoid accidental Cartesian results.