Search Results csi_item_instances

Overview

The CSI_ITEM_INSTANCES table is the central transactional repository within the Oracle E-Business Suite Install Base (CSI) module for versions 12.1.1 and 12.2.2. It serves as the master table for tracking individual, serialized instances of inventory items throughout their lifecycle. Each row represents a unique physical or logical asset, capturing its identity, current status, location, and associated transactional context. The table's extensive foreign key relationships integrate Install Base data with core EBS pillars like Inventory (INV), Order Management (OM), Purchasing (PO), Work in Process (WIP), and Projects (PA), enabling comprehensive asset tracking from procurement through manufacturing, sales, installation, and service.

Key Information Stored

The table's primary key is INSTANCE_ID, which uniquely identifies each tracked item instance. Critical columns define the instance's identity and state. Key inventory attributes include INVENTORY_ITEM_ID, INV_MASTER_ORGANIZATION_ID, SERIAL_NUMBER, and LOT_NUMBER, linking to the MTL_SYSTEM_ITEMS_B, MTL_SERIAL_NUMBERS, and MTL_LOT_NUMBERS tables. Status is managed via INSTANCE_STATUS_ID (link to CSI_INSTANCE_STATUSES) and INSTANCE_CONDITION_ID (link to MTL_MATERIAL_STATUSES_B). Location is tracked through LOCATION_ID, referencing HZ_PARTY_SITES. The table also stores references to active transactional documents, such as IN_TRANSIT_ORDER_LINE_ID (OE_ORDER_LINES_ALL), PO_ORDER_LINE_ID (PO_LINES_ALL), WIP_JOB_ID (WIP_DISCRETE_JOBS), and PA_PROJECT_ID/PA_PROJECT_TASK_ID. The SYSTEM_ID column allows grouping instances into logical systems via CSI_SYSTEMS_B.

Common Use Cases and Queries

A primary use case is generating an asset register or serial number track report. A typical query joins CSI_ITEM_INSTANCES with inventory, customer, and status tables:

• SELECT cii.instance_id, cii.serial_number, msib.segment1 item_code, msib.description, hps.party_site_name location, cis.name status FROM csi_item_instances cii, mtl_system_items_b msib, hz_party_sites hps, csi_instance_statuses cis WHERE cii.inventory_item_id = msib.inventory_item_id AND cii.inv_master_organization_id = msib.organization_id AND cii.location_id = hps.party_site_id(+) AND cii.instance_status_id = cis.instance_status_id;

Another critical scenario is tracing an instance's transactional history by querying its linked order, project, or job identifiers. Support teams frequently query this table to validate warranty coverage or service eligibility based on instance status and condition. The table is also central to asset reconciliation processes, where physical stock counts are matched against Install Base records via serial and lot numbers.

Related Objects

CSI_ITEM_INSTANCES has extensive dependencies. Key foreign key relationships, as documented, include MTL_SYSTEM_ITEMS_B, MTL_SERIAL_NUMBERS, CSI_INSTANCE_STATUSES, HZ_PARTY_SITES, OE_ORDER_LINES_ALL, PO_LINES_ALL, WIP_DISCRETE_JOBS, and PA_PROJECTS_ALL. It is referenced by other CSI tables that track instance relationships, warranties, and service history. Notably, it is also referenced by objects in the Service and Complex Maintenance Repair & Overhaul (CMRO) modules, specifically AHL_PART_CHANGES (for part swaps), AHL_UC_HEADERS_H (for unit configuration), and AHL_UF_HEADERS (for unit effectivity), highlighting its role in advanced service operations. The primary key constraint is CSI_ITEM_INSTANCES_PK on INSTANCE_ID.