Results for “ahl_item_compositions”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AHL_ITEM_COMPOSITIONS is a table owned by the AHL schema within Oracle E-Business Suite, belonging to the Complex Maintenance Repair and Overhaul (AHL) product family. In the ETRM 12.2.2 data model, the object carries a VALID status and comprises 31 documented physical columns. It functions as the header table for item compositions, capturing the top-level definition of how a maintainable inventory item is assembled or structured for MRO purposes. Within the broader AHL architecture, this header record anchors the detailed material relationships stored in dependent tables, notably AHL_RT_OPER_MATERIALS, which references the header through a foreign key.

From a data-modeling perspective, the metadata heuristic classifies AHL_ITEM_COMPOSITIONS as hub-leaning. This suggests treating it, in Data Vault terms, as a hub entity: a stable, business-keyed anchor representing a distinct composition instance, with descriptive and status attributes potentially modeled as satellite content. This classification derives from the table's role in owning a primary key while being referenced by downstream transactional tables rather than serving purely as a junction or attribute store.

Key Information Stored

The table's surrogate primary key is ITEM_COMPOSITION_ID, enforced by the AHL_ITEM_COMPOSITIONS_PK constraint. Business-key uniqueness is documented through two alternate unique indexes: AHL_ITEM_COMPOSITIONS_U1 on ITEM_COMPOSITION_ID and AHL_ITEM_COMPOSITIONS_U2 on the composite of INVENTORY_ITEM_ID, APPROVAL_STATUS_CODE, and INVENTORY_MASTER_ORG_ID. The most significant columns include:

• ITEM_COMPOSITION_ID — surrogate identifier and primary key for each composition header.
• INVENTORY_ITEM_ID — the inventory item whose composition is being defined; part of the business key.
• INVENTORY_MASTER_ORG_ID — the master organization context, scoping the item definition; part of the business key.
• APPROVAL_STATUS_CODE — lifecycle/approval state of the composition; part of the business key.
• EFFECTIVE_END_DATE — date through which the composition remains effective.
• LINK_COMP_ID — reference linking this composition to a related composition record.
• DRAFT_FLAG — indicates whether the composition is in draft state.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enabling data security partitioning.
• SOURCED_FROM_ENIGMA_FLAG — indicates legacy migration origin.
• OBJECT_VERSION_NUMBER — optimistic locking control.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard DFF/descriptive flexfield extension columns.

Common Use Cases and Queries

Typical usage centers on retrieving compositions for a given item and organization, checking approval status, and resolving linked composition details through AHL_RT_OPER_MATERIALS. A common query resolves the business key:

SELECT ITEM_COMPOSITION_ID, INVENTORY_ITEM_ID, INVENTORY_MASTER_ORG_ID, APPROVAL_STATUS_CODE, EFFECTIVE_END_DATE FROM AHL.AHL_ITEM_COMPOSITIONS WHERE INVENTORY_ITEM_ID = :item AND INVENTORY_MASTER_ORG_ID = :org AND APPROVAL_STATUS_CODE = 'APPROVED';

Reporting scenarios include listing active, non-draft compositions, auditing approval-state transitions, and joining to material details to produce a multi-level bill of MRO materials. Security-aware reporting should join SECURITY_GROUP_ID to FND_SECURITY_GROUPS.

Related Objects

• AHL_RT_OPER_MATERIALS — references AHL_ITEM_COMPOSITIONS via ITEM_COMP_DETAIL_ID, holding component-line detail.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for row-level security.
• AHL_ITEM_COMPOSITIONS_PK / _U1 / _U2 — primary and unique constraints enforcing identity and business keys.
• INV item master tables — supply INVENTORY_ITEM_ID and INVENTORY_MASTER_ORG_ID semantics.