Search Results ahl_mel_cdl_relationships_u2

Overview

The AHL.AHL_MEL_CDL_RELATIONSHIPS table is a valid, physical table in the AHL (Complex Maintenance, Repair and Overhaul / airline maintenance) schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores relationships between a pair of ATA (Air Transport Association) sequences that exist within the same MEL/CDL (Minimum Equipment List / Configuration Deviation List) definition. In operational terms, it captures the dependency logic that links one ATA sequence to another, allowing the MEL/CDL engine to express that a given sequence is associated with, or conditioned on, a related sequence in the same document.

From a heuristic Data Vault perspective, this object is best modeled as a link table. It does not hold descriptive state of its own in the way a satellite would; instead it records an association between two entity instances — the two ATA sequences — together with the audit and flexfield scaffolding common to EBS transactional tables. The surrogate primary key, MEL_CDL_RELATIONSHIP_ID, uniquely identifies each relationship instance.

Key Information Stored

The most significant columns and their roles are:

• MEL_CDL_RELATIONSHIP_ID — the surrogate primary key (AHL_MEL_CDL_RELATIONSHIPS_PK); also the sole column of unique index AHL_MEL_CDL_RELATIONSHIPS_U1.
• ATA_SEQUENCE_ID — the ATA sequence at one end of the relationship; foreign key to AHL_MEL_CDL_ATA_SEQUENCES.
• RELATED_ATA_SEQUENCE_ID — the ATA sequence at the other end of the relationship; foreign key to AHL_MEL_CDL_ATA_SEQUENCES.
• OBJECT_VERSION_NUMBER — the locking sequence number used for optimistic concurrency control.
• SECURITY_GROUP_ID — supports application hosting and data segregation; foreign key to FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS Who columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield storage columns.

The business-key candidate is composite: unique index AHL_MEL_CDL_RELATIONSHIPS_U2 spans RELATED_ATA_SEQUENCE_ID and ATA_SEQUENCE_ID, enforcing that a given ordered pair of sequences is recorded only once. The nonunique index AHL_MEL_CDL_RELATIONSHIPS_N1 on ATA_SEQUENCE_ID supports traversal from one sequence to its related sequences. The table resides in the APPS_TS_TX_DATA tablespace, with its indexes in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical usage centers on resolving which ATA sequences are related within a MEL/CDL document, and on reporting these linkages for engineering and compliance review.

• Finding all relationships for a given sequence:

  SELECT MEL_CDL_RELATIONSHIP_ID, ATA_SEQUENCE_ID, RELATED_ATA_SEQUENCE_ID
  FROM   AHL.AHL_MEL_CDL_RELATIONSHIPS
  WHERE  ATA_SEQUENCE_ID = :sequence_id;

• Detecting duplicate or conflicting pairings using the composite business key:

  SELECT RELATED_ATA_SEQUENCE_ID, ATA_SEQUENCE_ID, COUNT(*)
  FROM   AHL.AHL_MEL_CDL_RELATIONSHIPS
  GROUP  BY RELATED_ATA_SEQUENCE_ID, ATA_SEQUENCE_ID
  HAVING COUNT(*) > 1;

• Joining to both ends of the relationship to describe the linked sequences:

  SELECT r.MEL_CDL_RELATIONSHIP_ID, a1.SEQUENCE_NUMBER, a2.SEQUENCE_NUMBER
  FROM   AHL.AHL_MEL_CDL_RELATIONSHIPS r,
         AHL.AHL_MEL_CDL_ATA_SEQUENCES a1,
         AHL.AHL_MEL_CDL_ATA_SEQUENCES a2
  WHERE  r.ATA_SEQUENCE_ID = a1.ATA_SEQUENCE_ID
  AND    r.RELATED_ATA_SEQUENCE_ID = a2.ATA_SEQUENCE_ID;

Reporting scenarios include MEL/CDL dependency matrices, impact analysis when a sequence changes, and data-quality audits against the unique indexes.

Related Objects

• AHL.AHL_MEL_CDL_ATA_SEQUENCES — referenced twice, via ATA_SEQUENCE_ID and RELATED_ATA_SEQUENCE_ID; the principal parent of this link.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for hosting/security partitioning.
• AHL_MEL_CDL_RELATIONSHIPS_PK, AHL_MEL_CDL_RELATIONSHIPS_U1, AHL_MEL_CDL_RELATIONSHIPS_U2, AHL_MEL_CDL_RELATIONSHIPS_N1 — the primary key, unique, and nonunique indexes that govern access.
• MEL/CDL header and document tables in the AHL schema that frame the sequences participating in these relationships.

Because the table is classified as a link, integrity depends on its parent sequence records existing in AHL_MEL_CDL_ATA_SEQUENCES; deletions or merges at the sequence level should be evaluated against both foreign keys before maintenance.