Search Results ahl_position_reference




Overview
Explore in AI Chat

Overview

APPS.AHL_UNIT_DETAILS_V is a reporting and integration view within the Oracle E-Business Suite Enterprise Asset Management (eAM) and Complex Maintenance, Repair, and Overhaul (CMRO) modules. It consolidates unit configuration data stored across the AHL (Asset Lifecycle / CMRO) and CSI (Customer Support / Installed Base) schemas into a single flattened structure that is convenient for inquiry screens, concurrent program extracts, and interface loads. The view resolves coded values for position reference and position necessity through FND_LOOKUPS, joins item instance master data, and returns a unified set of columns describing each configured unit within a maintenance configuration header.

Because the underlying logical model is a self-referencing relationship structure, the view performs a union of two query branches: the first branch materializes configured component positions with a valid CSI_II_RELATIONSHIPS record, and the second branch returns positions lacking such a child relationship, replacing key relationship columns with NULLs. This pattern ensures that every position defined against a maintenance configuration header appears exactly once, whether or not it has been instantiated as a child component instance.

Underlying Base Objects

The view is documented as being defined over the following base objects:

  • AHL_MC_RELATIONSHIPS — the core maintenance configuration relationship table; supplies relationship_id, parent_relationship_id, position_ref_code, and position_necessity_code.
  • AHL_MC_HEADERS_VL — the maintenance configuration header view; supplies the header description.
  • AHL_UNIT_CONFIG_HEADERS — the unit configuration header synonym, used in the configuration hierarchy context.
  • CSI_II_RELATIONSHIPS — the installed base instance-to-instance relationship synonym; joins by position_reference and relationship_type_code = 'COMPONENT-OF'.
  • CSI_ITEM_INSTANCES — the installed base instance synonym; supplies instance_id, serial number, revision, lot, quantity, and UOM.
  • CSI_INST_EXTEND_ATTRIB_V — extended attribute view; provides the manufacturing date via attribute code 'AHL_MFG_DATE' at GLOBAL level.
  • FND_LOOKUPS — supplies lookup meanings for AHL_POSITION_REFERENCE and AHL_POSITION_NECESSITY, defaulting necessity to 'MANDATORY' via NVL.
  • MTL_SYSTEM_ITEMS_KFV — key flexfield view; supplies the concatenated item number segments.
  • FND_GLOBAL — the standard EBS package, referenced for session context (org/user) during resolution.

Key Columns

  • RELATIONSHIP_ID — identifier of the maintenance configuration relationship row.
  • CSI_ITEM_INSTANCE_ID — installed base instance ID of the configured unit.
  • POSITION_REF_MEANING — decoded description of the position reference code (the object the user searched for, "ahl_position_reference").
  • POSITION_NECESSITY_MEANING — decoded necessity value (MANDATORY, OPTIONAL, etc.).
  • PARENT_CSI_INSTANCE_ID — parent instance in the assembly hierarchy.
  • SERIAL_NUMBER, REVISION, LOT_NUMBER — identifying attributes of the installed unit.
  • QUANTITY, UOM_CODE — quantity and unit of measure for the position.
  • ITEM_NUMBER — concatenated key flexfield segments of the inventory item.
  • MFG_DATE — manufacturing date derived from the extended attribute.
  • DESCRIPTION — maintenance configuration header description.

Common Use Cases and Queries

The view is used to list all configured positions for a maintenance configuration header, to drive unit configuration inquiry pages, and to extract as-maintained bills of material for interface into MRO or external systems. A typical query filters by relationship or header:

  • SELECT relationship_id, item_number, serial_number, position_ref_meaning, position_necessity_meaning FROM apps.ahl_unit_details_v WHERE relationship_id = :p_relationship_id;
  • SELECT item_number, serial_number, quantity, uom_code FROM apps.ahl_unit_details_v WHERE parent_csi_instance_id = :p_parent_instance ORDER BY position_ref_meaning;
  • SELECT position_ref_meaning, COUNT(*) FROM apps.ahl_unit_details_v GROUP BY position_ref_meaning ORDER BY 2 DESC;

Because the view applies active date filtering (active_start_date/active_end_date), results reflect only currently effective relationships at query time, so historical reporting should join to the base tables directly rather than relying solely on this view.