Results for “cs_systems_all_vl”

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

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

• ROW_ID / SYSTEM_ID — ROW_ID surfaces the underlying base-table ROWID; SYSTEM_ID is the unique system identifier and the join key to the translation table.
• CUSTOMER_ID — Owning customer of the installed-base system.
• SYSTEM_TYPE_CODE / CONFIG_SYSTEM_TYPE — Classification and configuration type of the system.
• SERIAL_NUMBER — Manufacturer or internal serial reference.
• PARENT_SYSTEM_ID / AUTOCREATED_FROM_SYSTEM_ID — Self-referencing relationships supporting system hierarchies and auto-created records.
• Contact and site columns — TECHNICAL_CONTACT_ID, SERVICE_ADMIN_CONTACT_ID, BILL_TO_CONTACT_ID, BILL_TO_SITE_USE_ID, SHIP_TO_SITE_USE_ID, SHIP_TO_CONTACT_ID, INSTALL_SITE_USE_ID.
• START_DATE_ACTIVE / END_DATE_ACTIVE / COTERMINATE_DAY_MONTH — Lifecycle and cotermination attributes.
• Attribute1–15 and CONTEXT — Descriptive flexfield segments and context.
• NAME / DESCRIPTION — Translatable values sourced from the TL table in the session language.
• ORG_ID — Operating unit that owns the system, enabling multi-org filtering.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

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.