Results for “bom_resource_shifts”

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

Overview

BOM_RESOURCE_SHIFTS is a Bills of Material (BOM) module table in the Oracle E-Business Suite, owned by the BOM schema. It stores resource shift information, defining the individual shifts during which a department resource is available for capacity planning and scheduling purposes. In Oracle EBS, departments group resources such as machines, labor, and equipment. The BOM_RESOURCE_SHIFTS table records which shifts apply to a given combination of department and resource, providing the granular availability picture required by the Work in Process capacity and scheduling engines.

In Data Vault modeling terms, the heuristic classification derived from the foreign key structure is satellite-leaning. This suggests the table behaves as a descriptive satellite attached to a parent hub or link defined by the department and resource relationship, with SHIFT_NUM acting as a further qualifying attribute. Analysts building a Data Vault layer should treat the department and resource combination as the parent relationship and the remaining columns as descriptive satellite attributes.

Key Information Stored

The table is physically documented with 29 columns. The surrogate primary key is defined by the constraint BOM_RESOURCE_SHIFTS_PK, which composes the columns DEPARTMENT_ID, RESOURCE_ID, and SHIFT_NUM. Because the unique index BOM_RESOURCE_SHIFTS_U1 covers the same three columns, this combination also acts as the business-key candidate. The most important columns are as follows:

• DEPARTMENT_ID — Identifies the department owning the resource; part of the primary key and foreign key to BOM_DEPARTMENT_RESOURCES.
• RESOURCE_ID — Identifies the resource within the department; part of the primary key and foreign key to BOM_DEPARTMENT_RESOURCES.
• SHIFT_NUM — The shift number that qualifies this record; part of the primary key.
• CAPACITY_UNITS — The capacity conveyed by this shift, supporting availability and load calculations.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking who created and last modified the record.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The flexible descriptive flexfield (DFF) columns used for customer-specific extensions.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns identifying the job that last populated or updated the row.

Common Use Cases and Queries

The primary use case is exposing department-resource shift availability for capacity planning, resource scheduling, and shop-floor load reporting. A typical query joins the table to BOM_DEPARTMENT_RESOURCES to retrieve resource descriptions alongside shift capacity. For example, select B.DEPARTMENT_ID, B.RESOURCE_ID, B.SHIFT_NUM, B.CAPACITY_UNITS from BOM.BOM_RESOURCE_SHIFTS B where B.DEPARTMENT_ID = :dept and B.RESOURCE_ID = :resource; yields all shifts for a given resource.

Reporting scenarios include auditing which resources have shifts defined, validating capacity setup prior to production scheduling, and reconciling shift definitions against change records. A join with BOM_RESOURCE_CHANGES provides a historical view because that table references this table on DEPARTMENT_ID, RESOURCE_ID, and SHIFT_NUM. Because DFF attributes are present, extraction utilities often select ATTRIBUTE_CATEGORY and the ATTRIBUTE1 through ATTRIBUTE15 columns for downstream analytics.

Related Objects

The following objects are most significant in relation to BOM_RESOURCE_SHIFTS, based on the documented foreign key relationships:

• BOM_DEPARTMENT_RESOURCES — Parent table referenced by this table via DEPARTMENT_ID and RESOURCE_ID.
• BOM_RESOURCE_CHANGES — References this table on DEPARTMENT_ID, RESOURCE_ID, and SHIFT_NUM, capturing changes to resource shift definitions.
• BOM_RESOURCE_SHIFTS_PK — The primary key constraint on DEPARTMENT_ID, RESOURCE_ID, and SHIFT_NUM.
• BOM_RESOURCE_SHIFTS_U1 — The unique index covering the same business-key columns.
• BOM_RESOURCE_SHIFTS (base table under BOM schema) — Owned by the BOM schema and queried through standard EBS reporting and API layers.

Together these relationships establish BOM_RESOURCE_SHIFTS as a dependent child of BOM_DEPARTMENT_RESOURCES and a parent reference for BOM_RESOURCE_CHANGES, anchoring resource shift availability data within the BOM module.