Search Results cs_system_audit

Overview

The CS_SYSTEM_AUDIT table is a core audit trail object within the Oracle E-Business Suite (EBS) Service (CS) module, specifically for releases 12.1.1 and 12.2.2. Its primary function is to record historical changes to critical attributes of a service system, as defined in the CS_SYSTEMS_ALL_B table. This table is essential for maintaining a complete, auditable history of system configuration, enabling compliance reporting, troubleshooting data integrity issues, and understanding the evolution of a customer's installed base over time. It operates by capturing both the old (previous) and current (new) values for a changed attribute at the moment of an update.

Key Information Stored

The table's structure is designed to store comparative audit snapshots. Its primary key is CS_SYSTEM_AUDIT_ID, which uniquely identifies each audit record. The central foreign key, SYSTEM_ID, links the audit entry to the specific system in CS_SYSTEMS_ALL_B. The table predominantly consists of paired columns that store the previous and updated values for key system relationships. As per the documented metadata, these critical attribute pairs include:

• Customer Account (OLD_CUSTOMER_ID, CURRENT_CUSTOMER_ID)
• Parent System (OLD_PARENT_SYSTEM_ID, CURR_PARENT_SYSTEM_ID)
• Bill-to, Ship-to, and Install Site Uses (e.g., OLD_BILL_TO_SITE_USE_ID, CURR_BILL_TO_SITE_USE_ID)
• Technical, Service Admin, Bill-to, and Ship-to Contacts (e.g., OLD_TECHNICAL_CONTACT_ID, CURR_TECHNICAL_CONTACT_ID)

Additional columns, such as creation date and created by, are typically present to record who made the change and when, though they are not explicitly listed in the provided excerpt.

Common Use Cases and Queries

The primary use case is auditing and historical reporting on system master data changes. Support and system administrators query this table to trace when a system was reassigned to a different customer, had its parent-child hierarchy modified, or had its service contacts updated. A typical query retrieves the audit trail for a specific system, joining to related TCA (Trading Community Architecture) tables to resolve IDs into meaningful names.

Sample Query: Audit History for a System
SELECT sa.creation_date, sa.created_by,
hz_old_acct.account_number old_customer,
hz_curr_acct.account_number current_customer
FROM cs_system_audit sa,
hz_cust_accounts hz_old_acct,
hz_cust_accounts hz_curr_acct
WHERE sa.system_id = :p_system_id
AND hz_old_acct.cust_account_id (+) = sa.old_customer_id
AND hz_curr_acct.cust_account_id (+) = sa.current_customer_id
AND (sa.old_customer_id IS NOT NULL OR sa.current_customer_id IS NOT NULL)
ORDER BY sa.creation_date DESC;

Related Objects

CS_SYSTEM_AUDIT has extensive foreign key relationships, as documented, making it a central hub for audit data. Its primary relationship is with the master system table, CS_SYSTEMS_ALL_B, referenced multiple times. It is deeply integrated with the TCA schema to audit party and site information, with key dependencies on:

• HZ_CUST_ACCOUNTS (for customer account changes)
• HZ_PARTIES (for contact person changes)
• HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES (for site use changes)

This network of relationships ensures that the audit trail maintains referential integrity and can be fully decoded by joining to these master data tables.