Search Results ahl_position_alternates_v




Overview
Explore in AI Chat

Overview

AHL_POSITION_ALTERNATES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AHL - Complex Maintenance Repair and Overhaul (CMRO) product family. The view exposes position-level alternate item data associated with maintenance configurations. In the CMRO data model, a "position" represents a slot within a maintenance configuration (MC) path, and "alternates" reflect the inventory items that may legitimately occupy that position when the configuration is complete. The view therefore serves as a reporting and integration surface that answers the question: for a given configuration relationship and path position, which inventory items (and their inventory organizations) are valid alternates, and over what active date range?

Because it is a view rather than a table, AHL_POSITION_ALTERNATES_V holds no data of its own. It is intended for use by concurrent programs, Oracle Reports, BI Publisher data models, OAF pages, and custom integration extracts that need a denormalized, query-ready projection across the AHL configuration and item association tables. The view is documented as VALID and is present in both EBS 12.1.1 and 12.2.2; the referenced base objects are consistent across these releases.

Underlying Base Objects

The documented definition joins several AHL configuration and item objects. The ETRM metadata lists the following referenced base objects: AHL_ITEM_ASSOCIATIONS_VL (VIEW), AHL_ITEM_GROUPS_B (SYNONYM), AHL_MC_CONFIG_RELATIONS (SYNONYM), AHL_MC_HEADERS_B (SYNONYM), AHL_MC_PATH_POSITION_NODES (SYNONYM), and AHL_MC_RELATIONSHIPS (SYNONYM). The view text confirms these joins through aliases MCR (AHL_MC_RELATIONSHIPS), MCH (AHL_MC_HEADERS_B), MCP (AHL_MC_PATH_POSITION_NODES), IGASS (AHL_ITEM_ASSOCIATIONS_VL), MPP (AHL_MC_PATH_POSITIONS), and IG (AHL_ITEM_GROUPS_B).

The join chain links a maintenance header to its relationships, path positions, and path position nodes, then restricts to item groups marked COMPLETE whose item associations supply the alternate inventory items. The header-level filter requires CONFIG_STATUS_CODE = 'COMPLETE'. The path node is restricted to the maximum sequence for the path position, ensuring only the terminal node of each position is considered. A correlated subquery, combining a direct relationship item group lookup with a UNION ALL branch through AHL_MC_CONFIG_RELATIONS, determines the qualifying item groups.

Key Columns

  • RELATIONSHIP_ID — Derived as MCP.PATH_POSITION_ID. Identifies the configuration relationship row to which the alternate belongs.
  • INVENTORY_ITEM_ID — The alternate item identifier sourced from AHL_ITEM_ASSOCIATIONS_VL, keyed through the qualifying item group.
  • INVENTORY_ORG_ID — The inventory organization in which that item is defined, also from the item association.
  • ACTIVE_START_DATE — The effective start date of the relationship, carried from AHL_MC_RELATIONSHIPS.
  • ACTIVE_END_DATE — The effective end date of the relationship, likewise from AHL_MC_RELATIONSHIPS.

Consumers should note that the alternate item metadata is not joined placeholders; each row represents a genuine item/organization pairing eligible for the position under a completed configuration.

Common Use Cases and Queries

Typical scenarios include validating that a configured maintenance position has at least one eligible alternate item, extracting alternates for a work order or configuration report, and feeding item substitution logic in maintenance execution or spares planning.

SELECT relationship_id,
       inventory_item_id,
       inventory_org_id,
       active_start_date,
       active_end_date
  FROM apps.ahl_position_alternates_v
 WHERE sysdate BETWEEN nvl(active_start_date, sysdate)
                   AND nvl(active_end_date, sysdate + 1);

To list alternates for a specific relationship:

SELECT v.inventory_item_id,
       v.inventory_org_id
  FROM apps.ahl_position_alternates_v v
 WHERE v.relationship_id = :relationship_id
 ORDER BY v.inventory_item_id;

Because the view is read-only and references only documented base objects, it is safe for ad hoc reporting. Query performance benefits from joining through RELATIONSHIP_ID ranges and from filtering on the active date columns rather than post-filtering in the reporting layer.