Results for “csi_item_instances”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CSI_ITEM_INSTANCES table, owned by the CSI (Install Base) schema, is the central repository for item instance details in Oracle E-Business Suite. Each row represents a discrete, serialized, or tracked instance of an inventory item — commonly referred to as a "customer product" or "install base instance" — and records where that instance resides, its operational condition, and its commercial or transactional origin. The table spans Releases 12.1.1 and 12.2.2 with a documented physical width of 121 columns, positioning it as the primary anchor for enterprise asset lifecycle, service, and depot repair operations.

In Data Vault modeling terms, the heuristic classification derived from its foreign-key structure is a hub: CSI_ITEM_INSTANCES holds the durable business key (INSTANCE_ID) around which numerous satellites, links, and dependent transaction tables are organized. The table is referenced by a wide fan-out of downstream objects, confirming its role as a foundational entity rather than a peripheral attribute store.

Key Information Stored

The surrogate primary key is INSTANCE_ID, enforced through CSI_ITEM_INSTANCES_PK. Two unique indexes act as business-key candidates: CSI_ITEM_INSTANCES_U01 on INSTANCE_ID and CSI_ITEM_INSTANCES_U02 on the human-readable INSTANCE_NUMBER, which is the value users typically reference in forms and reports.

• Identification: INSTANCE_NUMBER (user-facing key), EXTERNAL_REFERENCE, INSTANCE_DESCRIPTION, INSTANCE_TYPE_CODE
• Item linkage: INVENTORY_ITEM_ID, INVENTORY_REVISION, INV_MASTER_ORGANIZATION_ID, SERIAL_NUMBER, LOT_NUMBER, QUANTITY, UNIT_OF_MEASURE
• Condition and status: INSTANCE_CONDITION_ID, INSTANCE_STATUS_ID, OPERATIONAL_STATUS_CODE, ASSET_CRITICALITY_CODE
• Location and ownership: LOCATION_ID, LOCATION_TYPE_CODE, OWNER_PARTY_ID, OWNER_PARTY_ACCOUNT_ID, INV_ORGANIZATION_ID, INV_SUBINVENTORY_NAME
• Lifecycle dates: INSTALL_DATE, ACTIVE_START_DATE, ACTIVE_END_DATE, RETURN_BY_DATE, ACTUAL_RETURN_DATE
• Commercial context: PURCHASE_UNIT_PRICE, SALES_UNIT_PRICE, SUPPLIER_WARRANTY_EXP_DATE
• Audit and context: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER, plus the ATTRIBUTE1–ATTRIBUTE30 descriptive flexfield segment columns
• Configuration: SYSTEM_ID, CONFIG_INST_HDR_ID, CONFIG_INST_REV_NUM, CONFIG_VALID_STATUS

Common Use Cases and Queries

Install Base reporting, service contract coverage analysis, and depot repair intake all begin here. A typical query joins the table to MTL_SYSTEM_ITEMS_B to resolve item descriptions, and to HZ_PARTY_SITES to resolve the owning customer location:

• Locate all instances of a given item: SELECT instance_number, serial_number, instance_status_id FROM csi_item_instances WHERE inventory_item_id = :item_id;
• Identify instances at a customer site: join LOCATION_ID to HZ_PARTY_SITES.
• Track instances under warranty: filter on SUPPLIER_WARRANTY_EXP_DATE versus SYSDATE.
• Reconcile purchasing history: join PO_ORDER_LINE_ID to PO_LINES_ALL, or IN_TRANSIT_ORDER_LINE_ID to OE_ORDER_LINES_ALL.
• Report on failing or returned units using ACTUAL_RETURN_DATE and OPERATIONAL_STATUS_CODE.

Queries should generally constrain on INSTANCE_NUMBER or INSTANCE_STATUS_ID to avoid full scans across the large 121-column structure.

Related Objects

The table's FK footprint confirms its hub role. Significant relationships include:

• MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS_B, MTL_SERIAL_NUMBERS, MTL_LOT_NUMBERS — linked on INVENTORY_ITEM_ID/INV_MASTER_ORGANIZATION_ID for item and tracking resolution.
• CSI_SYSTEMS_B and CSI_INSTANCE_STATUSES — via SYSTEM_ID and INSTANCE_STATUS_ID.
• HZ_PARTY_SITES and PA_PROJECTS_ALL/PA_TASKS — via LOCATION_ID and project keys.
• OKL_TXL_ITM_INSTS, OKS_SUBSCR_HEADER_B, CS_INCIDENTS_ALL_B, and CSD_REPAIRS — downstream consumers joining on INSTANCE_ID or CUSTOMER_PRODUCT_ID.
• CSI_I_ASSETS, CSI_I_PARTIES, CSI_I_ORG_ASSIGNMENTS — satellite tables sharing INSTANCE_ID for asset, party, and organization attributes.

These reference relationships establish CSI_ITEM_INSTANCES as the integration anchor across CSI, OKL, OKS, CS, and AHL service-suite modules.