Results for “bom_cal_year_start_dates_u1”

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

Overview

The BOM.BOM_CAL_YEAR_START_DATES table stores the yearly start dates that govern workday calendar processing within the Oracle Bills of Material (BOM) module. In Oracle EBS, manufacturing calendars are divided into workday patterns, exception sets, and yearly boundaries; this table anchors each calendar to a specific exception set for a given year by recording the exact date on which that year begins. Downstream scheduling logic in BOM and in integrated modules such as Work in Process (WIP) and Advanced Supply Chain Planning relies on these boundaries to correctly apply shift patterns and exception rules across consecutive years.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is link. This reflects that the table primarily serves to associate independent business entities — the calendar and the exception set — across a time dimension represented by the year start date. Modelers may therefore treat it as a relationship (link) rather than as a descriptive satellite.

Key Information Stored

The table is documented with twelve columns. The most significant include:

• CALENDAR_CODE — Identifies the workday calendar to which the yearly start date applies. This is a foreign key to BOM_CALENDARS.
• EXCEPTION_SET_ID — Identifies the exception set (holidays, shutdowns, and non-working periods) associated with the calendar for that year. Foreign key to BOM_EXCEPTION_SETS.
• YEAR_START_DATE — The effective start date for the calendar year being defined.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns tracking who last modified the row and when.
• CREATION_DATE, CREATED_BY — Record creation audit information.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns populated when the row is created or updated by a batch process.

The three business-key columns — CALENDAR_CODE, EXCEPTION_SET_ID, and YEAR_START_DATE — constitute the primary key BOM_CAL_YEAR_START_DATES_PK and are additionally enforced by the unique index BOM_CAL_YEAR_START_DATES_U1. There is no separate surrogate key column; the composite business key serves directly as the primary key.

Common Use Cases and Queries

Typical scenarios include verifying calendar coverage across manufacturing years, auditing exception set assignments, and joining calendar data into production scheduling reports.

Sample query retrieving all year start dates for a calendar:

SELECT calendar_code, exception_set_id, year_start_date
FROM   bom.bom_cal_year_start_dates
WHERE  calendar_code = :p_calendar_code
ORDER BY year_start_date;

Joining to confirm the exception set description:

SELECT c.calendar_code, e.exception_set_name, d.year_start_date
FROM   bom.bom_cal_year_start_dates d,
       bom.bom_calendars           c,
       bom.bom_exception_sets      e
WHERE  d.calendar_code    = c.calendar_code
AND    d.exception_set_id = e.exception_set_id
AND    d.year_start_date BETWEEN :from_date AND :to_date;

Reporting use cases include validating that every active calendar has a defined year start date before a period close, and detecting gaps or overlaps between consecutive years that could cause scheduling errors.

Related Objects

• BOM_CALENDARS — Referenced via CALENDAR_CODE; defines the parent workday calendar.
• BOM_EXCEPTION_SETS — Referenced via EXCEPTION_SET_ID; defines non-working periods applied to the year.
• BOM_CALENDAR_SHIFTS / BOM_CAL_SHIFTS — Shift patterns that consume the yearly boundaries.
• BOM_WORKDAY_PATTERNS — Workday definitions tied to the calendar.
• BOM_CALENDAR_EXCEPTIONS — Individual dated exceptions within an exception set.

These objects form the core of the BOM workday calendar schema and are frequently queried together in manufacturing scheduling and capacity reporting.