Search Results ahl_csi_instance_relns_v




Overview
Explore in AI Chat

Overview

The AHL_CSI_INSTANCE_RELNS_V view is a reporting and integration construct within the AHL – Complex Maintenance Repair and Overhaul (CMRO) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the hierarchical relationships that exist between item instances tracked in Oracle's Complex Maintenance, Repair and Overhaul and Enterprise Asset Management data model. Specifically, the view flattens the component-of hierarchy maintained by the CSI (Customer Service/Installed Base) instance relationships into a single row per instance, returning the instance identifier, its immediate parent instance identifier (where one exists), and the root instance identifier that represents the top-level unit to which the instance ultimately belongs.

Because AHL CMRO works extensively with complex, multi-level configured assets and their subcomponents, downstream applications and reports frequently need to resolve a component instance back to the higher-level assembly or unit it is mounted in. The AHL_CSI_INSTANCE_RELNS_V view was created to answer that question directly, and it is documented as a VALID view owned by the APPS schema. It is therefore a read-only object intended for querying, not for direct DML.

Underlying Base Objects

The view is defined over two synonymous base objects in the APPS schema: CSI_II_RELATIONSHIPS and CSI_ITEM_INSTANCES. The first stores the instance-to-instance relationship records, including SUBJECT_ID, OBJECT_ID, RELATIONSHIP_TYPE_CODE, ACTIVE_START_DATE, and ACTIVE_END_DATE. The second stores each item instance master record keyed by INSTANCE_ID.

The view is constructed as a UNION of two branches:

  • The first branch selects from CSI_II_RELATIONSHIPS where RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' and the relationship is currently active. It returns SUBJECT_ID as INSTANCE_ID, OBJECT_ID as PARENT_INSTANCE_ID, and a scalar subquery that walks the hierarchy upward using CONNECT BY PRIOR PARENT.OBJECT_ID = PARENT.SUBJECT_ID to resolve the topmost ancestor as ROOT_INSTANCE_ID.
  • The second branch selects standalone instances from CSI_ITEM_INSTANCES that have no active component-of relationship as a subject, returning the instance as its own root.

Both branches apply Sysdate-based effective-dating filters against ACTIVE_START_DATE and ACTIVE_END_DATE using TRUNC(SYSDATE) and NVL defaults, ensuring only currently valid relationships are surfaced.

Key Columns

  • INSTANCE_ID – The item instance identifier for the row. In the first branch it is sourced from CSI_II_RELATIONSHIPS.SUBJECT_ID; in the second from CSI_ITEM_INSTANCES.INSTANCE_ID.
  • PARENT_INSTANCE_ID – The immediate parent (OBJECT_ID) of the instance in a component-of relationship. This is the column most closely associated with the search term "parent_instance_id". It is NULL for top-level or standalone instances.
  • ROOT_INSTANCE_ID – The identifier of the topmost unit instance in the hierarchy. For standalone instances it equals INSTANCE_ID.

Common Use Cases and Queries

Typical use cases include resolving the full asset structure for a unit, reporting on components by their parent assembly, and identifying orphaned or top-level instances. A simple lookup for the parent of a given instance:

  • SELECT instance_id, parent_instance_id, root_instance_id FROM apps.ahl_csi_instance_relns_v WHERE instance_id = :p_instance_id;
  • SELECT instance_id FROM apps.ahl_csi_instance_relns_v WHERE root_instance_id = :p_unit_instance_id; — returns all instances belonging to a given unit.
  • SELECT instance_id FROM apps.ahl_csi_instance_relns_v WHERE parent_instance_id IS NULL; — returns top-level units.

Because the view is a read-only UNION query with a correlated hierarchical subquery, performance is best when filtered on INSTANCE_ID, PARENT_INSTANCE_ID, or ROOT_INSTANCE_ID rather than scanned in full.