Results for “item_comp_detail_id”

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

Overview

The view APPS.AHL_ITEM_COMP_DETAIL_V belongs to the Oracle E-Business Suite product group AHL — Complex Maintenance Repair and Overhaul (CMRO). It exposes item composition details together with the descriptive attributes of the associated item groups, units of measure, and inventory items. The AHL schema models complex assemblies—most notably repairable and rotable components—through composition relationships defined in AHL_ITEM_COMPOSITIONS and their line-level detail rows in AHL_ITEM_COMP_DETAILS. Those detail rows store numeric keys (inventory item identifiers, item group identifiers, organization identifiers, and UOM codes) but not the human-readable labels required for reporting. The view resolves those keys by joining to inventory item and unit-of-measure sources and to the item group value set, producing a denormalized, presentation-ready result set.

Because it is a view and not a table, AHL_ITEM_COMP_DETAIL_V carries no independent storage and no data integrity of its own; it reflects the current state of the underlying base objects at query time. It is defined in the APPS schema and is therefore available to any reporting or integration process that has been granted SELECT on it. It is typically consumed by CMRO setup and composition inquiry forms, by custom reports, and by interfaces that must translate composition records into item names, group names, and UOM meanings without performing the lookups themselves.

Underlying Base Objects

The documented definition of the view references the following base objects:

• AHL_ITEM_COMP_DETAILS (synonym) — the driving table, supplying composition detail identifiers, item group, inventory item, master organization, UOM code, quantity, effective end date, link detail, audit columns, and the fifteen descriptive flexfield attribute columns.
• AHL_ITEM_GROUPS_VL (view) — the translated item group value set, supplying the item group name.
• AHL_MTL_ITEMS_NON_OU_V (view) — an organization-independent inventory item source providing concatenated segments (the item name), description, organization name, and organization code.
• MTL_UNITS_OF_MEASURE_VL (view) — the translated unit-of-measure definition supplying the UOM meaning.
• HR_GENERAL and HR_SECURITY (packages) — invoked indirectly through the AHL organization and item security views to enforce operating unit and organization access rules.

All of the joins to item group, item, and UOM sources are outer joins, so a detail row is returned even when the group, item, or UOM lookup fails to resolve. The selection uses DISTINCT, which suppresses duplicate rows that can arise from the outer joins. The item join is the only case where a NULL inventory item identifier is deliberately handled: the composition detail is treated as group-only, and the group description is substituted for the item description where no group is present.

Key Columns

• ROW_ID — the ROWID of the underlying AHL_ITEM_COMP_DETAILS row; useful for direct-row operations and troubleshooting duplicates.
• ITEM_COMP_DETAIL_ID / ITEM_COMPOSITION_ID / LINK_COMP_DETL_ID — primary key of the detail, its parent composition, and the linked detail used to nest composition levels.
• ITEM_GROUP_ID / ITEM_GROUP_NAME — the item group key and its translated name from AHL_ITEM_GROUPS_VL.
• INVENTORY_ITEM_ID / ITEM_NAME — the item key and its concatenated inventory segment string.
• ITEM_DESCRIPTION — a DECODE that returns the item description when no item group is assigned, otherwise the item group description.
• INVENTORY_MASTER_ORG_ID / ORGANIZATION_NAME / ORGANIZATION_CODE — the master organization owning the item.
• UOM_CODE / UOM_MEANING — the unit of measure and its translated meaning.
• QUANTITY / EFFECTIVE_END_DATE — the composition quantity and the date after which the detail ceases to be effective.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, and SECURITY_GROUP_ID.
• ATTRIBUTE1 through ATTRIBUTE15, ATTRIBUTE_CATEGORY — the descriptive flexfield context and segment values associated with the composition detail.

Common Use Cases and Queries

Typical scenarios include composition explosion reports, as-maintained BOM inquiries, UOM conversion validation, and interfaces that create or validate composition details using names rather than identifiers. A common pattern filters by effective end date so only currently effective details are listed:

• SELECT ITEM_COMPOSITION_ID, ITEM_NAME, ITEM_GROUP_NAME, ITEM_DESCRIPTION, ORGANIZATION_CODE, UOM_MEANING, QUANTITY FROM APPS.AHL_ITEM_COMP_DETAIL_V WHERE EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE ORDER BY ITEM_COMPOSITION_ID;
• SELECT ITEM_COMPOSITION_ID, ITEM_GROUP_NAME, COUNT(*) FROM APPS.AHL_ITEM_COMP_DETAIL_V WHERE ITEM_GROUP_ID IS NOT NULL GROUP BY ITEM_COMPOSITION_ID, ITEM_GROUP_NAME;
• SELECT ITEM_COMPOSITION_ID, ITEM_NAME, QUANTITY, UOM_MEANING FROM APPS.AHL_ITEM_COMP_DETAIL_V WHERE INVENTORY_ITEM_ID = :p_item_id AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);

Because the item, group, and UOM lookups are outer joins, queries should not assume a non-null ITEM_NAME, ITEM_GROUP_NAME, or UOM_MEANING; result sets should be tested with NVL or filtered explicitly for reporting. Restrictions imposed by HR_SECURITY through the AHL item and organization views also limit visibility to organizations within the caller's security profile.