Search Results ahl_appln_usage




Overview
Explore in AI Chat

Overview

APPS.AHL_MR_ITEMS_V is a reporting view in the Oracle E-Business Suite Advanced Product Catalog (formerly Oracle Product Hub) module. It resolves the set of inventory items associated with Maintenance Requirement (MR) headers and their effectivity records, and it exposes those items together with their concatenated (flexfield-segmented) item identifiers. The view is a convenience layer intended for reporting, integration extracts, and diagnostic queries that need to flatten the many-to-many relationship between maintenance requirement definitions and inventory items without requiring the caller to navigate the underlying EFFECTIVITY and ALTERNATES structures directly.

A defining characteristic of the view, and the reason it is frequently surfaced in searches for ahl_appln_usage, is that it is filtered by the profile option AHL_APPLN_USAGE. Every row returned by the view is constrained to MR headers whose APPLICATION_USG_CODE equals the current value of that profile option for the querying session. In practice this means the same physical data set produces different result sets for different responsibilities or users, depending on their profile configuration. This behavior is central to understanding both the view's purpose and any discrepancies observed when two users run the "same" report.

Underlying Base Objects

The view is owned by APPS and is defined over the following documented objects:

  • AHL_MR_EFFECTIVITIES (synonym) — the effectivity records linking MR headers to relationships and, optionally, to inventory items directly.
  • AHL_MR_HEADERS_B (synonym) — the base maintenance requirement header table, which carries the APPLICATION_USG_CODE column used for profile-based filtering.
  • AHL_POSITION_ALTERNATES_V (view) — a view over position alternates that supplies an inventory item when the effectivity row itself does not carry one.
  • MTL_SYSTEM_ITEMS_KFV (synonym) — the key flexfield view over inventory items, providing CONCATENATED_SEGMENTS.
  • FND_PROFILE (package) — invoked at parse/execution time via FND_PROFILE.VALUE('AHL_APPLN_USAGE').

The view body uses a UNION ALL over two branches. The first branch joins AHL_POSITION_ALTERNATES_V, AHL_MR_EFFECTIVITIES, and AHL_MR_HEADERS_B to derive items where the effectivity's INVENTORY_ITEM_ID is null and a relationship identifier exists. The second branch takes items where INVENTORY_ITEM_ID is populated on the effectivity itself. Both branches are filtered by APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE'). The combined result is joined to MTL_SYSTEM_ITEMS_KFV on INVENTORY_ITEM_ID, and a DISTINCT is applied.

Key Columns

  • MR_HEADER_ID — identifier of the maintenance requirement header. Joins to AHL_MR_HEADERS_B.
  • MR_EFFECTIVITY_ID — identifier of the effectivity record. Joins to AHL_MR_EFFECTIVITIES.
  • INVENTORY_ITEM_ID — inventory item associated with the MR/effectivity, derived either from the effectivity directly or from position alternates.
  • CONCATENATED_SEGMENTS — the user-facing flexfield concatenation of the item, sourced from MTL_SYSTEM_ITEMS_KFV.

Common Use Cases and Queries

Typical uses include listing all items governed by a maintenance requirement, validating effectivity coverage, and driving downstream extracts that require item segment values.

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

Because the profile filter is inside the view, callers cannot bypass it. When results appear incomplete, the first diagnostic step is to compare FND_PROFILE.VALUE('AHL_APPLN_USAGE') across sessions. Note also that the view relies on DISTINCT and a UNION ALL; it is unsuitable for high-volume, low-latency integration paths without review of execution plans.