Search Results bom_item_attach_category_assoc




Overview
Explore in AI Chat

Overview

BOM_ITEM_ATTACH_CATEGORY_ASSOC is a reference table in the Oracle Bills of Material (BOM) module, owned by the BOM schema. Its documented purpose is to hold the association between structure types and item attachment categories. In practical terms, the table defines which document or media attachment categories are permitted (or applicable) for a given manufacturing structure type, so that attachments linked to items, bills of material, or routings are validated against the correct category set during data entry and processing.

The table is small and configuration-oriented. It carries no transactional volume of its own; instead it constrains and classifies attachment behavior across the BOM and Engineering modules. Because it holds only key columns plus standard WHO audit columns, the ETRM metadata classifies it heuristically as standalone within a Data Vault model. As a modeling suggestion, that classification indicates the row set is a stable reference or cross-reference set rather than a hub of a business entity or a link between two independently tracked entities; no foreign key dependencies were mined from its physical structure.

Key Information Stored

The documented physical schema contains seven columns. The two business columns form both the primary key and a unique business key, which is unusual and worth noting for developers.

  • ATTACH_CATEGORY_ID — identifies the item attachment category being associated. This is the first column of the composite primary key BOM_ITEM_ATTACH_CATEGORY_PK.
  • STRUCTURE_TYPE_ID — identifies the structure type to which the attachment category applies. It is the second column of the composite primary key.
  • CREATED_BY — the user ID that inserted the association row.
  • CREATION_DATE — the date and time the association row was created.
  • LAST_UPDATED_BY — the user ID that most recently modified the row.
  • LAST_UPDATE_DATE — the date and time of the most recent modification.
  • LAST_UPDATE_LOGIN — the login identifier associated with the most recent update, used for audit and concurrent-program tracking.

The surrogate primary key, BOM_ITEM_ATTACH_CATEGORY_PK, is composed of (ATTACH_CATEGORY_ID, STRUCTURE_TYPE_ID). A separate unique index, BOM_ITEM_ATTACH_CATEGORY_U1, is defined on (STRUCTURE_TYPE_ID, ATTACH_CATEGORY_ID) — the same two columns in reverse order. This means the table is effectively a pure intersection or cross-reference table: it holds no attributes beyond the audit columns, and both key orderings are guaranteed unique. The ATTACH_CATEGORY_ID and STRUCTURE_TYPE_ID columns are the only business-key candidates documented.

Common Use Cases and Queries

Typical usage centers on validating attachment categories against structure types, and on reporting which categories are enabled for each structure type. Because the table contains no descriptive columns, queries invariably join outward to the category and structure type definition tables.

  • Configuration review. Determine which attachment categories are associated with a structure type before enabling or disabling attachment entry.
  • Existence checks. Confirm that a category is valid for a structure type during data loading or interface validation.
  • Impact analysis. Identify all structure types affected if a given attachment category is retired or reclassified.
  • Audit and lineage. Use CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY to trace when an association was introduced or changed.

A representative query pattern is:

SELECT a.ATTACH_CATEGORY_ID, a.STRUCTURE_TYPE_ID, a.CREATION_DATE, a.LAST_UPDATED_BY FROM BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC a WHERE a.STRUCTURE_TYPE_ID = :p_structure_type ORDER BY a.ATTACH_CATEGORY_ID;

For reporting, join ATTACH_CATEGORY_ID to the attachment category definition and STRUCTURE_TYPE_ID to the structure type definition so the report shows meaningful names rather than numeric identifiers.

Related Objects

The ETRM metadata documents no mined foreign keys for this table, so the relationships below are functional rather than enforced by database constraints. The most significant related objects are:

  • Attachment category definition — the master table providing the description for ATTACH_CATEGORY_ID; join on ATTACH_CATEGORY_ID.
  • Structure type definition — the master table providing the description for STRUCTURE_TYPE_ID; join on STRUCTURE_TYPE_ID.
  • BOM_ITEM_ATTACH_CATEGORIES — the item attachment categories themselves, referenced through ATTACH_CATEGORY_ID.
  • BOM_STRUCTURE_TYPES — the structure type master, referenced through STRUCTURE_TYPE_ID.
  • BOM_BILL_OF_MATERIALS — bills whose structure types depend on the associations defined here.
  • BOM_OPERATIONAL_ROUTINGS — routings governed by the same structure type assignment.
  • FND_ATTACHED_DOCUMENTS — the attachment repository whose category values are validated against this association set.
  • BOM_ITEM_ATTACHMENTS — item-level attachment records that consume the permitted category set.

Because no enforced foreign keys exist, referential integrity for these relationships is maintained by the application layer, and any direct SQL against the table should account for orphaned identifiers when joining to the master definitions.