Search Results cs_systems_all_b




Overview
Explore in AI Chat

Overview

The CS_SYSTEMS_ALL_B table is the central master data repository for Service Request (SR) and Installed Base management within the Oracle E-Business Suite (EBS) Service (CS) module. It represents the physical or logical systems, equipment, or configurations that a customer owns and for which service can be requested. This table stores the core definition of a "System," which is a fundamental entity linking customers, their products, service history, and related transactions. Its role is critical for enabling service contracts, tracking service requests against specific assets, and managing the hierarchical relationships between complex system configurations.

Key Information Stored

While the specific column list is not detailed in the provided metadata, the foreign key relationships clearly define the critical data linkages stored in this table. The primary key is SYSTEM_ID. Essential foreign keys include CUSTOMER_ID, linking the system to the customer account (HZ_CUST_ACCOUNTS), and PARENT_SYSTEM_ID, which enables the creation of parent-child system hierarchies for modeling complex assemblies. The table also stores key location and contact information through foreign keys to site uses (INSTALL_SITE_USE_ID, SHIP_TO_SITE_USE_ID, BILL_TO_SITE_USE_ID) and parties (TECHNICAL_CONTACT_ID, SERVICE_ADMIN_CONTACT_ID). The AUTOCREATED_FROM_SYSTEM_ID column supports scenarios where systems are automatically generated from other systems.

Common Use Cases and Queries

Common operational and reporting scenarios involve querying this table to understand customer assets. A fundamental query retrieves all systems for a specific customer, often joining with the TCA (Trading Community Architecture) tables for customer details and the CS_CUSTOMER_PRODUCTS_ALL table for product information. Another critical use case is traversing system hierarchies to generate a complete bill of materials for a top-level asset, using recursive SQL or CONNECT BY queries on the PARENT_SYSTEM_ID self-referential foreign key. Analysts also frequently join CS_SYSTEMS_ALL_B with CS_SERVICE_REQUESTS (via CS_SYSTEM_SR_LINKS) to report on service history and incident patterns per asset.

  • Sample Query: Fetch system hierarchy for a root system:
    SELECT LEVEL, system_id, parent_system_id FROM cs_systems_all_b START WITH system_id = &root_id CONNECT BY PRIOR system_id = parent_system_id;
  • Sample Query: List systems with customer and install location:
    SELECT css.system_id, hca.account_number, hps.party_site_name FROM cs_systems_all_b css, hz_cust_accounts hca, hz_party_sites hps WHERE css.customer_id = hca.cust_account_id AND css.install_site_use_id = hps.party_site_id;

Related Objects

CS_SYSTEMS_ALL_B is a cornerstone table with extensive relationships. As per the metadata, it is directly referenced by several key service tables: CS_CUSTOMER_PRODUCTS_ALL (for products installed on the system), CS_SYSTEM_SR_LINKS (linking systems to service requests), and CS_SYSTEM_AUDIT (tracking changes to system attributes and hierarchy). It also has a self-referential relationship for modeling parent-child system structures. The table is deeply integrated with the TCA schema, linking to HZ_CUST_ACCOUNTS, HZ_PARTIES, and HZ_PARTY_SITES for customer, contact, and location data. In standard EBS deployments, this table is complemented by the corresponding _TL (translation) and _VL (view) objects for multilingual support.