Results for “serial_number_to”

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

Overview

AHL_MR_EFFECTIVITY_DTLS is a table in the AHL schema, owned by the Complex Maintenance Repair and Overhaul (AHL) module of Oracle E-Business Suite. It stores the item-instance level details of a maintenance requirement, providing the granular effectivity criteria that determine precisely which serialized items, manufacturers, or territories a given maintenance requirement applies to. Whereas its parent table AHL_MR_EFFECTIVITIES captures the header-level definition of a maintenance requirement effectivity, this detail table narrows the scope to individual item attributes such as serial number ranges, manufacture date ranges, manufacturer, and country of origin. The object is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 and is exposed through the standard ETRM repository with a documented physical schema of 32 columns.

From a Data Vault modeling perspective, this object is heuristically classified as a link. This classification is derived from its FK structure: the table resolves relationships between a maintenance requirement effectivity (AHL_MR_EFFECTIVITIES), a manufacturer (MTL_MANUFACTURERS), and a territory (FND_TERRITORIES), rather than acting as a standalone hub of business keys or a pure descriptive satellite. This is offered as a modeling suggestion only; in native EBS terms the table functions as a child detail entity of the maintenance requirement effectivity.

Key Information Stored

The most significant columns are the identifiers and effectivity criteria that define each row.

• MR_EFFECTIVITY_DETAIL_ID — the surrogate primary key, enforced by AHL_MR_EFFECTIVITY_DTLS_PK and also the sole documented unique-key candidate (AHL_MR_EFFECTIVITY_DTLS_U1). It uniquely identifies each detail line.
• MR_EFFECTIVITY_ID — the foreign key back to AHL_MR_EFFECTIVITIES, linking each detail line to its parent effectivity header.
• MANUFACTURER_ID — FK to MTL_MANUFACTURERS, restricting the effectivity to a specific manufacturer's items.
• COUNTRY_CODE — FK to FND_TERRITORIES, restricting the effectivity by country of origin.
• SERIAL_NUMBER_FROM / SERIAL_NUMBER_TO — the serial number range that the maintenance requirement applies to, the core item-instance selector.
• MANUFACTURE_DATE_FROM / MANUFACTURE_DATE_TO — the manufacture date range qualifying applicable item instances.
• EXCLUDE_FLAG — indicates whether the row represents an inclusion or exclusion criterion.
• SECURITY_GROUP_ID — the Multi-Org / security group discriminator (FK to FND_SECURITY_GROUPS) governing row-level access.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the AHL forms and OAF pages.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns for customer-extensible data.

Common Use Cases and Queries

The table is queried when resolving which serialized item instances are governed by a maintenance requirement, for example when generating maintenance work orders or verifying effectivity during repair planning. A typical pattern joins the detail to its parent header and to the manufacturer and territory lookups:

• Resolve all detail lines for a given effectivity: SELECT * FROM AHL.AHL_MR_EFFECTIVITY_DTLS WHERE MR_EFFECTIVITY_ID = :p_id;
• Find effectivities applicable to a serial number by range comparison: WHERE :serial BETWEEN SERIAL_NUMBER_FROM AND SERIAL_NUMBER_TO combined with manufacture-date range predicates.
• Reporting joins: ... FROM AHL_MR_EFFECTIVITY_DTLS d, AHL_MR_EFFECTIVITIES e, MTL_MANUFACTURERS m, FND_TERRITORIES t WHERE d.MR_EFFECTIVITY_ID = e.MR_EFFECTIVITY_ID AND d.MANUFACTURER_ID = m.MANUFACTURER_ID(+) AND d.COUNTRY_CODE = t.TERRITORY_CODE(+).
• Filtering by EXCLUDE_FLAG to separate qualifying versus excluded instances.

Related Objects

The following objects are the most significant dependencies, grounded in the documented FK relationships.

• AHL_MR_EFFECTIVITIES — parent header; joined on MR_EFFECTIVITY_ID.
• MTL_MANUFACTURERS — manufacturer lookup; joined on MANUFACTURER_ID.
• FND_TERRITORIES — territory/country lookup; joined on COUNTRY_CODE.
• FND_SECURITY_GROUPS — security group reference; joined on SECURITY_GROUP_ID.
• AHL_MR_EFFECTIVITY_DTLS_PK / _U1 — primary and unique indexes on MR_EFFECTIVITY_DETAIL_ID.
• MTL_SYSTEM_ITEMS — indirectly referenced via the parent when resolving the maintained item.