Search Results bom_cal_year_start_dates




Overview
Explore in AI Chat

Overview

The BOM_CAL_YEAR_START_DATES table is a core data object within the Oracle E-Business Suite Bills of Material (BOM) module. It functions as a supporting table for the workday calendar system, which is critical for scheduling and capacity planning in manufacturing and supply chain operations. Its primary role is to define the specific start date for each fiscal or operational year within a given workday calendar. This table works in conjunction with the BOM_CALENDARS and BOM_EXCEPTION_SETS tables to provide a complete definition of working and non-working days, enabling accurate lead time calculations, shop floor scheduling, and material requirement planning (MRP).

Key Information Stored

The table stores a unique combination of identifiers and a date to demarcate yearly boundaries within a calendar. The primary key, BOM_CAL_YEAR_START_DATES_PK, is a composite key consisting of three columns, ensuring a unique year start date for a specific calendar and exception set combination. The key columns are CALENDAR_CODE, which identifies the specific workday calendar; EXCEPTION_SET_ID, which links to a set of non-working day exceptions; and YEAR_START_DATE, which stores the actual date that marks the beginning of a year within that calendar context. This structure allows for the same base calendar to have different yearly start dates when paired with different exception sets, providing flexibility for various planning scenarios.

Common Use Cases and Queries

This table is essential for any process that requires temporal calculations based on workdays. A primary use case is generating a rolling workday calendar for forward or backward scheduling from a given order date. For reporting, it is used to aggregate production or capacity data by fiscal periods that are not aligned with the Gregorian calendar. A common query pattern involves joining this table to BOM_CALENDARS and BOM_CALENDAR_DATES to retrieve all working days for a specific year. For example, to find the working days for the 2024 fiscal year in the 'US_MFG' calendar with a standard exception set, a query would filter on YEAR_START_DATE and use a date range function to generate the period, then join to BOM_CALENDAR_DATES to exclude non-work days.

Related Objects

The BOM_CAL_YEAR_START_DATES table has defined foreign key relationships with two other fundamental BOM tables, as documented in the ETRM metadata. It is a child table to BOM_CALENDARS, referenced via the CALENDAR_CODE column. This relationship ties the yearly start date to a specific, defined workday calendar. It is also a child table to BOM_EXCEPTION_SETS, referenced via the EXCEPTION_SET_ID column. This links the yearly definition to a specific collection of holidays and other non-working day exceptions. These relationships mean that a record in BOM_CAL_YEAR_START_DATES cannot exist without a corresponding parent record in both BOM_CALENDARS and BOM_EXCEPTION_SETS, enforcing data integrity for the calendar structure.