Search Results parent_csi_instance_id

Overview

APPS.AHL_UNIT_DETAILS_V is a database view in the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environments, owned by the APPS schema and shipped as part of the AHL – Complex Maintenance Repair and Overhaul (CMRO) product. It is a VALID object in the ETRM repository and exposes Unit Configuration node details, specifically the item identity and position information associated with each node of a maintainable unit's configuration hierarchy. The view consolidates data from maintenance configuration headers, CSI item instances, CSI relationship definitions, inventory item key flexfield data, and descriptive flexfield attribute values into a single denormalized query surface.

The view is principally consumed by CMRO unit configuration screens, MRO reporting, and downstream integration extracts where the assembled structure of a unit (its components, their positions, and their instance-level attributes) must be presented alongside descriptive terminology rather than raw lookup codes. Because it resolves lookup meanings and concatenated item numbers at query time, it reduces the join complexity required by report authors and interface developers.

Underlying Base Objects

The documented base objects referenced by AHL_UNIT_DETAILS_V are AHL_MC_HEADERS_VL (view), AHL_MC_RELATIONSHIPS (synonym), AHL_UNIT_CONFIG_HEADERS (synonym), CSI_II_RELATIONSHIPS (synonym), CSI_INST_EXTEND_ATTRIB_V (view), CSI_ITEM_INSTANCES (synonym), FND_GLOBAL (package), FND_LOOKUPS (view), and MTL_SYSTEM_ITEMS_KFV (synonym). The core driver is AHL_MC_RELATIONSHIPS, which supplies the configuration relationship identifier, parent relationship, position reference code, and position necessity code. AHL_MC_HEADERS_VL provides the configuration header description, while CSI_II_RELATIONSHIPS links a subject item instance to its parent through the COMPONENT-OF relationship type, with active date filtering applied to the relationship's start and end dates. CSI_ITEM_INSTANCES supplies instance-level facts including serial number, revision, lot number, quantity, and unit of measure. MTL_SYSTEM_ITEMS_KFV resolves the inventory item to its concatenated segment item number, and FND_LOOKUPS is joined twice to translate the AHL_POSITION_REFERENCE and AHL_POSITION_NECESSITY lookup codes into their meanings.

Key Columns

• RELATIONSHIP_ID – Identifier of the maintenance configuration relationship row from AHL_MC_RELATIONSHIPS.
• CSI_ITEM_INSTANCE_ID – The CSI item instance identifier for the configuration node.
• POSITION_REF_MEANING and POSITION_NECESSITY_MEANING – Decoded meanings of the position reference and position necessity lookups.
• QUANTITY and UOM_CODE – Instance quantity and its unit of measure.
• PARENT_CSI_INSTANCE_ID – Parent instance identifier, establishing hierarchy.
• SERIAL_NUMBER, REVISION, LOT_NUMBER – Instance-level traceability attributes.
• ITEM_NUMBER – Concatenated flexfield segments from MTL_SYSTEM_ITEMS_KFV.
• MFG_DATE – Derived as TO_DATE(CIEA.ATTRIBUTE_VALUE,'DD/MM/YYYY') from the CSI_INST_EXTEND_ATTRIB_V attribute code AHL_MFG_DATE at GLOBAL level. This is the column matched by the search term "mfg_date".

Note that the MFG_DATE expression is subject to an NLS-dependent date format mask; values stored in an alternate format will raise conversion errors, so reports should be validated against the attribute population convention.

Common Use Cases and Queries

Typical usage includes unit configuration reporting, component bill-of-material style extracts, and maintenance planning queries that need item numbers, positions, and manufacturing dates for serialized components.

SELECT csi_item_instance_id,
       item_number,
       serial_number,
       position_ref_meaning,
       mfg_date
FROM   apps.ahl_unit_details_v
WHERE  parent_csi_instance_id = :instance_id
ORDER  BY position_ref_meaning;

SELECT item_number, COUNT(*)
FROM   apps.ahl_unit_details_v
WHERE  mfg_date >= TO_DATE(:from_date,'DD/MM/YYYY')
GROUP  BY item_number;