Search Results ahl_item_compositions




Overview
Explore in AI Chat

Overview

The AHL_ITEM_COMPOSITIONS table is a core data object within the Oracle E-Business Suite (EBS) module for Complex Maintenance, Repair, and Overhaul (AHL). It functions as the header table for defining item compositions, which are structured lists of components that constitute a parent item. This is critical for managing complex assemblies in maintenance, repair, and overhaul (MRO) operations, where a single serviceable unit may be defined by a specific, approved set of constituent parts. The table stores master definitions that are referenced throughout the AHL application to ensure consistency in bills of material for maintenance tasks, material planning, and service documentation.

Key Information Stored

The table's primary purpose is to uniquely identify and manage the status of each item composition definition. Its key columns, as indicated by the provided primary and unique keys, include:

  • ITEM_COMPOSITION_ID: The primary key (PK) surrogate identifier for each composition record.
  • INVENTORY_ITEM_ID: References the inventory item (from INV) that is the parent assembly.
  • INVENTORY_MASTER_ORG_ID: References the master organization to which the parent item belongs.
  • APPROVAL_STATUS_CODE: Indicates the workflow status (e.g., Draft, Approved, Obsolete) of the composition definition, controlling its usability in transactions.

The unique key constraint (UK1) on these three columns ensures that for a given item and organization, there is only one record with a specific approval status, maintaining data integrity.

Common Use Cases and Queries

This table is central to queries that validate or report on the configured structure of serviceable items. A common use case is retrieving all approved compositions for a specific inventory item to validate material requirements for a work order. A typical SQL pattern would join to inventory tables for item details:

SELECT aic.ITEM_COMPOSITION_ID, msib.SEGMENT1 ITEM_CODE, msib.DESCRIPTION, aic.APPROVAL_STATUS_CODE
FROM AHL.AHL_ITEM_COMPOSITIONS aic,
INV.MTL_SYSTEM_ITEMS_B msib
WHERE aic.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND aic.INVENTORY_MASTER_ORG_ID = msib.ORGANIZATION_ID
AND aic.APPROVAL_STATUS_CODE = 'APPROVED'
AND msib.SEGMENT1 = '<ITEM_NUMBER>';

Another critical scenario involves change management, where reports may list all compositions in 'DRAFT' status pending review, or where compositions are queried alongside their detailed component lines from AHL_ITEM_COMP_DETAILS for a complete parts list explosion.

Related Objects

The AHL_ITEM_COMPOSITIONS table sits at the top of a hierarchy of related objects. As documented, it has a direct one-to-many relationship with the detail table, and is referenced by other transactional tables.

  • AHL_ITEM_COMP_DETAILS: The primary child table. It holds the individual component lines for each composition header, joined via AHL_ITEM_COMP_DETAILS.ITEM_COMPOSITION_ID = AHL_ITEM_COMPOSITIONS.ITEM_COMPOSITION_ID.
  • AHL_RT_OPER_MATERIALS: This table references the composition details, establishing a relationship where routine operation materials can be linked to specific components within a defined item composition.

These relationships ensure that the master composition definition governs material usage consistently across the AHL module's planning and execution processes.