Results for “next_seq_num”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_ST_CALENDAR_DATES is an interface (staging) table in the MSC schema of Oracle E-Business Suite, owned by the Advanced Supply Chain Planning (ASCP) product. The table serves as the landing area for calendar date records collected from source systems before they are validated and promoted into the base planning table MSC_CALENDAR_DATES. During a data collection run, the collection program populates the staging table with raw source data, applies validation logic, rejects invalid rows, and transfers the surviving records to the destination table used by the planning engine.

In Oracle EBS 12.1.1 and 12.2.2 the physical structure is identical from a documentation standpoint: 23 columns, all residing in the MSC schema, with a foreign key to BOM_EXCEPTION_SETS. Under the heuristic Data Vault classification supplied in the metadata, the table is marked as standalone. This is a modeling suggestion rather than a physical design statement; it reflects the fact that MSC_ST_CALENDAR_DATES has no incoming foreign keys and only a single outgoing reference, so it behaves more like a source-staged satellite of calendar date attributes than a central hub or link. In practice it functions as a transient staging object whose rows have a limited lifecycle tied to the collection request that created them.

Key Information Stored

The table does not expose a documented single-column surrogate primary key; rows are effectively identified by the combination of the calendar business key and the collection context. The most significant columns are:

• CALENDAR_CODE – the business identifier of the calendar to which the date belongs.
• CALENDAR_DATE – the actual date value being staged for the calendar.
• SEQ_NUM, NEXT_SEQ_NUM, PRIOR_SEQ_NUM – sequence positions used to order working and non-working days and to navigate adjacent entries.
• NEXT_DATE, PRIOR_DATE – the following and preceding calendar dates in sequence.
• CALENDAR_START_DATE, CALENDAR_END_DATE – the validity window for the calendar.
• DESCRIPTION – free-text description of the date entry.
• EXCEPTION_SET_ID – the exception set associated with the date; this is the only documented foreign key and references BOM_EXCEPTION_SETS.
• DELETED_FLAG – indicates rows logically removed at the source that must be purged or flagged during validation.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE – the concurrent request and program context that produced the staged row.
• SR_INSTANCE_ID, REFRESH_ID – source system instance identifier and collection refresh identifier, which partition staged data by source and collection cycle.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – standard EBS audit columns.

Common Use Cases and Queries

The primary operational scenario is diagnosing collection failures. A planner or DBA will query the staging table by REQUEST_ID to determine which calendar dates were loaded, which failed validation, and whether the DELETED_FLAG was set correctly.

A typical diagnostic query:

• SELECT CALENDAR_CODE, CALENDAR_DATE, SEQ_NUM, DELETED_FLAG FROM MSC.MSC_ST_CALENDAR_DATES WHERE REQUEST_ID = :request_id ORDER BY CALENDAR_CODE, CALENDAR_DATE;

Reconciliation reporting compares staged rows against the destination table to confirm the collection transferred completely:

• SELECT s.CALENDAR_CODE, s.CALENDAR_DATE FROM MSC.MSC_ST_CALENDAR_DATES s WHERE s.REQUEST_ID = :request_id AND NOT EXISTS (SELECT 1 FROM MSC.MSC_CALENDAR_DATES d WHERE d.CALENDAR_CODE = s.CALENDAR_CODE AND d.CALENDAR_DATE = s.CALENDAR_DATE);

Multi-source environments use SR_INSTANCE_ID and REFRESH_ID to isolate data arriving from specific source instances, which is especially relevant when calendars are collected from multiple legacy or ERP systems. These queries are also useful during performance tuning of the collection program, since high rejection rates become visible as a large residual population in the staging table.

Related Objects

• MSC_CALENDAR_DATES – the destination planning table to which validated staged records are promoted.
• BOM_EXCEPTION_SETS – referenced via MSC_ST_CALENDAR_DATES.EXCEPTION_SET_ID; defines the exception set applicable to the staged date.
• MSC_ST_CALENDARS – the staging table for calendar headers, which supplies the parent calendar context for these date rows.
• MSC_CALENDARS – the base calendar header table corresponding to MSC_CALENDAR_DATES.
• MSC_ST_CALENDAR_SHIFTS – related staging table for shift definitions associated with the same calendars.
• FND_CONCURRENT_REQUESTS – joined on REQUEST_ID to translate collection runs into request status and timing information.
• MSC_SYSTEM_ITEMS / collection program definitions – used to trace SR_INSTANCE_ID back to the source system.