Search Results bom_resource_employees




Overview
Explore in AI Chat

Overview

BOM_RESOURCE_EMPLOYEES is a Bills of Material (BOM) module table that stores the association between manufacturing resources of type "employee" and the specific persons (employees) who are qualified to perform work against those resources. In Oracle EBS, resources defined in the BOM module can represent people, equipment, or expense pools used in routings and work definitions. When a resource is defined as an employee-type resource, the roster of qualified persons is persisted in this table, allowing capacity planning, scheduling, and costing to reference a definite pool of labor rather than an abstract quantity.

The table is owned by the BOM schema and is classified as VALID in the ETRM 12.2.2 data model. Its documented physical schema contains 32 columns. From a relational modeling perspective, the heuristic Data Vault classification mined from the foreign key structure is standalone, which suggests the object is best modelled as a link or reference table rather than as a transactional hub or satellite. Practically, it functions as an intersection between the manufacturing resource entity and the HR person entity, scoped by organization and dated by effective periods.

Key Information Stored

The primary key BOM_RESOURCE_EMPLOYEES_PK is defined across five columns: RESOURCE_ID, ORGANIZATION_ID, PERSON_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This composite key establishes that a person may be attached to a resource within a given inventory organization only for a bounded, date-effective window. Because every key component is meaningful business data, the primary key also serves as a natural business key rather than a pure surrogate.

  • RESOURCE_ID — identifies the BOM resource record to which the employee is assigned.
  • ORGANIZATION_ID — scopes the assignment to a specific inventory organization, since resources are organization-specific in EBS.
  • PERSON_ID — the HR person (employee) qualified for the resource.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the valid-from and valid-to dates controlling date-effective querying.
  • INSTANCE_ID — a unique identifier captured by index BOM_RESOURCE_EMPLOYEES_U1, typically used for data migration or interface row tracking.
  • INTERLEAVE — supports interleaved resource assignment semantics within manufacturing schedules.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns reserved for customer-specific extensions.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program traceability columns identifying the process that last wrote the row.

The unique index BOM_RESOURCE_EMPLOYEES_U2 mirrors the primary key columns, reinforcing the rule that resource, organization, person, and effective dates together form the authoritative business key.

Common Use Cases and Queries

Typical use cases include reporting on which employees are currently attached to a given resource, auditing labor qualification rosters by organization, and validating date-effective overlaps before loading new assignments. A common query pattern filters on the effective range relative to the current date:

  • List active employees for a resource: SELECT person_id FROM bom_resource_employees WHERE resource_id = :p_res AND organization_id = :p_org AND SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, SYSDATE + 1);
  • Detect overlapping assignments for the same resource/person before insert, to avoid violating the PK and U2 constraints.
  • Join to resource definitions to reconcile employee-type resources with their qualified pool.
  • Extract roster data for capacity and scheduling reports, or reconcile against HR person records.

Related Objects

BOM_RESOURCE_EMPLOYEES does not carry documented foreign keys in the ETRM relationship data (classified as standalone), so joins are convention-based through the key columns. Significant related objects include:

  • BOM_RESOURCES — join on RESOURCE_ID (and ORGANIZATION_ID) to obtain the resource definition, type, and unit of measure.
  • BOM_RESOURCE_CHARGES — associates resources with charge accounts; join through RESOURCE_ID.
  • BOM_DEPARTMENTS / BOM_DEPARTMENT_RESOURCES — department-to-resource mappings relevant to labor routing.
  • BOM_OPERATION_RESOURCES — links resources to routing operations, connecting employee rosters to work definitions.
  • PER_ALL_PEOPLE_F (HR) — join on PERSON_ID to retrieve employee name and assignment details.
  • HR/HRMS employee APIs — upstream source of PERSON_ID values.
  • BOM_RESOURCE_EMPLOYEES_* indexes and the concurrent program columns — used for validation and interface processing.