Search Results ahl_mr_effectivity_ext_dtls_pk




Overview
Explore in AI Chat

Overview

AHL_MR_EFFECTIVITY_EXT_DTLS is a table within the AHL schema, the database owner for Oracle Complex Maintenance Repair and Overhaul (CMRO), a module of Oracle E-Business Suite available in releases 12.1.1 and 12.2.2. As its name implies, the table stores extended effectivity details associated with maintenance requirements (MR) effectivity records. Maintenance requirements in AHL define what maintenance work must be performed, and effectivity records define when, where, and under what conditions that work applies. The AHL_MR_EFFECTIVITY_EXT_DTLS table supplements the base effectivity definition by holding conditional or extended attributes — such as owner, location type, and configuration item (CSI) extended attribute filters — that further qualify a maintenance requirement's applicability.

From a data modeling perspective, the metadata's heuristic Data Vault classification describes this table as satellite-leaning. This classification is a modeling suggestion rather than a physical constraint: the table behaves like a satellite because it carries descriptive, contextual detail keyed to a parent entity (AHL_MR_EFFECTIVITIES) rather than representing an independent business entity or a many-to-many associative link. Its structure — a surrogate primary key plus foreign keys and timestamp/audit columns — is characteristic of a detail table that hangs off a master effectivity record.

Key Information Stored

The table is documented with 31 columns. The most significant include:

  • MR_EFFECTIVITY_EXT_DTL_ID — the surrogate primary key, enforced by AHL_MR_EFFECTIVITY_EXT_DTLS_PK and also covered by unique index AHL_MR_EFFECTIVITY_EXT_DTLS_U1. This column uniquely identifies each extended detail row.
  • MR_EFFECTIVITY_ID — foreign key to AHL_MR_EFFECTIVITIES, tying each extended detail row to its parent effectivity record. This is the principal join column.
  • EFFECT_EXT_DTL_REC_TYPE — a record-type discriminator, indicating the kind of extended effectivity detail being stored and allowing the table to accommodate multiple detail categories.
  • EXCLUDE_FLAG — flags whether the associated condition or entity is included or excluded from the effectivity scope.
  • OWNER_ID — identifies the owning party or organization to which the extended effectivity applies.
  • LOCATION_TYPE_CODE — qualifies the effectivity by location type, restricting applicability to particular physical or organizational locations.
  • CSI_EXT_ATTRIBUTE_CODE and CSI_EXT_ATTRIBUTE_VALUE — a code/value pair used to filter effectivity against configuration item extended attributes. This supports configuration-driven maintenance applicability, a common requirement in complex MRO environments.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-organization data security and access partitioning.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the AHL forms and APIs to detect concurrent updates.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns capturing who created and last modified each row and when.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns, enabling customers to extend the table with site-specific data without schema changes.

The distinction between the surrogate key (MR_EFFECTIVITY_EXT_DTL_ID) and the parent reference (MR_EFFECTIVITY_ID) is important: the former is a system-generated identifier, while the latter carries the true business relationship to the effectivity master.

Common Use Cases and Queries

The most frequent access pattern retrieves all extended details for a given effectivity record. For example:

  • SELECT * FROM AHL.AHL_MR_EFFECTIVITY_EXT_DTLS d WHERE d.mr_effectivity_id = :effectivity_id;
  • Joining to the master to see full context: SELECT d.*, e.* FROM AHL.AHL_MR_EFFECTIVITIES e, AHL.AHL_MR_EFFECTIVITY_EXT_DTLS d WHERE e.mr_effectivity_id = d.mr_effectivity_id;
  • Reporting on effectivity filtered by configuration: filtering on csi_ext_attribute_code and csi_ext_attribute_value to determine which maintenance requirements apply to a specific configuration item.
  • Reporting by location: aggregating rows by location_type_code or owner_id to analyze where maintenance requirements apply.
  • Security-scoped extracts restricted through security_group_id for multi-organization deployments.

Because the table is detail-oriented and often rendered through the AHL maintenance requirement forms, direct DML is rarely advisable; changes normally flow through the CMRO application or its published APIs.

Related Objects

  • AHL_MR_EFFECTIVITIES — the master effectivity table; the foreign key AHL_MR_EFFECTIVITY_EXT_DTLS.MR_EFFECTIVITY_ID references it. This is the primary and most significant relationship.
  • FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID, providing the security partitioning context for each row.
  • FND_ATTACHED_DOCUMENTS / DFF views — the ATTRIBUTE columns are surfaced through flexfield definition and value-set objects in the FND schema.
  • AHL_MR_HEADERS and AHL_MR_OPERATIONS — related maintenance requirement tables typically traversed together with effectivity data when building a complete MR picture.
  • AHL maintenance requirement APIs and concurrent programs — the supported interface for creating and maintaining effectivity and extended detail records rather than direct SQL.