Search Results bom_shift_dates

Overview

The BOM_SHIFT_DATES table is a core data object within the Oracle E-Business Suite Bills of Material (BOM) module, specifically for versions 12.1.1 and 12.2.2. It functions as the transactional repository for workday calendar exceptions. While standard shift patterns are defined in other calendar tables, BOM_SHIFT_DATES stores the specific dates on which a defined shift is either worked or not worked, deviating from the standard pattern. This table is essential for creating accurate manufacturing and capacity calendars, enabling precise scheduling, lead time calculation, and resource planning by accounting for holidays, planned downtime, or special working days.

Key Information Stored

The table's structure is designed to uniquely identify a calendar exception for a specific shift on a specific date. Its primary key is a composite of four columns: CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, and SHIFT_DATE. The CALENDAR_CODE identifies the master calendar, while SHIFT_NUM points to the specific shift within that calendar's definition. The EXCEPTION_SET_ID links to a group of exception rules (BOM_EXCEPTION_SETS), and SHIFT_DATE is the calendar date for which the exception applies. A record in this table signifies that for the given calendar, exception set, and shift number, the standard shift pattern is overridden on the specified SHIFT_DATE.

Common Use Cases and Queries

This table is primarily accessed for generating accurate production schedules and capacity reports. A common operational query involves listing all exception dates for a given calendar and shift to validate scheduling inputs. For reporting, it is frequently joined with BOM_CALENDAR_SHIFTS to describe the shift details and with BOM_CALENDARS to get the calendar description. A typical SQL pattern retrieves exceptions for a planning period:

• SELECT bsd.SHIFT_DATE, bcs.SHIFT_NAME FROM BOM_SHIFT_DATES bsd, BOM_CALENDAR_SHIFTS bcs WHERE bsd.CALENDAR_CODE = :p_cal_code AND bsd.EXCEPTION_SET_ID = :p_exc_set_id AND bsd.SHIFT_NUM = bcs.SHIFT_NUM AND bsd.SHIFT_DATE BETWEEN :p_date_from AND :p_date_to ORDER BY bsd.SHIFT_DATE;

Data is typically maintained via the Oracle EBS "Workday Calendar" interface, not via direct SQL manipulation.

Related Objects

BOM_SHIFT_DATES is centrally linked to other key manufacturing calendar entities through documented foreign key relationships:

• BOM_CALENDAR_SHIFTS: Defines the standard shift (SHIFT_NUM) within a calendar (CALENDAR_CODE). The join is on CALENDAR_CODE and SHIFT_NUM.
• BOM_EXCEPTION_SETS: Defines the collection of exception rules applied. The join is on EXCEPTION_SET_ID.
• CRP_CAL_SHIFT_DELTA: A Capacity (CRP) module table that likely stores net changes to capacity for scheduling. It references BOM_SHIFT_DATES via CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, and maps SHIFT_DATE to CALENDAR_DATE.