Search Results old_inventory_item_id

Overview

CSI_ITEM_INSTANCES_H is the item instance history table in the Oracle E-Business Suite Install Base (CSI) module. It preserves the before-and-after image of every attribute change applied to a record in CSI_ITEM_INSTANCES, the operational table that holds the current definition of each installed item instance. Where CSI_ITEM_INSTANCES answers the question "what is true about this instance now," CSI_ITEM_INSTANCES_H answers "what changed, when, and as a result of which transaction."

Each row in CSI_ITEM_INSTANCES_H is keyed by INSTANCE_HISTORY_ID and is tied to a single parent instance through INSTANCE_ID and to a single driving event through TRANSACTION_ID. The table is unusually wide, carrying 212 documented columns in ETRM 12.2.2, because it stores paired OLD_ and NEW_ values for the majority of the parent table's descriptive, locational, financial, and configuration attributes.

Under a Data Vault classification heuristic derived from its foreign key structure, this object is best described as a link (or, more precisely, a link carrying satellite-style attribute deltas), since it records a relationship between an instance and a transaction, enriched with descriptive change data. This classification is a modeling suggestion for warehouse design rather than a constraint enforced by EBS itself. In EBS terms, the table functions as a Type 2 style history or audit satellite of the installed base.

Key Information Stored

The most significant columns fall into three groups:

• INSTANCE_HISTORY_ID — the surrogate primary key, enforced by CSI_ITEM_INSTANCES_H_PK. This is the column users typically search for by name, and it is also the sole column of unique index CSI_ITEM_INSTANCES_H_U01.
• INSTANCE_ID — foreign key to CSI_ITEM_INSTANCES identifying the instance whose history this row records.
• TRANSACTION_ID — foreign key to CSI_TRANSACTIONS identifying the transaction that produced the change. Together with INSTANCE_ID it forms unique index CSI_ITEM_INSTANCES_H_U02, which is the strongest business-key candidate in the table.
• OLD_INSTANCE_NUMBER / NEW_INSTANCE_NUMBER — the instance number before and after the change.
• OLD_SERIAL_NUMBER / NEW_SERIAL_NUMBER — serial number transitions.
• OLD_INSTANCE_STATUS_ID / NEW_INSTANCE_STATUS_ID — the installed base status transition, such as active, expired, or returned.
• OLD_LOCATION_ID / NEW_LOCATION_ID and the related location type codes — physical or party location movement.
• OLD_INVENTORY_ITEM_ID / NEW_INVENTORY_ITEM_ID — item master reassignment, including the associated revision and organization columns.
• OLD_QUANTITY / NEW_QUANTITY — quantity deltas for the instance.
• OLD_ACTIVE_START_DATE / NEW_ACTIVE_START_DATE and the corresponding end dates — life-cycle date changes.
• FULL_DUMP_FLAG — indicates whether the row represents a complete image or a partial change set.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org style data security.
• MIGRATED_FLAG — identifies rows originating from migration or data conversion activity.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard WHO audit columns and optimistic locking.

The large OLD_ATTRIBUTE1 through OLD_ATTRIBUTE30 and NEW_ATTRIBUTE1 through NEW_ATTRIBUTE30 block mirrors the descriptive flexfield context columns OLD_CONTEXT and NEW_CONTEXT, allowing flexfield changes to be reconstructed.

Common Use Cases and Queries

The primary use case is audit and traceability: reconstructing the full change history of an instance for warranty disputes, service entitlement verification, or compliance review. A second common use is reconciliation between the Install Base and downstream modules such as Service Contracts, Advanced Supply Chain Planning, or Projects, where changes to location, status, or quantity must be dated accurately.

A typical pattern retrieves the ordered history for one instance:

• SELECT instance_history_id, transaction_id, old_instance_status_id, new_instance_status_id, last_update_date FROM csi.csi_item_instances_h WHERE instance_id = :p_instance_id ORDER BY last_update_date, instance_history_id;
• SELECT h.instance_history_id, h.transaction_id, t.transaction_type FROM csi.csi_item_instances_h h, csi.csi_transactions t WHERE h.transaction_id = t.transaction_id AND h.instance_id = :p_instance_id;

Reporting queries frequently filter on FULL_DUMP_FLAG to isolate complete snapshots, or on MIGRATED_FLAG to separate converted records from transactional history. Because the table is wide, reports should project only the OLD_/NEW_ pairs relevant to the question rather than selecting all columns. Joins back to CSI_ITEM_INSTANCES supply the current values for comparison.

Related Objects

• CSI_ITEM_INSTANCES — joined on CSI_ITEM_INSTANCES_H.INSTANCE_ID = CSI_ITEM_INSTANCES.INSTANCE_ID. The parent that holds the current instance definition.
• CSI_TRANSACTIONS — joined on CSI_ITEM_INSTANCES_H.TRANSACTION_ID = CSI_TRANSACTIONS.TRANSACTION_ID. Supplies the transaction type, date, and source that caused the change.
• FND_SECURITY_GROUPS — joined on CSI_ITEM_INSTANCES_H.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID. Enforces row-level data security.
• CSI_ITEM_INSTANCES_TL and related Install Base views — used to resolve translatable instance descriptions referenced by the OLD_/NEW_INSTANCE_DESCRIPTION columns.
• INV_ITEM_INSTANCES / MTL_SYSTEM_ITEMS_B — referenced indirectly through OLD_INVENTORY_ITEM_ID and NEW_INVENTORY_ITEM_ID to resolve item names.
• Public Install Base APIs (for example, the CSI instance and transaction APIs) — the documented procedural interface through which rows in the history table are created and maintained.

Together these objects form the audit backbone of the Install Base, with CSI_ITEM_INSTANCES_H providing the temporal dimension that the transactional tables alone cannot supply.