Search Results ahl_item_associations_b_pk

Overview

AHL_ITEM_ASSOCIATIONS_B is a base intersection table in the Oracle E-Business Suite product AHL – Complex Maintenance Repair and Overhaul. It stores the association between an item group and a specific inventory item, effectively defining which inventory items are considered valid members of a given item group within a specified inventory organization. The table resides in the AHL schema and is marked VALID in the ETRM 12.2.2 physical schema, with 34 documented columns.

Because the "AHL" product governs complex MRO bills of material, work orders, and maintenance tasks, this table acts as the membership bridge that maps the generic grouping construct (item group) to the concrete consumable or repairable item. In Data Vault terms, the heuristic classification is hub-leaning, meaning it can be modeled as a hub-like entity whose natural identity is the (item group, inventory org, inventory item) combination, with associated descriptive attributes carried alongside. This is a modeling suggestion rather than a strict constraint; in a normalized EBS physical model the table functions as an intersection/link between item groups and inventory items.

Key Information Stored

The primary key is the surrogate ITEM_ASSOCIATION_ID (index AHL_ITEM_ASSOCIATIONS_B_PK), which also appears as a single-column unique index ..._U1. A second unique index, ..._U2, defines the business-key candidate as (ITEM_GROUP_ID, INVENTORY_ORG_ID, INVENTORY_ITEM_ID). The most significant columns are:

• ITEM_ASSOCIATION_ID – surrogate primary key for each association record.
• ITEM_GROUP_ID – the item group to which the item is associated; foreign key to AHL_ITEM_GROUPS_B.
• INVENTORY_ITEM_ID – the inventory item (from MTL_SYSTEM_ITEMS) being associated.
• INVENTORY_ORG_ID – the inventory organization scoping the association.
• PRIORITY – ordering/priority of the item association.
• UOM_CODE – unit of measure for the associated item.
• QUANTITY – quantity associated with the item in the group.
• REVISION – item revision reference.
• INTERCHANGE_TYPE_CODE and ITEM_TYPE_CODE – classification codes for interchange/type.
• SECURITY_GROUP_ID – foreign key to FND_SECURITY_GROUPS for row-level security.
• SOURCE_ITEM_ASSOCIATION_ID – self-referencing source for copied/derived records.
• OBJECT_VERSION_NUMBER – optimistic locking column.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1..15 – descriptive flexfield (DFF) extension columns.

Common Use Cases and Queries

Typical usage includes determining which items belong to a group for work-order validation, MRO planning, and reporting on consumable/repairable item sets. A common query joins the base table to its translations and groups:

• List all items in a given group and org:
  SELECT item_group_id, inventory_item_id, inventory_org_id FROM ahl.ahl_item_associations_b WHERE item_group_id = :p_group;
• Resolve item name/description via AHL_ITEM_ASSOCIATIONS_TL using ITEM_ASSOCIATION_ID.
• Audit association changes using the history table AHL_ITEM_ASSOCIATIONS_B_H.
• Reporting on priority, quantity, and UOM for MRO planning extracts.
• Security-filtered reads by joining FND_SECURITY_GROUPS on SECURITY_GROUP_ID.

Related Objects

• AHL_ITEM_GROUPS_B – referenced via ITEM_GROUP_ID; the group definition.
• AHL_ITEM_ASSOCIATIONS_TL – translation table joined on ITEM_ASSOCIATION_ID.
• AHL_ITEM_ASSOCIATIONS_B_H – history table joined on ITEM_ASSOCIATION_ID.
• FND_SECURITY_GROUPS – referenced via SECURITY_GROUP_ID.
• MTL_SYSTEM_ITEMS_B – implied source of INVENTORY_ITEM_ID.
• Oracle MRO APIs in the AHL module consume this table when validating item-group membership.