Results for “ahl_mr_effectivities”

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

Overview

The AHL_MR_EFFECTIVITIES table, owned by the AHL schema, is a core data object within the Oracle E-Business Suite Complex Maintenance Repair and Overhaul (CMRO) module. In Oracle EBS 12.1.1 and 12.2.2, this table stores the effectivity records that define when and against which assets, items, or operational contexts a given maintenance requirement applies. Effectivities act as the boundary conditions that determine whether a maintenance requirement is valid for a specific unit, relationship, or inventory item at a given point in time, making this table central to scheduling and execution logic in depot and fleet maintenance workflows.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this table is satellite-leaning. Its heavy dependence on parent references — notably MR_HEADER_ID pointing to AHL_MR_HEADERS_B — indicates it behaves primarily as a descriptive attribute container attached to the maintenance requirement hub, with link-like characteristics introduced through its associations to relationships, inventory items, and PC nodes.

Key Information Stored

The table contains 35 documented columns. The most significant include:

• MR_EFFECTIVITY_ID — Surrogate primary key (AHL_MR_EFFECTIVITIES_PK) uniquely identifying each effectivity record.
• MR_HEADER_ID — Foreign key to AHL_MR_HEADERS_B, tying the effectivity to its parent maintenance requirement.
• NAME — Descriptive identifier; combined with MR_HEADER_ID it forms business unique key AHL_MR_EFFECTIVITIES_U2.
• THRESHOLD_DATE — The date from which the effectivity applies.
• INVENTORY_ITEM_ID and INVENTORY_ORG_ID — Foreign keys to MTL_SYSTEM_ITEMS_B, scoping the effectivity to a specific item and organization.
• RELATIONSHIP_ID — Foreign key to AHL_RELATIONSHIPS_B, linking effectivity to an asset relationship.
• PC_NODE_ID — Reference to AHL_PC_NODES_B, associating the effectivity with a configuration node.
• FLEET_HEADER_ID — Identifies the fleet to which the effectivity applies.
• DEFAULT_FLAG — Indicates whether this is the default effectivity for the requirement.
• PROGRAM_DURATION and PROGRAM_DURATION_UOM_CODE — Define the operational program duration and its unit of measure.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS for multi-tenant data segregation.
• OBJECT_VERSION_NUMBER and the audit columns (LAST_UPDATE_DATE, CREATED_BY, and related fields) — Support concurrency control and change tracking.
• Fifteen ATTRIBUTE columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) — Provide extensibility for customer-specific data.

The unique index AHL_MR_EFFECTIVITIES_U1 mirrors the primary key, while U2 confirms the business key of MR_HEADER_ID combined with NAME.

Common Use Cases and Queries

Typical usages include retrieving all effectivities for a maintenance requirement, determining active effectivities by threshold date, and reconciling effectivity scope against item or relationship assignments. A representative query joining to the parent header:

• SELECT e.mr_effectivity_id, e.name, e.threshold_date FROM ahl_mr_effectivities e JOIN ahl_mr_headers_b h ON e.mr_header_id = h.mr_header_id WHERE e.mr_header_id = :p_mr_header_id;
• Filtering by item scope: WHERE e.inventory_item_id = :p_item AND e.inventory_org_id = :p_org;
• Reporting default effectivities across a fleet using FLEET_HEADER_ID and DEFAULT_FLAG.

Reporting scenarios commonly surface effectivity detail lines (via AHL_MR_EFFECTIVITY_DTLS) and intervals (via AHL_MR_INTERVALS) to build complete maintenance program definition reports.

Related Objects

The most significant related objects and their join columns are:

• AHL_MR_HEADERS_B — Parent requirement; join on MR_HEADER_ID.
• AHL_RELATIONSHIPS_B — Join on RELATIONSHIP_ID.
• MTL_SYSTEM_ITEMS_B — Join on INVENTORY_ITEM_ID and INVENTORY_ORG_ID.
• AHL_PC_NODES_B — Join on PC_NODE_ID.
• FND_SECURITY_GROUPS — Join on SECURITY_GROUP_ID.
• AHL_MR_EFFECTIVITY_DTLS — Child detail table; join on MR_EFFECTIVITY_ID.
• AHL_MR_INTERVALS — Interval definitions; join on MR_EFFECTIVITY_ID.
• AHL_UNIT_EFFECTIVITIES_B — Unit-level effectivities; join on MR_EFFECTIVITY_ID.
• AHL_APPLICABLE_MRS — Applicable maintenance requirements; join on MR_EFFECTIVITY_ID.