Search Results exception_date




Overview
Explore in AI Chat

Overview

The BOM_EXCEPTION_SET_DATES table is a core data object within the Bills of Material (BOM) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It functions as a detail table for defining workday exception templates. These templates are critical for manufacturing and supply chain planning, as they allow users to specify specific calendar dates—such as holidays or planned shutdowns—that are exceptions to the standard work schedule. By defining these non-working days, the system can accurately calculate manufacturing lead times, schedule production orders, and perform capacity planning, ensuring that plans reflect real-world operational calendars.

Key Information Stored

The table stores a simple but essential pair of data points for each exception date. Its structure is defined by a composite primary key to ensure uniqueness. The key columns are:

  • EXCEPTION_SET_ID: A foreign key that links the date to a specific exception set or template defined in the parent table, BOM_EXCEPTION_SETS. This ID groups individual exception dates into a logical calendar.
  • EXCEPTION_DATE: The specific calendar date that is designated as a non-working day or exception within the context of the associated EXCEPTION_SET_ID. This column is the core data element that defines the actual day to be excluded from planning calculations.

Common Use Cases and Queries

The primary use case is the maintenance and application of workday calendars for manufacturing and planning engines. A common operational task is to query all exception dates for a given calendar to validate or report on plant holidays. A typical SQL pattern would be:

SELECT exception_date FROM bom_exception_set_dates WHERE exception_set_id = :p_calendar_id ORDER BY exception_date;

For integration or data validation purposes, one might need to identify exception sets that contain a specific date, which can be achieved with a query such as:

SELECT bes.exception_set_name, besd.exception_date FROM bom_exception_set_dates besd JOIN bom_exception_sets bes ON besd.exception_set_id = bes.exception_set_id WHERE besd.exception_date = TO_DATE('25-DEC-2023', 'DD-MON-YYYY');

This table is also pivotal in background processing for modules like Master Production Schedule (MPS) and Material Requirements Planning (MRP), where the planning engine references these dates to skip scheduling activities.

Related Objects

The BOM_EXCEPTION_SET_DATES table has a direct and singular parent-child relationship within the BOM schema, as documented in the provided metadata.

  • BOM_EXCEPTION_SETS: This is the primary parent table. The relationship is enforced by a foreign key constraint where BOM_EXCEPTION_SET_DATES.EXCEPTION_SET_ID references BOM_EXCEPTION_SETS.EXCEPTION_SET_ID. The BOM_EXCEPTION_SETS table holds the header information, such as the exception set name and description, while BOM_EXCEPTION_SET_DATES holds the detailed dates for each set.

While not explicitly listed in the provided excerpt, this exception date data is ultimately referenced by planning parameters and manufacturing calendars used throughout the Manufacturing, Planning, and Costing suites to define working days.