Results for “bom_shift_exceptions”

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

Overview

BOM.BOM_SHIFT_EXCEPTIONS is a transactional table within the Bills of Material (BOM) module of Oracle E-Business Suite, release 12.1.1 and 12.2.2. It stores workday calendar shift exceptions, meaning the discrete date-level deviations that override the standard shift pattern defined for a manufacturing or workday calendar. Where BOM_CALENDAR_SHIFTS defines the recurring shift schedule within a given calendar, BOM_SHIFT_EXCEPTIONS records the individual exception dates on which that recurring pattern is suspended, replaced, or modified — for example holidays, plant shutdowns, extended shifts, or partial working days.

This object occupies a supporting role in capacity planning, scheduling, and available-to-promise calculations. Any Oracle application or custom logic that computes available working time from a BOM calendar must consult this table to avoid overstating capacity on exception dates. The documented heuristic Data Vault classification is link, which is a modeling suggestion rather than an enforced property: the table resolves relationships among a calendar shift, an exception set, and a specific date, and its composite primary key is entirely composed of foreign and identifying columns, consistent with link-table semantics.

Key Information Stored

The table contains 30 documented columns. The primary key, enforced by BOM_SHIFT_EXCEPTIONS_PK, is the composite of CALENDAR_CODE, SHIFT_NUM, EXCEPTION_SET_ID, and EXCEPTION_DATE. A unique index, BOM_SHIFT_EXCEPTIONS_U1, covers the same four columns in the order CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, EXCEPTION_DATE. Because the primary key is purely a composite of business columns, there is no separate surrogate key; the business-key candidates and the primary key are the same attribute set.

The most significant columns are:

• CALENDAR_CODE — identifies the workday calendar to which the exception applies; part of both the primary key and the unique index, and a foreign key to BOM_CALENDAR_SHIFTS.
• SHIFT_NUM — the shift number within the calendar whose standard definition is being excepted.
• EXCEPTION_SET_ID — the exception set that classifies the deviation; a foreign key to BOM_EXCEPTION_SETS.
• EXCEPTION_DATE — the specific date on which the exception takes effect.
• EXCEPTION_TYPE — characterizes the nature of the exception at the date level.
• Audit and concurrency columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.
• Concurrent program tracking columns: REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.
• Flexfield support columns: ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15.

Common Use Cases and Queries

Typical usage centers on capacity and scheduling inquiry. A planner may need to list all exception dates for a calendar and shift within a date range:

• SELECT exception_date, exception_set_id, exception_type FROM bom.bom_shift_exceptions WHERE calendar_code = :calendar AND shift_num = :shift AND exception_date BETWEEN :from_date AND :to_date ORDER BY exception_date;

Reporting use cases include generating a plant holiday and shutdown listing for a given calendar, auditing changes to exceptions through the audit columns over a period, and feeding downstream capacity calculations that must exclude non-working dates. Reconciliation queries frequently join to BOM_EXCEPTION_SETS to translate EXCEPTION_SET_ID into a descriptive exception category, and to BOM_CALENDAR_SHIFTS to validate that an exception references an existing calendar and shift combination. Because the table carries standard WHO audit columns, change-tracking reports and interfaces loading calendar data can key on LAST_UPDATE_DATE for incremental extracts.

Related Objects

The following objects are the most significant relationships, derived from the documented foreign keys and primary key structure:

• BOM_CALENDAR_SHIFTS — referenced by BOM_SHIFT_EXCEPTIONS.CALENDAR_CODE and SHIFT_NUM; defines the base shift pattern that exceptions override.
• BOM_EXCEPTION_SETS — referenced by BOM_SHIFT_EXCEPTIONS.EXCEPTION_SET_ID; provides the grouping and description of exception types.
• BOM_CALENDARS — the parent workday calendar definition that establishes CALENDAR_CODE context through the shift table.
• Oracle Bills of Material and Capacity calendar forms and concurrent programs that maintain and consume workday calendar definitions.
• Manufacturing scheduling and capacity planning logic within the BOM and MRP modules that derives available working time from calendar shifts and their exceptions.

Because the primary key is a pure composite link structure with no surrogate column, any interface writing to this table must supply all four key columns to achieve uniqueness.