Search Results msc_shift_times_u1

Overview

MSC.MSC_SHIFT_TIMES is a planning-side reference table in the Oracle E-Business Suite MSC schema (Advanced Supply Chain Planning / VCP). It stores the shift-hour definitions that make up a workday calendar used by the planning engine. Each row represents one shift within a calendar, expressed as a start and an end time held in seconds. The table is populated by the planning collection programs that copy calendar and shift data from a source application instance into the MSC schema, which is why every row carries an SR_INSTANCE_ID.

Two business rules are intrinsic to the object. Shift hours within the same calendar may not overlap, and a shift may legitimately extend past midnight (beyond 24:00). Because FROM_TIME and TO_TIME are stored as numeric seconds and not as dates, a shift crossing midnight is represented arithmetically rather than by a date rollover. The table is keyed by a composite primary key, MSC_SHIFT_TIMES_PK, and is also constrained by a unique index, MSC_SHIFT_TIMES_U1.

Following heuristic Data Vault classification, the object resolves to a standalone structure rather than a hub, link, or satellite. It has no documented foreign key dependents, and its natural business key (calendar, instance, shift, and time boundaries) is fully contained within the table itself. A Data Vault model would most plausibly treat MSC_SHIFT_TIMES as a reference or dimension-style structure keyed on the same composite attributes.

Key Information Stored

The table contains 31 documented columns, of which the time-defining and ownership attributes are of greatest functional importance.

• SR_INSTANCE_ID (NUMBER) — identifies the source application instance from which the calendar was collected. It is a mandatory part of the primary key and of the unique index, reflecting the multi-instance nature of the planning schema.
• CALENDAR_CODE (VARCHAR2, 14) — the workday calendar identifier to which the shift belongs. This is the principal business key for grouping shift definitions.
• SHIFT_NUM (NUMBER) — the ordinal number of the shift within the calendar, allowing several shifts per working day.
• FROM_TIME (NUMBER) — the shift start time expressed in seconds from the start of the day.
• TO_TIME (NUMBER) — the shift end time in seconds; values greater than 86400 indicate a shift running beyond midnight.
• REFRESH_NUMBER (NUMBER) — populated by the collection program and used to identify the refresh generation of the collected row.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — the concurrent Who columns that identify the planning collection request and program that wrote the row.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard Who audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield columns available for customer-defined extensions on the shift definition.

The surrogate primary key is defined as the combination of CALENDAR_CODE, SR_INSTANCE_ID, SHIFT_NUM, FROM_TIME, and TO_TIME. The unique index MSC_SHIFT_TIMES_U1 covers the same five columns in the order SR_INSTANCE_ID, CALENDAR_CODE, SHIFT_NUM, FROM_TIME, TO_TIME, which makes it the practical business-key access path.

Common Use Cases and Queries

The most frequent use of this table is to determine working capacity for a calendar so that resource and supplier capacity can be spread across the correct hours of the day. A typical query returns all shifts for a given calendar, ordered by shift number:

• SELECT shift_num, from_time, to_time FROM msc.msc_shift_times WHERE sr_instance_id = :instance AND calendar_code = :calendar ORDER BY shift_num;
• To detect shifts crossing midnight, filter on TO_TIME greater than 86400, since 24 hours equals 86,400 seconds.
• To convert the numeric values into readable times, apply simple arithmetic, for example dividing FROM_TIME by 3600 to obtain hours.
• To audit collection activity, join REQUEST_ID to the concurrent request tables or filter by REFRESH_NUMBER to isolate the latest refresh generation.

Reporting areas that draw on this data include capacity planning, calendar validation (verifying that no two shifts overlap), and reconciliation of planning calendars against the source instance calendars.

Related Objects

MSC_SHIFT_TIMES is a reference detail of the calendar definition hierarchy in the MSC schema. The most significant related objects, joined on the documented key columns, are:

• MSC_CALENDARS — the parent calendar definition, joined on CALENDAR_CODE and SR_INSTANCE_ID.
• MSC_WORKDAY_PATTERNS and associated workday exception tables — define which days are working days for the same calendar and instance.
• MSC_SR_INSTANCES — the source instance registry, joined on SR_INSTANCE_ID to resolve instance names.
• MSC_SYSTEM_ITEMS and MSC_SUPPLIER_SITES — planning entities whose capacity is expressed against a calendar code.
• MSC_SHIFT_TIMES_U1 and MSC_SHIFT_TIMES_PK — the unique index and primary key constraint that enforce the no-overlap, single-definition rule.
• Planning collection concurrent programs that populate the MSC calendar set, identifiable through REQUEST_ID and PROGRAM_ID on each row.