Search Results ahl_mel_cdl_headers_pk

Overview

The table AHL.AHL_MEL_CDL_HEADERS resides in the AHL schema, which supports the Complex Maintenance Repair and Overhaul (CMRO) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores Maintenance Engineering Library (MEL) and Configuration Deviation List (CDL) header information associated with a Product Classification node. In CMRO, MEL and CDL records capture engineering allowances and approved deviations that apply to a specific configuration item within the product classification hierarchy. Each row in this table defines a versioned header that binds a MEL or CDL definition to a PC node, with status, revision, and effective date information governing its lifecycle.

From a Data Vault modeling perspective, the metadata suggests a satellite-leaning classification. The table's grain is defined by a single surrogate key, MEL_CDL_HEADER_ID, and it carries descriptive attributes (status, revision, version, dates, and DFF columns) that change over time, which is characteristic of a satellite. Its dependency on AHL_PC_NODES_B via PC_NODE_ID and its role as a parent to AHL_MEL_CDL_ATA_SEQUENCES position it as a detail-bearing entity within the CMRO configuration model.

Key Information Stored

The surrogate primary key is MEL_CDL_HEADER_ID, enforced by AHL_MEL_CDL_HEADERS_PK. Business-key uniqueness is enforced by AHL_MEL_CDL_HEADERS_U2 over the combination of PC_NODE_ID, MEL_CDL_TYPE_CODE, and VERSION_NUMBER, which guarantees that only one header exists per classification node, type, and version. The most significant columns include:

• MEL_CDL_HEADER_ID — surrogate primary key and the unique identifier referenced by child tables.
• PC_NODE_ID — foreign key to AHL_PC_NODES_B, tying the header to a Product Classification node.
• MEL_CDL_TYPE_CODE — distinguishes MEL records from CDL records.
• VERSION_NUMBER — version of the header, part of the business key.
• STATUS_CODE — lifecycle state (for example draft, active, or obsolete).
• REVISION and REVISION_DATE — revision identifier and the date the revision took effect.
• EXPIRED_DATE — date on which the header ceases to be effective.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS enabling multi-org/security filtering.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the ADF/BC4J framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield (DFF) segments for customer-specific extensions.

The table contains 31 documented columns. The DFF and audit columns are not part of the business key and should not be used to identify a header uniquely.

Common Use Cases and Queries

CMRO users and integrators query this table to retrieve the active MEL or CDL definition for a given classification node, to audit version history, or to join header data to the ATA sequence detail lines. A typical lookup by the searched identifier follows:

• SELECT * FROM AHL.AHL_MEL_CDL_HEADERS WHERE MEL_CDL_HEADER_ID = :p_id;
• SELECT h.* FROM AHL.AHL_MEL_CDL_HEADERS h WHERE h.PC_NODE_ID = :node AND h.MEL_CDL_TYPE_CODE = 'MEL' AND h.STATUS_CODE = 'ACTIVE';
• Join to detail: SELECT h.MEL_CDL_HEADER_ID, s.* FROM AHL.AHL_MEL_CDL_HEADERS h, AHL.AHL_MEL_CDL_ATA_SEQUENCES s WHERE h.MEL_CDL_HEADER_ID = s.MEL_CDL_HEADER_ID AND h.PC_NODE_ID = :node;

Reporting scenarios include listing all versions of a MEL/CDL for a part configuration, identifying headers nearing EXPIRED_DATE, and extracting DFF attribute values for downstream engineering systems. Because the table carries SECURITY_GROUP_ID, queries executed under a specific security profile should filter accordingly.

Related Objects

The following objects are most significant in relation to AHL_MEL_CDL_HEADERS:

• AHL_PC_NODES_B — parent product classification node table; joined on AHL_MEL_CDL_HEADERS.PC_NODE_ID = AHL_PC_NODES_B.PC_NODE_ID.
• AHL_MEL_CDL_ATA_SEQUENCES — child detail table holding ATA sequence lines; joined on MEL_CDL_HEADER_ID.
• FND_SECURITY_GROUPS — security group reference via SECURITY_GROUP_ID.
• AHL_PC_NODES_TL — translation table for classification node names, typically joined through PC_NODE_ID for reporting.
• AHL_MEL_CDL_HEADERS_PK / _U1 / _U2 — primary and unique indexes enforcing key and business-key integrity.

Together these objects form the MEL/CDL configuration structure within the CMRO module, with AHL_MEL_CDL_HEADERS acting as the central versioned header entity.