Search Results bom_calendar_dates_pk

Overview

The BOM_CALENDAR_DATES table is a core data structure within the Oracle E-Business Suite (EBS) Bills of Material (BOM) module, specifically for versions 12.1.1 and 12.2.2. It functions as the detailed repository for workday calendar dates, serving as the transactional child table to the master calendar definitions. Its primary role is to define the specific operational dates, including exceptions like holidays or shutdowns, for a given manufacturing or work calendar. This calendar data is fundamental to scheduling and capacity planning processes across manufacturing, supply chain planning, and shop floor execution modules, enabling the system to accurately calculate lead times, plan production schedules, and determine resource availability based on valid working days.

Key Information Stored

The table stores a record for each date managed within a defined calendar. Its structure is anchored by a composite primary key and critical foreign key relationships that ensure data integrity. The most important columns include:

• CALENDAR_CODE: References a master calendar defined in the BOM_CALENDARS table. It identifies which calendar the date record belongs to.
• EXCEPTION_SET_ID: References a set of date exceptions (e.g., company holidays) defined in the BOM_EXCEPTION_SETS table. This allows for the flexible application of non-working day rules.
• CALENDAR_DATE: The specific date entry. The combination of CALENDAR_CODE, EXCEPTION_SET_ID, and CALENDAR_DATE forms the table's primary key (BOM_CALENDAR_DATES_PK).
• Additional descriptive columns (implied by the table's purpose but not detailed in the provided metadata) typically exist to classify the date, such as indicators for whether it is a workday, a holiday, or a shifted day.

Common Use Cases and Queries

This table is central to any operation requiring date-aware logic. A common use case is validating a potential ship or production date against the work calendar. For example, a planner might query to find the next available workday after a requested date. Sample SQL patterns often involve joining to BOM_CALENDARS and filtering on date ranges and workday indicators. Reporting use cases include generating calendar exception reports for a facility or analyzing capacity utilization over a period of working days. A fundamental query retrieves all working dates for a given calendar within a date range, which is essential for scheduling algorithms in Manufacturing, CRP (Capacity Requirements Planning), and ASCP (Advanced Supply Chain Planning).

Related Objects

The BOM_CALENDAR_DATES table exists within a tightly integrated schema. As per the provided metadata, its primary relationships are:

• BOM_CALENDARS: The parent table, via foreign key on CALENDAR_CODE. This holds the master calendar definition.
• BOM_EXCEPTION_SETS: The parent table for exception rules, via foreign key on EXCEPTION_SET_ID.
• CRP_CAL_SHIFT_DELTA: A dependent table that references BOM_CALENDAR_DATES' key columns. This table is used in Capacity Planning to manage shift-level adjustments to the calendar, demonstrating how the base calendar dates are extended for detailed scheduling.

Furthermore, this table is indirectly referenced by numerous planning and scheduling engines throughout Oracle EBS, which consume the workday calendar data to perform time-phased calculations.