Results for “bom_shift_dates”

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

Overview

BOM_SHIFT_DATES is a Bills of Material (BOM) module table in the Oracle E-Business Suite that stores the resolved workday calendar shift dates used by manufacturing scheduling and capacity calculations. Where the higher-level BOM_CALENDAR_SHIFTS table defines shift templates and BOM_EXCEPTION_SETS defines deviations from the standard pattern, BOM_SHIFT_DATES materializes the effective dated instances of those shifts — the concrete date on which a given shift number is actually performed for a given calendar and exception set. This table is central to the workday calendar engine that drives repetitive manufacturing line scheduling, capacity planning, and shop floor dispatch.

The object resides in the BOM schema and carries the Data Vault classification heuristic of satellite-leaning. In modeling terms, this suggests the table behaves as a descriptive satellite: it records the dated state of a parent calendar/shift construct rather than acting as an independent hub or a pure link between two hubs. Its identity is anchored to the combination of calendar, exception set, shift, and date, which is consistent with satellite semantics attached to a calendar-shift subject area.

Key Information Stored

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

• CALENDAR_CODE — Identifies the workday calendar to which the shift date belongs; a foreign key to BOM_CALENDAR_SHIFTS.
• EXCEPTION_SET_ID — Identifies the exception set applied to the calendar; a foreign key to BOM_EXCEPTION_SETS.
• SHIFT_NUM — The ordinal number of the shift within the calendar.
• SHIFT_DATE — The effective date on which the shift is performed; the core calendar date dimension.
• SEQ_NUM, NEXT_SEQ_NUM, PRIOR_SEQ_NUM — Sequencing pointers that order consecutive workday shifts and support navigation through the calendar sequence.
• NEXT_DATE, PRIOR_DATE — The adjacent calendar dates for the prior and next working shifts, enabling interval and duration calculations.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit columns identifying the process that generated the shift date rows.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO audit columns.

The composite primary key is BOM_SHIFT_DATES_PK, defined as (CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, SHIFT_DATE). A unique index, BOM_SHIFT_DATES_U1, is defined as (CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_DATE, SHIFT_NUM), which is the same set of columns in a different order and serves as the business-key candidate that enforces uniqueness of a shift on a date. No separate surrogate key is documented; the table uses this composite business key directly.

Common Use Cases and Queries

The primary use cases involve resolving which shifts are worked on given calendar dates, computing shift durations, and reporting available capacity. Typical patterns include:

• Retrieving all shift dates for a calendar in a date range:

SELECT shift_num, shift_date, next_date, prior_date FROM bom_shift_dates WHERE calendar_code = :calendar_code AND exception_set_id = :exception_set_id AND shift_date BETWEEN :start_date AND :end_date ORDER BY shift_date, shift_num;

• Determining the next working shift relative to a reference date using NEXT_DATE and NEXT_SEQ_NUM.
• Joining to BOM_CALENDAR_SHIFTS to obtain shift start/end times for capacity and scheduling reports.
• Supporting CRP (Capacity Requirements Planning) calculations, where shift dates anchor load placement across workday calendars.

Because the table is regenerated by concurrent programs (evidenced by the REQUEST_ID and PROGRAM_ID audit columns), reporting queries should account for the possibility that a calendar's dates may be recalculated and that row counts grow with the calendar horizon.

Related Objects

• BOM_CALENDAR_SHIFTS — Parent definition of shifts; joined on CALENDAR_CODE and SHIFT_NUM.
• BOM_EXCEPTION_SETS — Defines calendar exceptions; joined on EXCEPTION_SET_ID.
• CRP_CAL_SHIFT_DELTA — Referencing table that joins on CALENDAR_CODE, EXCEPTION_SET_ID, and SHIFT_NUM, recording shift delta information used by Capacity Requirements Planning.
• BOM_CALENDARS — Calendar header that provides the calendar context for the CALENDAR_CODE.
• BOM_WORKDAY_PATTERNS — Pattern definitions that ultimately drive the generated shift dates.
• MTL_CALENDAR_DATES and related MTL calendar objects — Complementary manufacturing calendar data used alongside BOM calendars in scheduling.

Together these objects form the workday calendar subsystem within the BOM module, with BOM_SHIFT_DATES acting as the dated, queryable output that downstream scheduling and capacity processes consume.