Search Results ahl_mr_effectivities_v




Overview
Explore in AI Chat

Overview

AHL_MR_EFFECTIVITIES_V is a reporting view owned by the APPS schema within the AHL (Complex Maintenance Repair and Overhaul) product family of Oracle E-Business Suite. It exposes maintenance requirement (MR) effectivity definitions in a denormalized, presentation-ready form, joining the base effectivity records to inventory item descriptions, configuration node names, position reference data, and lookup meanings. The object is documented as VALID in both EBS 12.1.1 and 12.2.2, and its structure is stable across those releases.

The view is not a transactional entry point. It is intended for inquiry, reporting, and integration: extract programs, custom concurrent requests, Oracle Discoverer or BI Publisher reports, and OAF/Forms read paths that must display an effectivity along with the human-readable item number, position reference, and unit-of-measure meaning. Because effectivity records drive which maintenance programs apply at which points in a configured product hierarchy, this view is the natural source for any report answering "what maintenance applies here, and how long does the program run?"

Underlying Base Objects

The view is defined over a small set of documented objects, joined primarily in an outer-join fashion so that effectivity rows are never lost when optional references are absent:

The documented metadata also lists AHL_MASTER_CONFIG_DETAILS_V and FND_PROFILE as referenced objects, reflecting the broader definition lineage of the underlying application view.

Key Columns

The projection applies DECODE logic to separate an effectivity that targets an item directly from one that targets a configuration position. The documented columns include:

  • MR_EFFECTIVITY_ID, OBJECT_VERSION_NUMBER — primary key and optimistic locking token for the effectivity row.
  • MR_HEADER_ID — foreign key to the maintenance requirement header.
  • INVENTORY_ITEM_ID, ITEM_NUMBER — populated when RELATIONSHIP_ID is null, i.e. the effectivity is item-based.
  • RELATIONSHIP_ID, POSITION_REF_MEANING — the configuration position reference; POSITION_REF_MEANING is returned only when a relationship exists.
  • POSITION_INVENTORY_ITEM_ID, POSITION_ITEM_NUMBER — the installed item at that position, again conditional on RELATIONSHIP_ID.
  • PC_NODE_ID, PC_NODE_NAME — the configuration node the effectivity applies to.
  • NAME — effectivity name; THRESHOLD_DATE — the effective-from date.
  • PROGRAM_DURATION, PROGRAM_DURATION_UOM_CODE, PROG — program interval and its unit of measure, with the UOM meaning resolved from the AHL_UMP_TIME_UOM lookup.
  • DEFAULT_FLAG — yes/no indicator, with its meaning resolved from AHL_YES_NO_TYPE.
  • ATTRIBUTE_CATEGORY through ATTRIBUTE15, APPLICATION_USG_CODE — descriptive flexfield context, attribute values, and the application usage classification.

Common Use Cases and Queries

Typical scenarios include listing all effectivities for a maintenance program, auditing item-based versus position-based effectivities, and feeding external planning systems with normalized effectivity data. A representative query follows:

  • SELECT mr_effectivity_id, mr_header_id, name, threshold_date, program_duration, prog, item_number, position_item_number, pc_node_name, default_flag FROM ahl_mr_effectivities_v WHERE mr_header_id = :p_mr_header_id ORDER BY threshold_date;
  • SELECT mr_effectivity_id, item_number, position_ref_meaning FROM ahl_mr_effectivities_v WHERE relationship_id IS NOT NULL; — isolates position-based effectivities.
  • SELECT inventory_item_id, item_number, COUNT(*) FROM ahl_mr_effectivities_v GROUP BY inventory_item_id, item_number; — items carrying the greatest number of maintenance effectivities.

Because the view resolves lookup and item descriptions internally, it is well suited to direct report data sources; however, the call to AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID per row means large extracts should be filtered by MR_HEADER_ID or configuration scope to control execution cost.