Search Results ahl_item_associations_b_h




Overview
Explore in AI Chat

Overview

The AHL_ITEM_ASSOCIATIONS_B_H table resides in the AHL schema, the product schema for Oracle Complex Maintenance Repair and Overhaul (CMRO), an Enterprise Asset Management family application available in Oracle E-Business Suite 12.1.1 and 12.2.2. The table stores the history of item group associations, functioning as an audit and versioning companion to the base table AHL_ITEM_ASSOCIATIONS_B. Every insert, update, or logical deletion performed against the base association record produces a corresponding historical row here, preserving the state of the association at a given point in time.

In Data Vault modeling terms, the mined foreign key structure classifies this object as satellite-leaning. The primary key ITEM_ASSOCIATION_H_ID acts as a hash or surrogate identifier for the historical record, while the relationship to AHL_ITEM_ASSOCIATIONS_B through ITEM_ASSOCIATION_ID anchors the satellite to its parent hub. This classification is a modeling suggestion derived from the FK topology rather than a declared Oracle attribute; practitioners should treat it as a design guideline when building downstream data marts.

Key Information Stored

The documented physical schema contains 38 columns. The most operationally significant include:

Common Use Cases and Queries

Historical tracking of item-group associations supports change audits, compliance reporting, and point-in-time reconstruction of maintenance configurations. A typical query retrieves the full change history for a specific association:

SELECT ITEM_ASSOCIATION_H_ID, ITEM_ASSOCIATION_ID, ITEM_GROUP_ID,
       INVENTORY_ITEM_ID, ACTION, TRANSACTION_DATE, VERSION_NUMBER
FROM   AHL.AHL_ITEM_ASSOCIATIONS_B_H
WHERE  ITEM_ASSOCIATION_ID = :p_association_id
ORDER BY TRANSACTION_DATE DESC;

Analysts frequently join to AHL_ITEM_ASSOCIATIONS_TL_H (via ITEM_ASSOCIATION_H_ID) to retrieve the translated name and description of each historical version, and to FND_SECURITY_GROUPS to apply row-level security filtering. As-of reporting compares the current base record against its most recent history entry to detect drift between planned and actual association attributes.

Related Objects

  • AHL_ITEM_ASSOCIATIONS_B — parent base table; joined on ITEM_ASSOCIATION_ID = ITEM_ASSOCIATIONS_B_H.ITEM_ASSOCIATION_ID.
  • AHL_ITEM_ASSOCIATIONS_TL_H — translated history table; joined on ITEM_ASSOCIATION_H_ID.
  • AHL_ITEM_GROUPS_B — item group master; joined via ITEM_GROUP_ID.
  • FND_SECURITY_GROUPS — security group definition; joined via SECURITY_GROUP_ID.
  • AHL_ITEM_ASSOCIATIONS_TL — translated base table associated with the live parent record.
  • CMRO item association APIs and concurrent programs that populate the base table typically trigger the corresponding history inserts into this object.