Results for “bom_exception_sets”

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

Overview

BOM_EXCEPTION_SETS is a Bills of Material (BOM) module table that defines named exception sets used by Oracle EBS manufacturing and inventory calendars. An exception set is the grouping construct that collects date-specific calendar deviations — holidays, plant shutdowns, alternate shift patterns, and non-working periods — so that a single set can be attached to a workday calendar rather than maintaining exceptions date-by-date for every calendar. The table is owned by the BOM schema and is classified as VALID in the EBS 12.1.1 and 12.2.2 data dictionaries.

Because the table holds the identity and descriptive attributes of the exception set itself rather than the transactional exception dates, the heuristic Data Vault classification derived from its foreign-key structure is hub. Within a Data Vault model, BOM_EXCEPTION_SETS would function as the business-key hub for exception sets, with the associated BOM_EXCEPTION_SET_DATES and BOM_CALENDAR_EXCEPTIONS tables acting as satellites or links supplying the dated detail.

Key Information Stored

The table contains 30 documented columns. The most significant are:

• EXCEPTION_SET_ID — surrogate primary key, enforced by BOM_EXCEPTION_SETS_PK. This is the value propagated to every dependent table.
• EXCEPTION_SET_NAME — the user-facing identifier of the exception set; the business-key candidate documented in unique index BOM_EXCEPTION_SETS_UK1.
• DESCRIPTION — free-text explanation of the set's purpose, typically describing the plant, region, or shift pattern it covers.
• DISABLE_DATE — the date after which the set is no longer active, enabling retirement without deletion.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS audit columns identifying who created and last changed the record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard fifteen descriptive flexfield columns available for customer-specific extensions.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context, recording which request populated or last modified the row.
• ZD_EDITION_NAME — the editioning column present in the 12.2.2 Online Patching architecture, participating in unique indexes BOM_EXCEPTION_SETS_U1 and BOM_EXCEPTION_SETS_U2.

The documented unique indexes give two business-key candidates: BOM_EXCEPTION_SETS_U1 on (EXCEPTION_SET_NAME, ZD_EDITION_NAME) and BOM_EXCEPTION_SETS_U2 on (EXCEPTION_SET_ID, ZD_EDITION_NAME).

Common Use Cases and Queries

Typical usage centers on validating, reporting, and migrating manufacturing calendar definitions. A frequent query lists active sets and their dependent exception dates:

SELECT es.exception_set_id, es.exception_set_name, es.description, ed.exception_date
FROM   bom.bom_exception_sets es,
       bom.bom_exception_set_dates ed
WHERE  es.exception_set_id = ed.exception_set_id
AND    (es.disable_date IS NULL OR es.disable_date > SYSDATE);

Inventory organizations reference exception sets through MTL_PARAMETERS.CALENDAR_EXCEPTION_SET_ID, so a common joining pattern resolves the exception set applied to each organization. Cycle counting setups reference the same object through MTL_CYCLE_COUNT_HEADERS.CALENDAR_EXCEPTION_SET, allowing users to exclude holidays when scheduling count dates. During data migration or cloning, DBAs verify which exception sets are still referenced before purging, and developers use the ATTRIBUTE columns to carry customer-specific identifiers without schema changes.

Related Objects

The table is referenced by a broad set of BOM and planning objects:

• BOM_EXCEPTION_SET_DATES — supplies the individual exception dates belonging to a set, joined on EXCEPTION_SET_ID.
• BOM_CALENDAR_EXCEPTIONS, BOM_CALENDAR_DATES, BOM_SHIFT_EXCEPTIONS, BOM_SHIFT_DATES — calendar and shift exception definitions tied to the set.
• BOM_CAL_WEEK_START_DATES, BOM_CAL_YEAR_START_DATES, BOM_PERIOD_START_DATES — week, year, and period boundary definitions for the calendar.
• MTL_PARAMETERS — references the set via CALENDAR_EXCEPTION_SET_ID for each inventory organization.
• MTL_CYCLE_COUNT_HEADERS — references the set via CALENDAR_EXCEPTION_SET for cycle count scheduling.
• MSC_CALENDAR_EXCEPTIONS, MSC_ST_CALENDAR_DATES, MSC_PHUB_MFG_CAL_DATES_MV — Planning Central collections and materialized views that stage the same exception data.

Any deletion or renumbering of an EXCEPTION_SET_ID must respect these foreign keys across the BOM and MTL schemas.