Search Results new_external_reference

Overview

CSI_IIH_DETAILS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the CSI (Install Base) product family. Its documented purpose is to expose Item Instance History details. In the Install Base data model, every change to an item instance — a serialized or non-serialized tracked unit — is captured as a history record, and CSI_IIH_DETAILS_V flattens that history into an old-value/new-value pair per attribute. The view therefore serves as the principal read interface for auditing and reporting on the lifecycle of an instance: what the instance looked like before a transaction and what it looks like after.

The view is particularly relevant to the search term old_instance_status_id. That column is materialized directly in the view text as IH.OLD_INSTANCE_STATUS_ID, the pre-transaction status of the instance. Because instance status drives business behavior such as whether an instance is shippable, transactable, or available for service, exposing the prior status alongside the new status makes the view the canonical source for status-change analysis. It is a standard, valid APPS view available in both 12.1.1 and 12.2.2, with the same structure in each release.

Underlying Base Objects

The view is defined over CSI_ITEM_INSTANCES_H, referenced through an APPS synonym and aliased IH in the view text. CSI_ITEM_INSTANCES_H is the Install Base history/audit table that stores a row for each tracked change to an instance. The view predominantly selects columns from this single history table and projects them with explicit aliases such as INSTANCE_HISTORY_ID, INSTANCE_ID, and TRANSACTION_ID.

The ETRM metadata lists an extensive set of additional referenced objects, including CSI_INSTANCE_STATUSES, MTL_MATERIAL_STATUSES, MTL_SYSTEM_ITEMS and MTL_SYSTEM_ITEMS_KFV, MTL_UNITS_OF_MEASURE_VL, CSI_SYSTEMS_VL, WIP_ENTITIES, PO_LINES_ALL, OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, PA_PROJECTS_ALL, PA_TASKS, HR_ORGANIZATION_UNITS, and the HR_SECURITY and HR_GENERAL packages. These relationships indicate that status, material, item, UOM, system, sourcing, order, project, and organization security information are all resolvable in the context of an instance history row, which supports flexible reporting and joins without requiring the caller to reconstruct the Install Base schema.

Key Columns

• OLD_INSTANCE_STATUS_ID / NEW_INSTANCE_STATUS_ID — the instance status before and after the transaction. The old value is resolved against CSI_INSTANCE_STATUSES.
• INSTANCE_HISTORY_ID — primary identifier for the history record.
• INSTANCE_ID — the instance affected by the change.
• TRANSACTION_ID — the transaction that produced the history row.
• OLD_INSTANCE_NUMBER / NEW_INSTANCE_NUMBER and OLD_EXTERNAL_REFERENCE / NEW_EXTERNAL_REFERENCE — identification changes.
• OLD_INVENTORY_ITEM_ID / NEW_INVENTORY_ITEM_ID — item reclassification history.
• OLD_SERIAL_NUMBER / NEW_SERIAL_NUMBER and the corresponding MFG_SERIAL_NUMBER_FLAG pairs.
• OLD_QUANTITY / NEW_QUANTITY and OLD_UNIT_OF_MEASURE / NEW_UNIT_OF_MEASURE.
• OLD_INSTANCE_CONDITION_ID / NEW_INSTANCE_CONDITION_ID — condition code changes.
• OLD_SYSTEM_ID / NEW_SYSTEM_ID and OLD_INSTANCE_TYPE_CODE / NEW_INSTANCE_TYPE_CODE.
• OLD_CUSTOMER_VIEW_FLAG, OLD_MERCHANT_VIEW_FLAG, OLD_SELLABLE_FLAG and their new-value counterparts.

Common Use Cases and Queries

The most common requirement is to report instance status transitions and the transaction responsible for each change. A straightforward query selects the identifying columns and the status pair, joined to the status table for descriptive names.

• Auditing status changes for a specific instance over time.
• Reconciling Install Base history against order, PO, WIP, or project activity using TRANSACTION_ID.
• Identifying instances whose status changed to or from a restricted status.

SELECT instance_history_id, instance_id, transaction_id, old_instance_status_id, new_instance_status_id
FROM apps.csi_iih_details_v
WHERE instance_id = :p_instance_id
ORDER BY instance_history_id;

To resolve condition and status descriptions, join CSI_INSTANCE_STATUSES on both status columns. Multitenancy and organization security are governed by the referenced HR_SECURITY and HR_GENERAL packages, so queries executed through APPS inherit the responsibility of the connecting user.