Results for “csi_item_instances_u02”

12 results




Overview
Explore in AI Chat

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

Overview

CSI.CSI_ITEM_INSTANCES is the core master table of Oracle E-Business Suite's Customer Item / Installed Base (CSI) module, holding one row per tracked item instance — a serialized unit, lot-controlled assembly, or configured product tracked through its operational life. In Oracle EBS 12.1.1 and 12.2.2 this table serves as the authoritative repository for instance identity, ownership, location, and status, and it underpins Installed Base management, Field Service (AHL, CS), Depot Repair, Service Contracts (OKS), and Order Management integration.

The table resides in tablespace APPS_TS_TX_DATA with a PCT Free of 10. The primary key is CSI_ITEM_INSTANCES_PK on INSTANCE_ID. Under the supplied Data Vault heuristic classification, CSI_ITEM_INSTANCES models as a hub: it carries the durable business key of an item instance, and the many dependent CSI_I_*, CSI_T_*, AHL_*, CS_*, and OKS_* tables behave as satellites and links hanging off that hub. This is a modeling suggestion only, useful when mapping EBS objects into a Data Vault or dimensional warehouse.

Key Information Stored

The documented schema exposes 121 columns. The most significant are:

Common Use Cases and Queries

Typical uses include: locating an instance by serial number, listing all active instances for a customer or system, tracing where an instance sits physically, and driving field-service or depot-repair transactions.

  • Lookup by serial number (uses CSI_ITEM_INSTANCES_F01 / CSI_ITEM_INSTANCES_N05):
    SELECT instance_id, instance_number, serial_number, inventory_item_id
    FROM csi_item_instances
    WHERE UPPER(serial_number) = UPPER(:p_serial);
  • Active instances for a system (uses CSI_ITEM_INSTANCES_N07 and N04):
    SELECT instance_id, instance_number, inventory_item_id
    FROM csi_item_instances
    WHERE system_id = :p_system_id
    AND (active_end_date IS NULL OR active_end_date > SYSDATE);
  • Instances in an inventory location (uses CSI_ITEM_INSTANCES_N03):
    SELECT instance_id, serial_number, inv_subinventory_name
    FROM csi_item_instances
    WHERE location_type_code = 'INV'
    AND inv_organization_id = :p_org_id;
  • Instances owned by a party (uses CSI_ITEM_INSTANCES_N12):
    SELECT instance_id, inventory_item_id, owner_party_account_id
    FROM csi_item_instances
    WHERE owner_party_id = :p_party_id;
  • Instances associated with a project (uses CSI_ITEM_INSTANCES_N08):
    SELECT instance_id, serial_number, pa_project_id
    FROM csi_item_instances
    WHERE pa_project_id = :p_project_id;

Because the table is very wide (121 columns) and heavily indexed (17+ secondary indexes), reports should always filter on one of the documented indexed columns above; full-table scans on CSI_ITEM_INSTANCES are prohibitively expensive in production installations.

Related Objects

CSI_ITEM_INSTANCES is referenced by a large family of dependent tables through INSTANCE_ID or equivalent FK columns. The most significant include:

Outbound foreign keys confirm that the instance master validates against MTL_SYSTEM_ITEMS_B and MTL_ITEM_REVISIONS_B (via INVENTORY_ITEM_ID/INVENTORY_REVISION), MTL_SERIAL_NUMBERS (SERIAL_NUMBER), MTL_LOT_NUMBERS, MTL_MATERIAL_STATUSES_B (INSTANCE_CONDITION_ID), CSI_INSTANCE_STATUSES (INSTANCE_STATUS_ID), CSI_SYSTEMS_B (SYSTEM_ID), HZ_PARTY_SITES (LOCATION_ID), PA_PROJECTS_ALL and PA_TASKS, OE_ORDER_LINES_ALL (IN_TRANSIT_ORDER_LINE_ID), WIP_DISCRETE_JOBS, PO_LINES_ALL, and FND_SECURITY_GROUPS. Together these relationships establish CSI_ITEM_INSTANCES as the central hub object for installed-base data across the EBS Service, Manufacturing, Order Management, and Projects applications.