Results for “bom_calendar_dates”

50+ results




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

Overview

BOM_CALENDAR_DATES is a transactional detail table in the Oracle Bills of Material (BOM) module that stores the individual workday calendar dates defining when manufacturing and production activities may be scheduled. Each row represents a single calendar date belonging to a specific workday calendar and exception set, providing the granular, date-level expansion of the calendar definitions held in the parent BOM_CALENDARS table. In Oracle EBS 12.1.1 and 12.2.2, this table is owned by the BOM schema and is critical to capacity planning, scheduling, and work-in-process (WIP) date calculations, because Oracle scheduling logic interrogates these rows to determine valid working days, non-working days, and shifted work patterns.

From a Data Vault modeling perspective, the heuristic classification of this object is satellite-leaning. It carries descriptive, date-specific attributes that describe the state of a parent business key (the calendar combined with an exception set), rather than acting as an independent hub or a pure relationship link. Modelers should treat CALENDAR_CODE plus EXCEPTION_SET_ID as the parent business key and CALENDAR_DATE as the effective date driving the satellite grain.

Key Information Stored

The table is composed of 17 documented columns. The most significant include the composite primary key components and the sequencing columns that describe each date's position within the calendar.

  • CALENDAR_CODE — Identifies the workday calendar to which the date belongs; part of the primary key and a foreign key to BOM_CALENDARS.
  • EXCEPTION_SET_ID — Identifies the exception set (shift pattern or holiday variation) applied to the calendar; part of the primary key and a foreign key to BOM_EXCEPTION_SETS.
  • CALENDAR_DATE — The actual workday date being defined; part of the primary key and a business-key candidate.
  • SEQ_NUM — Sequence number of the date within the calendar ordering.
  • PRIOR_SEQ_NUM / NEXT_SEQ_NUM — Pointers to the preceding and succeeding date sequence numbers, enabling traversal of the calendar chain.
  • PRIOR_DATE / NEXT_DATE — The preceding and succeeding calendar dates, supporting forward and backward date navigation without range scans.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and modified each row.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context identifying the process that populated the row.

The surrogate primary key is BOM_CALENDAR_DATES_PK, defined over (CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE). A unique index, BOM_CALENDAR_DATES_U1, over (CALENDAR_DATE, CALENDAR_CODE, EXCEPTION_SET_ID), reinforces the same business-key candidate set, confirming that no duplicate date may exist for a given calendar and exception set combination.

Common Use Cases and Queries

Typical scenarios involve verifying whether a specific date is a working day, extracting the sequence of working days for a calendar, or tracing the prior/next date chain during schedule regeneration.

  • Identify the working dates for a calendar and exception set:
    SELECT calendar_date, seq_num
    FROM   bom_calendar_dates
    WHERE  calendar_code = :code
    AND    exception_set_id = :set_id
    ORDER BY seq_num;
  • Find the next working date after a given date using the chain columns:
    SELECT next_date
    FROM   bom_calendar_dates
    WHERE  calendar_code = :code
    AND    exception_set_id = :set_id
    AND    calendar_date = :ref_date;
  • Validate alignment between an exception set and its base calendar by joining to BOM_CALENDARS and BOM_EXCEPTION_SETS.
  • Report date volumes per calendar for capacity or shutdown analysis.

Related Objects

  • BOM_CALENDARS — Parent table; joined on CALENDAR_CODE.
  • BOM_EXCEPTION_SETS — Parent table; joined on EXCEPTION_SET_ID.
  • CRP_CAL_SHIFT_DELTA — Dependent table; references (CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE) for capacity shift deltas.
  • BOM_CALENDAR_DATES_PK / BOM_CALENDAR_DATES_U1 — Primary and unique indexes enforcing calendar-date uniqueness.
  • CRP_* planning APIs — Schedule and capacity engines that read these rows to resolve workday definitions.