Search Results ahl_mc_relationships




Overview
Explore in AI Chat

Overview

The AHL_MC_RELATIONSHIPS table is a core data structure within the Oracle E-Business Suite (EBS) module for Complex Maintenance, Repair, and Overhaul (AHL). It serves as the primary repository for Master Configuration (MC) node information, representing the hierarchical relationships between components and assemblies within a configured asset or product structure. This table is the result of a denormalization process combining data from the base table AHL_RELATIONSHIP_B and its corresponding translation table (TL), creating a consolidated and performance-optimized entity for storing and accessing configuration node data in EBS releases 12.1.1 and 12.2.2.

Key Information Stored

The table's primary key is the RELATIONSHIP_ID, which uniquely identifies each node or relationship in the configuration hierarchy. Another critical unique key (UK1) combines MC_HEADER_ID and POSITION_KEY, enforcing the structural uniqueness of nodes within a specific master configuration. Essential columns include MC_HEADER_ID, which links to the parent master configuration definition; PARENT_RELATIONSHIP_ID, which establishes the hierarchical parent-child links between nodes (implemented via a self-referential foreign key); and POSITION_KEY, which defines the node's sequence or position within the structure. The ITEM_GROUP_ID column links the node to a specific item group definition, connecting the configuration logic to inventory items.

Common Use Cases and Queries

This table is central to queries that traverse or analyze the bill-of-material-like structure of a configured asset. Common use cases include generating a complete hierarchical tree of components for a maintenance work order, validating configuration rules during service requests, and reporting on the usage of specific item groups across different asset configurations. A typical query pattern involves hierarchical SQL (CONNECT BY or recursive WITH clause) starting from a root MC_HEADER_ID to retrieve all child nodes.

  • Sample Query (Hierarchical Fetch): SELECT relationship_id, parent_relationship_id, position_key FROM ahl_mc_relationships WHERE mc_header_id = :p_mc_header_id START WITH parent_relationship_id IS NULL CONNECT BY PRIOR relationship_id = parent_relationship_id ORDER SIBLINGS BY position_key;
  • Reporting Use Case: Joining this table with AHL_ITEM_GROUPS_B and INV_ITEMS to list all inventory items and their positions within a specific master configuration for a parts list report.

Related Objects

The AHL_MC_RELATIONSHIPS table has integral relationships with several other key AHL objects. It is linked to AHL_MC_HEADERS_B via the MC_HEADER_ID foreign key, which anchors the node hierarchy to its parent configuration. The self-referential foreign key on PARENT_RELATIONSHIP_ID maintains the internal hierarchy. The ITEM_GROUP_ID column references AHL_ITEM_GROUPS_B, tying configuration nodes to definable groups of inventory items. Furthermore, the AHL_MC_CONFIG_RELATIONS table holds a foreign key to this table's RELATIONSHIP_ID, indicating it is used to store additional configuration-specific relationship data or validations.