Search Results external_reference




Overview
Explore in AI Chat

Overview

APPS.CSI_WIP_ASSEMBLIES_V is a reporting and integration view within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments that exposes Work in Process (WIP) assembly instances as they are tracked by the CSI (Complex Maintenance, Repair, and Overhaul / Installed Base) transaction model. The view consolidates assembly instance data originating from two distinct sources: active CSI transaction lines generated during WIP assembly completion and return transactions, and the master item instance repository. This dual-source design allows downstream consumers to see WIP assemblies in a single unified query without directly joining the transactional and master-instance tables.

The view is registered under the APPS schema and is commonly referenced in Installed Base reconciliation, WIP completion validity reports, and custom integrations that need to reconcile physical assembly serial and lot information with the WIP job that produced it. It is particularly relevant to users searching for the external_reference attribute, which is surfaced directly by this view to carry external system identifiers associated with the WIP transaction.

Underlying Base Objects

Three documented base objects underpin the view, all accessed through APPS synonyms:

  • CSI_T_TRANSACTION_LINES — the transactional header table identifying each CSI transaction and its originating source (for example, source_transaction_table = 'WIP_ENTITIES').
  • CSI_T_TXN_LINE_DETAILS — the detail table holding per-instance data such as serial number, lot number, quantity, and the external reference for each transaction line.
  • CSI_ITEM_INSTANCES — the master item instance table, supplying instance-level attributes for assemblies that have no active CSI transaction lines.

The view is defined as a UNION of two SELECT statements. The first branch joins CSI_T_TRANSACTION_LINES to CSI_T_TXN_LINE_DETAILS, filtered on the WIP_ENTITIES source table and a source_transaction_flag of 'Y', to return transactionally active assemblies. The second branch reads CSI_ITEM_INSTANCES directly, using a NOT EXISTS subquery to exclude instances already represented in active transaction lines, synthesizing constant values for source_transaction_flag, processing_status, and transaction identifiers.

Key Columns

The view exposes the following notable columns:

  • instance_id — the CSI item instance identifier, the primary join key to other Installed Base views.
  • inventory_item_id and inv_organization_id — the assembly item and inventory organization. In the second branch this maps from last_vld_organization_id.
  • serial_number and lot_number — traceability attributes for the tracked assembly.
  • quantity and unit_of_measure — the assembled quantity and its UOM.
  • instance_status_id — the current Installed Base status of the instance.
  • location_type_code and location_id — where the instance is currently held.
  • external_reference — a free-form external system reference carried from the transaction line detail; useful for cross-system reconciliation. In the second branch it is sourced directly from CSI_ITEM_INSTANCES.
  • last_wip_job_id — the WIP job that last affected the instance, mapped from source_transaction_id in the transactional branch.
  • source_transaction_flag, processing_status, transaction_line_id, txn_line_detail_id — transaction lineage columns; the second branch returns 'Y', NULL, and NULL respectively for these.

Common Use Cases and Queries

Typical uses include reconciling WIP assembly completions against Installed Base records, auditing serialized assemblies by external reference, and diagnosing assemblies that exist in CSI_ITEM_INSTANCES but lack a corresponding transaction line.

Sample query retrieving WIP assemblies by external reference:

  • SELECT instance_id, inventory_item_id, inv_organization_id, serial_number, lot_number, external_reference, last_wip_job_id FROM apps.csi_wip_assemblies_v WHERE external_reference = :p_ref;

Sample query listing assemblies with transaction lineage:

  • SELECT instance_id, transaction_line_id, txn_line_detail_id, source_transaction_flag, processing_status FROM apps.csi_wip_assemblies_v WHERE transaction_line_id IS NOT NULL;

Sample query identifying orphaned instances (no active transaction line):

  • SELECT instance_id, serial_number, external_reference FROM apps.csi_wip_assemblies_v WHERE transaction_line_id IS NULL AND source_transaction_flag = 'Y';

Because the view performs set-based UNION logic over transactional and master tables, queries should include selective predicates on instance_id, inventory_item_id, or external_reference to constrain the result set and avoid full-table scans across the CSI transaction tables.