Search Results csi_systems_rg_v




Overview
Explore in AI Chat

Overview

The CSI_SYSTEMS_RG_V view is a reporting and integration object in the Oracle E-Business Suite Install Base (CSI) module, owned by the APPS schema and marked VALID in ETRM 12.2.2. It is described as a "Systems view for the record groups," meaning it presents a consolidated, denormalized projection of customer-owned systems that is suitable for record group consumption, concurrent program parameter lists, and value-set or LOV (list of values) driven forms. Rather than requiring the caller to join the transactional system tables to the Trading Community Architecture (TCA) party model and the application lookup tables, the view exposes a single flat result set combining system identification, customer identity, and system classification. This is the primary reason it is widely referenced in reporting queries, discoverer worksheets, and integration extracts where system-to-customer relationships must be resolved without embedding complex join logic.

Underlying Base Objects

The view is defined over six referenced objects, all materialized as APPS synonyms: CSI_SYSTEMS_B, CSI_SYSTEMS_TL, CSI_INSTALL_PARAMETERS, HZ_CUST_ACCOUNTS, HZ_PARTIES, and FND_LOOKUP_VALUES. CSI_SYSTEMS_B is the base table supplying system identity and the customer foreign key; CSI_SYSTEMS_TL is the translation table providing the language-dependent system name and description, joined on SYSTEM_ID and constrained by USERENV('LANG'). HZ_CUST_ACCOUNTS links the system's CUSTOMER_ID to a TCA account, and HZ_PARTIES supplies the party name. FND_LOOKUP_VALUES is an outer join resolving the CSI_SYSTEM_TYPE lookup code (application ID 542) to a meaningful system type. CSI_INSTALL_PARAMETERS is used in a NOT IN subquery against INTERNAL_PARTY_ID, excluding internal, non-customer parties from the result set.

Key Columns

  • SYSTEM_ID — Surrogate primary key of the Install Base system record.
  • SYSTEM — Language-specific system name from CSI_SYSTEMS_TL.
  • SERIAL_NUMBER — The system number (CSB.SYSTEM_NUMBER) identifying the physical or logical system.
  • PARENT_SYSTEM_ID — Self-referencing pointer establishing system hierarchies.
  • PARTY_NAME — Customer party name resolved from HZ_PARTIES via PARTY_ID.
  • ACCOUNT_NUMBER — TCA customer account number.
  • SYSTEM_DESCRIPTION — Language-specific description.
  • SYSTEM_TYPE — Decoded meaning of the CSI_SYSTEM_TYPE lookup code.
  • CUSTOMER_ID, CUST_ACCOUNT_ID, PARTY_ID — Customer identifiers spanning the CSI and TCA models.

Common Use Cases and Queries

The most frequent use is retrieval of all customer-owned systems for a given party or account. The INTERNAL_PARTY_ID filter ensures only external customers are returned, which is directly relevant when the search term is internal_party_id: the view explicitly suppresses parties listed in CSI_INSTALL_PARAMETERS.INTERNAL_PARTY_ID.

Example:

SELECT system_id, system, serial_number, party_name, account_number, system_type
FROM   apps.csi_systems_rg_v
WHERE  party_id = :p_party_id;

For reporting on systems by type or hierarchy:

SELECT system, serial_number, parent_system_id, system_type
FROM   apps.csi_systems_rg_v
WHERE  system_type = 'PRODUCTION';

Because the view performs the TCA and lookup joins internally, it is well suited to record group definitions, value sets, and BI Publisher data templates, reducing the need to replicate join logic against the base CSI tables.