Search Results bom_item_attach_category_u1

Overview

BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC is a reference table in the Oracle E-Business Suite Bills of Material (BOM) schema. It stores association information linking Item Attachment Categories to BOM Structure Types. In other words, it defines which attachment categories are permitted (or valid) for a given structure type, allowing the application to control and validate the attachment behavior associated with different bill of material structures.

The table is owned by the BOM schema and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its status is VALID and it is registered as FND Design Data under BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC. The documented schema contains seven columns.

From a data modeling perspective, the metadata classifies this object heuristically as standalone within the Data Vault framework. Because the table carries no documented foreign key references to other database objects, it may be modeled most simply as a standalone table rather than as a hub, link, or satellite. Its two business columns — STRUCTURE_TYPE_ID and ATTACH_CATEGORY_ID — combine to form the primary key, which makes the table function as an associative or intersection entity connecting the two domains.

Key Information Stored

The most significant columns in this table are the two key attributes that define each association, together with the standard Oracle WHO audit columns:

• STRUCTURE_TYPE_ID (NUMBER) — The BOM structure type identifier. This identifies the type of bill of material structure to which the attachment category association applies. It is the first column in the unique index BOM_ITEM_ATTACH_CATEGORY_U1.
• ATTACH_CATEGORY_ID (NUMBER) — The attachment category identifier. It identifies the attachment category being associated with the structure type. It is the second column in the unique index BOM_ITEM_ATTACH_CATEGORY_U1.
• CREATED_BY (NUMBER) — Standard WHO audit column recording the user who created the row.
• CREATION_DATE (DATE) — Standard WHO audit column recording when the row was created.
• LAST_UPDATED_BY (NUMBER) — Standard WHO audit column recording the user who last updated the row.
• LAST_UPDATE_DATE (DATE) — Standard WHO audit column recording when the row was last updated.
• LAST_UPDATE_LOGIN (NUMBER) — Standard WHO audit column recording the login associated with the last update.

There is an important distinction between the documented primary key and the documented unique index. The metadata identifies BOM_ITEM_ATTACH_CATEGORY_PK with the column order (ATTACH_CATEGORY_ID, STRUCTURE_TYPE_ID), while the unique business-key candidate BOM_ITEM_ATTACH_CATEGORY_U1 is defined on (STRUCTURE_TYPE_ID, ATTACH_CATEGORY_ID). Both enforce uniqueness over the same pair of columns, but in a different order. When writing queries or designing joins, the index column order of BOM_ITEM_ATTACH_CATEGORY_U1 (STRUCTURE_TYPE_ID first) is the one to consider for access path optimization when filtering by structure type.

The unique index resides in the APPS_TS_TX_IDX tablespace, separating index storage from table data storage. No surrogate or generated key column is documented; the natural composite key of the two business identifiers serves as the primary identifier.

Common Use Cases and Queries

Because this table governs the relationship between structure types and attachment categories, it is typically consulted when displaying or validating attachment categories available for a particular structure type. A common reporting pattern retrieves all attachment categories associated with a given structure type:

• Look up permitted attachment categories by structure type: SELECT ATTACH_CATEGORY_ID FROM BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC WHERE STRUCTURE_TYPE_ID = :p_structure_type_id;
• Reverse lookup of structure types by attachment category: SELECT STRUCTURE_TYPE_ID FROM BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC WHERE ATTACH_CATEGORY_ID = :p_attach_category_id;
• Full association extract for reconciliation or migration: SELECT STRUCTURE_TYPE_ID, ATTACH_CATEGORY_ID, LAST_UPDATE_DATE FROM BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC;
• Audit of recent configuration changes using the WHO columns: SELECT * FROM BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC WHERE LAST_UPDATE_DATE >= :p_since_date;

Typical use cases include validating that a selected attachment category is legal for the current structure type, driving LOV (list of values) queries in attachment-enabled BOM forms, and auditing configuration changes through the LAST_UPDATED_BY and LAST_UPDATE_DATE columns. The unique index BOM_ITEM_ATTACH_CATEGORY_U1 supports efficient retrieval when queries are filtered on STRUCTURE_TYPE_ID, since that column leads the index.

Related Objects

The documented dependency information states that BOM.BOM_ITEM_ATTACH_CATEGORY_ASSOC does not reference any database object, and that it is referenced only by its own synonym or internal definition, BOM_ITEM_ATTACH_CATEGORY_ASSO#. Consequently, the relationship data classifies the object as standalone with no documented foreign key dependencies.

Despite the absence of explicit documented foreign keys, the two key columns imply logical relationships to the parent domains they identify. The STRUCTURE_TYPE_ID column logically references the BOM structure type definitions used throughout the Bills of Material module, while ATTACH_CATEGORY_ID logically references the attachment category definitions maintained for document and item attachments. These logical associations are enforced by application logic rather than by declared database constraints.

Because no explicit references are documented, joins to related tables should be constructed on the business identifier columns STRUCTURE_TYPE_ID and ATTACH_CATEGORY_ID rather than on a surrogate key. When integrating with this table, reconciliation reports should be run against the source category and structure type definitions to confirm that no orphaned associations exist, given the absence of enforced foreign keys.