Search Results ahl_item_groups_v




Overview
Explore in AI Chat

Overview

AHL_ITEM_GROUPS_V is an Oracle E-Business Suite (EBS) view owned by the APPS schema and delivered as part of the AHL – Complex Maintenance Repair and Overhaul (CMRO) product family. Its documented purpose is to join item group lookup meanings and codes, presenting a denormalized, reporting-friendly representation of item group definitions. Rather than exposing raw coded values, the view resolves the internal STATUS_CODE and TYPE_CODE columns into their user-facing STATUS_MEANING and TYPE_MEANING descriptions by way of FND_LOOKUP_VALUES_VL. This makes it suitable for reporting, integration, and OAF-based pages where a human-readable description is required alongside the underlying code. The view is documented as VALID in ETRM for both EBS 12.1.1 and 12.2.2, and is commonly surfaced in queries that search for ahl_item_groups_v.

Underlying Base Objects

The view is defined over two documented base objects:

  • AHL_ITEM_GROUPS_VL (VIEW) – the multilingual base view holding the item group records, including ITEM_GROUP_ID, NAME, DESCRIPTION, audit columns, and the DFF attribute columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15). The _VL suffix indicates it is the translated view that returns the language-appropriate NAME and DESCRIPTION.
  • FND_LOOKUP_VALUES_VL (VIEW) – the standard EBS lookup values view, referenced twice with aliases FND_STAT and FND_TYPE.

The joins are outer joins ((+) syntax): FND_STAT.LOOKUP_CODE = AHL_ITEM_GROUPS_VL.STATUS_CODE restricted to LOOKUP_TYPE = 'AHL_ITEMGROUP_STATUS', and FND_TYPE.LOOKUP_CODE = AHL_ITEM_GROUPS_VL.TYPE_CODE restricted to LOOKUP_TYPE = 'AHL_ITEMGROUP_TYPE'. Because they are outer joins, an item group is still returned even where no matching lookup value exists, in which case the corresponding meaning is NULL.

Key Columns

  • ROW_ID – unique identifier for the item group row.
  • ITEM_GROUP_ID – primary business key for the item group.
  • OBJECT_VERSION_NUMBER – optimistic locking column used by the AHL framework.
  • NAME / DESCRIPTION – language-appropriate name and description sourced from AHL_ITEM_GROUPS_VL.
  • STATUS_CODE / STATUS_MEANING – coded and decoded status of the item group, decoded via lookup type AHL_ITEMGROUP_STATUS.
  • TYPE_CODE / TYPE_MEANING – coded and decoded type of the item group, decoded via lookup type AHL_ITEMGROUP_TYPE.
  • SOURCE_ITEM_GROUP_ID – reference to the originating item group, useful for copied or templated definitions.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – standard audit columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – descriptive flexfield (DFF) columns for customer-defined attributes.

Common Use Cases and Queries

The view is typically used to list or validate item groups with resolved status and type descriptions, for example in CMRO configuration reports or integration extracts.

  • List all active item groups with readable status and type: 
    SELECT item_group_id, name, status_code, status_meaning,
       type_code, type_meaning
FROM   apps.ahl_item_groups_v
WHERE  status_code = 'ACTIVE';
  • Find item groups of a specific type by its meaning: 
    SELECT item_group_id, name, type_meaning
FROM   apps.ahl_item_groups_v
WHERE  type_meaning = 'Standard';
  • Trace copied/templated item groups via the source reference: 
    SELECT item_group_id, name, source_item_group_id
FROM   apps.ahl_item_groups_v
WHERE  source_item_group_id IS NOT NULL;

Because the view relies on translated and lookup objects, queries should be run within an environment whose current session language resolves the desired NAME, DESCRIPTION, and lookup meanings. In all cases the view is read-only and intended for query and reporting purposes.