Results for “ahl_ue_relationships”

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

Overview

AHL_UE_RELATIONSHIPS is a table in the AHL schema belonging to the Complex Maintenance Repair and Overhaul (CMRO) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2 releases. It stores the relationships that exist between unit effectivities — the records held in AHL_UNIT_EFFECTIVITIES_B that define the effective applicability of maintenance programs, tasks, and requirements against specific units of equipment.

Unit effectivities describe whether a given maintenance definition applies to a particular unit. In complex MRO scenarios, effectivities are rarely isolated: one effectivity may supersede, replace, depend upon, or otherwise be associated with another. This table captures those associations by relating one unit effectivity (UE_ID) to another (RELATED_UE_ID) and labelling the nature of the association through RELATIONSHIP_CODE. Because its sole purpose is to record associations between two entity instances, the object is classified heuristically as a link table in Data Vault modeling terms. In a Data Vault design, the unit effectivity would form the hub, and AHL_UE_RELATIONSHIPS would be the link connecting effectivity instances to one another, with any descriptive attributes handled separately in satellites.

Key Information Stored

The table carries a documented physical schema of 28 columns. The most significant are:

• UE_RELATIONSHIP_ID — the surrogate primary key, enforced by AHL_UE_RELATIONSHIPS_PK and also carried in unique index AHL_UE_RELATIONSHIPS_U1. This is the internal identifier referenced by any dependent objects.
• UE_ID — foreign key to AHL_UNIT_EFFECTIVITIES_B; the unit effectivity that owns the relationship.
• RELATED_UE_ID — foreign key to AHL_UNIT_EFFECTIVITIES_B; the counterpart unit effectivity participating in the relationship.
• RELATIONSHIP_CODE — the business code describing the semantic of the association between the two effectivities.
• ORIGINATOR_UE_ID — identifies the originating unit effectivity, useful for lineage and audit of relationship creation.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org / data-security filtering.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/ADF framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard Who columns for audit and concurrency.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS flexfield-style descriptive columns available for extensions.

The business-key candidate is documented under unique index AHL_UE_RELATIONSHIPS_U2, defined on the combination of RELATED_UE_ID, UE_ID, and RELATIONSHIP_CODE. This guarantees that a given effectivity pair may hold at most one instance of a given relationship type, while still allowing multiple distinct relationship types between the same two effectivities.

Common Use Cases and Queries

Typical uses include lineage reporting for maintenance requirements, identifying effectivity chains when a configuration changes, and validating referential consistency between effectivity pairs.

To retrieve all relationships for a given unit effectivity:

• SELECT r.ue_relationship_id, r.ue_id, r.related_ue_id, r.relationship_code FROM ahl_ue_relationships r WHERE r.ue_id = :p_ue_id;

To resolve both sides of the relationship against the unit effectivity master:

• SELECT r.relationship_code, ue1.effectivity_name, ue2.effectivity_name FROM ahl_ue_relationships r, ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2 WHERE r.ue_id = ue1.ue_id AND r.related_ue_id = ue2.ue_id;

To audit creation of relationships within a security group or by a specific user, filter on SECURITY_GROUP_ID, CREATED_BY, or CREATION_DATE. Reporting extracts commonly join this table to the unit effectivity and to the associated maintenance program tables for MRO planning analytics.

Related Objects

The following objects are the most significant in relation to AHL_UE_RELATIONSHIPS:

• AHL_UNIT_EFFECTIVITIES_B — referenced twice through UE_ID and RELATED_UE_ID; the primary parent entity for both sides of every relationship.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID, enforcing data security partitioning.
• AHL_UE_RELATIONSHIPS_PK / AHL_UE_RELATIONSHIPS_U1 / AHL_UE_RELATIONSHIPS_U2 — the indexes that enforce uniqueness of the surrogate key and the business key combination of effectivity pair plus relationship code.
• AHL_UNIT_EFFECTIVITIES_TL — the translation table for unit effectivity names and descriptions, used in joined reporting.
• The AHL CMRO effectivity maintenance UI and its associated public APIs — which insert, update, and delete rows in this table when users maintain effectivity relationships.