Results for “ahl_mr_items_v”

38 results




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

Overview

AHL_MR_ITEMS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It resides within the AHL product family, Complex Maintenance Repair and Overhaul (CMRO), and its documented purpose is to return the affected items for all maintenance requirements. In practical terms, the view answers the question: given a maintenance requirement header and a specific effectivity record, which inventory items are in scope?

The view is a resolution layer rather than a transactional entity. It does not store data; it joins maintenance requirement effectivities to position alternates and to the item master, then projects a small, purpose-built result set. Because the last column is populated from MTL_SYSTEM_ITEMS_KFV, the view returns user-facing concatenated item flexfield values rather than raw segment columns, which makes it directly suitable for concurrent programs, BI Publisher reports, OAF-based pages, and outbound integrations that need readable item identification. The view is documented as VALID and is referenced by the CMRO maintenance requirement feature set.

Underlying Base Objects

The documented base objects are AHL_MR_EFFECTIVITIES (synonym), AHL_MR_HEADERS_B (synonym), AHL_POSITION_ALTERNATES_V (view), FND_PROFILE (package), and MTL_SYSTEM_ITEMS_KFV (synonym). The view body is defined as a UNION of two SELECT statements, which is the structural key to understanding its behaviour.

  • AHL_MR_EFFECTIVITIES and its application-layer variant AHL_MR_EFFECTIVITIES_APP_V supply MR_HEADER_ID, MR_EFFECTIVITY_ID, RELATIONSHIP_ID, and INVENTORY_ITEM_ID. This is the driving table in both branches of the UNION.
  • AHL_POSITION_ALTERNATES_V supplies the item-to-position relationship used in the first branch, resolving effectivities that are defined by position rather than by a directly named item.
  • MTL_SYSTEM_ITEMS_KFV is the key flexfield view over the item master and provides both the join key (INVENTORY_ITEM_ID) and the display value (CONCATENATED_SEGMENTS, surfaced as ITEM_NUMBER).
  • AHL_MR_HEADERS_B anchors the maintenance requirement header identity referenced by MR_HEADER_ID.
  • FND_PROFILE is referenced in the dependency list, typically for organization or operating-unit context resolution used by the position alternates view.

The first branch of the UNION handles effectivity rows where INVENTORY_ITEM_ID IS NULL but a RELATIONSHIP_ID exists, expanding them through position alternates. The second branch handles effectivity rows where INVENTORY_ITEM_ID IS NOT NULL, taking the item directly. SELECT DISTINCT is applied in both branches, so duplicate item/effectivity combinations are collapsed.

Key Columns

  • MR_HEADER_ID — Identifier of the parent maintenance requirement header. Joins to AHL_MR_HEADERS_B.
  • MR_EFFECTIVITY_ID — Identifier of the individual effectivity line, linking the item back to its effectivity definition.
  • INVENTORY_ITEM_ID — Surrogate key of the affected item, joinable to MTL_SYSTEM_ITEMS_B and related item tables.
  • ITEM_NUMBER — The concatenated item flexfield segments sourced from MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS, providing the human-readable item identifier.

Common Use Cases and Queries

Typical uses include listing all items affected by a given maintenance requirement, driving item-level validation or pick lists in CMRO execution pages, and feeding downstream work order or spares planning processes.

Retrieve all affected items for a specific maintenance requirement:

  • SELECT mr_header_id, mr_effectivity_id, inventory_item_id, item_number FROM apps.ahl_mr_items_v WHERE mr_header_id = :p_mr_header_id ORDER BY item_number;

Locate all maintenance requirements that affect a particular item, which is useful for impact analysis before an item is obsoleted:

  • SELECT v.mr_header_id, h.mr_header_name, v.item_number FROM apps.ahl_mr_items_v v, apps.ahl_mr_headers_b h WHERE v.mr_header_id = h.mr_header_id AND v.inventory_item_id = :p_item_id;

Because the view already performs the flexfield concatenation and de-duplication, it is generally preferable to query AHL_MR_ITEMS_V directly rather than re-deriving the position-alternate logic against the base effectivity tables.