Search Results cs_systems_all_vl




Overview
Explore in AI Chat

Overview

CS_SYSTEMS_ALL_VL is a service-module view within Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2 that presents system (installed base instance) records across all organizations in a single-language, translated form. It belongs to the CS (Service) product family and is classified in the ETRM documentation as a "Systems (all orgs, single language view)." Its principal role is to expose the translatable name and description attributes of service systems alongside the compulsory, non-translatable system attributes, filtered to the session language. Because the view is multi-org aware (it carries ORG_ID) and its name ends in "_ALL," it is intended for cross-organization reporting, data extraction, and integration rather than for use as a DML target. Notably, the ETRM implementation record states "Not implemented in this database," indicating the object is documented but not physically installed in the referenced environment; querying it will only succeed where the underlying Service tables have been deployed.

Underlying Base Objects

The view text defines a two-table join between CS_SYSTEMS_ALL_B (the base table storing system attributes for all organizations) and CS_SYSTEMS_ALL_TL (the translation table storing language-specific NAME and DESCRIPTION values). The join predicate is B.SYSTEM_ID = T.SYSTEM_ID combined with T.LANGUAGE = USERENV('LANG'), which restricts the returned translation row to the language of the current session. The "_VL" suffix denotes precisely this pattern: a view that joins a base ("_B") table to a translation ("_TL") table while resolving the language from the session environment. The ID column SYSTEM_ID is the primary correlation key between the two objects. The ETRM metadata lists no further documented base objects, and the view text reveals no additional joins, so the relationship is strictly one translation row per base row per session language.

Key Columns

Common Use Cases and Queries

Typical uses include installed-base reporting, extract-load integrations, service contract preparation, and validation of system hierarchies. Because the view filters on USERENV('LANG'), NAME and DESCRIPTION are returned only for the caller's session language; additional languages require querying CS_SYSTEMS_ALL_TL directly.

  • List active systems for a customer: 
    SELECT system_id, name, serial_number, system_type_code
FROM   cs_systems_all_vl
WHERE  customer_id = :p_customer_id
AND    (end_date_active IS NULL OR end_date_active > SYSDATE);
  • Multi-org extract by operating unit: 
    SELECT org_id, system_id, name, config_system_type
FROM   cs_systems_all_vl
WHERE  org_id = :p_org_id
ORDER  BY name;
  • Hierarchy check using the parent reference: 
    SELECT child.system_id, child.name, child.parent_system_id
FROM   cs_systems_all_vl child
WHERE  child.parent_system_id IS NOT NULL;

These queries illustrate the view's suitability for read-only reporting. Direct DML against CS_SYSTEMS_ALL_VL should be avoided; insertions and updates must target CS_SYSTEMS_ALL_B and CS_SYSTEMS_ALL_TL.