Results for “ahl_item_groups_b”

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

Overview

AHL_ITEM_GROUPS_B is a base table in the AHL (Complex Maintenance Repair and Overhaul) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of item groups, which are named collections of items used within the AHL application to support maintenance, repair, and overhaul planning. As stated in the object description, items are grouped so that each group can be reused across positions multiple times, providing a reusable building block for complex assemblies and maintenance tasks rather than requiring individual item selection in every context.

The table follows the standard Oracle EBS "_B" (base) naming convention, indicating that it is the language-independent base entity, with translatable descriptive attributes carried in a companion "_TL" table and audit history maintained in an "_H" table. In Data Vault modeling terms, the FK/PK structure identifies this object as a hub: ITEM_GROUP_ID is a stable, non-descriptive surrogate key referenced by many surrounding entities, while descriptive attributes such as NAME and STATUS_CODE are candidates for a satellite. This classification is a heuristic modeling suggestion, not a physical constraint imposed by EBS.

Key Information Stored

The table contains 29 documented columns. The primary surrogate key is ITEM_GROUP_ID, enforced by the AHL_ITEM_GROUPS_B_PK constraint. Two unique indexes identify business-key candidates: AHL_ITEM_GROUPS_B_U1 on ITEM_GROUP_ID and AHL_ITEM_GROUPS_B_U2 on NAME and STATUS_CODE, confirming that group names are unique within a given status.

• ITEM_GROUP_ID — the surrogate primary key that uniquely identifies each item group and is the foreign key target for all dependent tables.
• NAME — the business identifier of the group; participates in the AHL_ITEM_GROUPS_B_UK1 unique constraint with STATUS_CODE.
• STATUS_CODE — the lifecycle or validity state of the group, used together with NAME for uniqueness.
• TYPE_CODE — classifies the kind of item group, distinguishing groupings by purpose or behavior.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and controls the security domain that governs visibility of the group's data.
• SOURCE_ITEM_GROUP_ID — self-referencing identifier linking a group to a source group from which it was derived or copied.
• SOURCED_FROM_ENIGMA_FLAG — an indicator recording whether the group originated from the legacy Enigma data migration.
• OBJECT_VERSION_NUMBER — an optimistic locking column used by the AHL API layer to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Oracle EBS who-columns capturing audit and ownership information.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — a flexible descriptive flexfield (DFF) block for customer-specific extensions without schema modification.

Common Use Cases and Queries

Typical usage centers on retrieving and validating group definitions and on reporting which groups are in active use.

Listing active item groups by name and status:

• SELECT item_group_id, name, status_code, type_code FROM ahl_item_groups_b WHERE status_code = 'ACTIVE' ORDER BY name;

Finding a group by its business key:

• SELECT item_group_id FROM ahl_item_groups_b WHERE name = :name AND status_code = :status;

Resolving membership and usage through related tables:

• SELECT ig.name, icd.item_id FROM ahl_item_groups_b ig JOIN ahl_item_comp_details icd ON icd.item_group_id = ig.item_group_id WHERE ig.item_group_id = :id;

Reporting on groups created from the legacy source system or derived from other groups:

• SELECT name, sourced_from_enigma_flag, source_item_group_id FROM ahl_item_groups_b WHERE sourced_from_enigma_flag = 'Y';

Because NAME and STATUS_CODE form the unique business key, integrations should always qualify lookups by status to avoid ambiguous matches.

Related Objects

The FK metadata shows AHL_ITEM_GROUPS_B as a hub referenced by numerous dependent tables, all joining through ITEM_GROUP_ID.

• AHL_ITEM_GROUPS_TL — the translatable companion holding language-specific descriptive text; join on ITEM_GROUP_ID.
• AHL_ITEM_GROUPS_B_H — the audit history table capturing prior versions of base rows; join on ITEM_GROUP_ID.
• AHL_ITEM_COMP_DETAILS — component details that reference a group's items via ITEM_GROUP_ID.
• AHL_ITEM_ASSOCIATIONS_B — associations linking groups to other entities via ITEM_GROUP_ID.
• AHL_MC_RELATIONSHIPS — maintenance relationship definitions that reference groups via ITEM_GROUP_ID.
• AHL_RELATIONSHIPS_B — relationship definitions using ITEM_GROUP_ID as a structural reference.
• AHL_RT_OPER_MATERIALS — routing operation materials that consume group definitions via ITEM_GROUP_ID.
• AHL_SCHEDULE_MATERIALS — scheduling materials that reference groups via ITEM_GROUP_ID.
• FND_SECURITY_GROUPS — the referenced parent of SECURITY_GROUP_ID, governing data security on this table.

Together these relationships confirm that item groups function as a reusable, centralized definition consumed across maintenance, routing, and scheduling functions in the AHL module.