Results for “new_customer_id”

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

Overview

CSI_SYSTEMS_H is the history (audit) table for system records in the Oracle E-Business Suite Install Base module (CSI). While CSI_SYSTEMS_B stores the current state of each system, CSI_SYSTEMS_H captures the transactional trail of changes applied to those systems over time. Every insert or update of significance against a system record generates a corresponding history row in this table, preserving both the pre-change ("OLD_") and post-change ("NEW_") values for the affected attributes. This design allows Install Base users and developers to reconstruct the lifecycle of a system — including customer reassignment, site changes, contact updates, status/date shifts, and descriptive attribute edits — without relying on custom auditing.

Under the heuristic Data Vault classification derived from its foreign key structure, CSI_SYSTEMS_H is best modeled as a link table. It connects a system (via SYSTEM_ID to CSI_SYSTEMS_B) to the transaction that caused the change (via TRANSACTION_ID to CSI_TRANSACTIONS), effectively representing the relationship and event context between a system and the change event that acted upon it. The presence of paired OLD_/NEW_ columns reinforces this event-centric, association-oriented role rather than a pure hub or satellite.

Key Information Stored

The table contains 82 documented columns in the 12.2.2 schema. The most consequential are summarized below.

• SYSTEM_HISTORY_ID — Surrogate primary key, enforced by CSI_SYSTEMS_H_PK and additionally backed by unique index CSI_SYSTEMS_H_U01. It uniquely identifies each history event.
• SYSTEM_ID — Foreign key to CSI_SYSTEMS_B; identifies the system whose change is being recorded.
• TRANSACTION_ID — Foreign key to CSI_TRANSACTIONS; ties the history row to the originating Install Base transaction.
• OLD_CUSTOMER_ID / NEW_CUSTOMER_ID — Capture ownership transfer before and after the change.
• OLD_SYSTEM_NUMBER / NEW_SYSTEM_NUMBER and OLD_SYSTEM_TYPE_CODE / NEW_SYSTEM_TYPE_CODE — Record renumbering and system type transitions.
• OLD_PARENT_SYSTEM_ID / NEW_PARENT_SYSTEM_ID — Track hierarchy (parent-child) reassignment.
• OLD_/NEW_SHIP_TO_SITE_USE_ID, INSTALL_SITE_USE_ID, BILL_TO_SITE_USE_ID — Preserve site usage changes.
• OLD_/NEW_START_DATE_ACTIVE and END_DATE_ACTIVE — Document active period adjustments.
• OLD_/NEW_NAME and DESCRIPTION — Capture descriptive edits.
• FULL_DUMP_FLAG — Indicates whether the row holds a complete snapshot versus a delta.
• OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrency control.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, scoping visibility by security group.
• AUDIT columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

The OLD_/NEW_ATTRIBUTE1–15 pairs provide the DFF (descriptive flexfield) change history.

Common Use Cases and Queries

Typical scenarios include auditing system ownership changes, reconstructing the state of a system at a point in time, and reporting on transaction-driven modifications for compliance.

• List all history events for a specific system, newest first:

SELECT system_history_id, transaction_id, old_customer_id, new_customer_id,
  last_update_date
FROM csi.csi_systems_h
WHERE system_id = :p_system_id
ORDER BY last_update_date DESC;

• Identify systems reassigned to a new customer within a date range:

SELECT system_id, old_customer_id, new_customer_id, last_update_date
FROM csi.csi_systems_h
WHERE old_customer_id <> new_customer_id
  AND last_update_date BETWEEN :from_date AND :to_date;

• Correlate history rows to their originating transactions by joining CSI_TRANSACTIONS on TRANSACTION_ID.
• Filter by SECURITY_GROUP_ID to respect multi-org/security-group access in custom reports.

Related Objects

• CSI_SYSTEMS_B — Current-state system table; joined via CSI_SYSTEMS_H.SYSTEM_ID = CSI_SYSTEMS_B.SYSTEM_ID (parent FK).
• CSI_TRANSACTIONS — Transaction header providing the event context; joined via CSI_SYSTEMS_H.TRANSACTION_ID = CSI_TRANSACTIONS.TRANSACTION_ID.
• FND_SECURITY_GROUPS — Security scoping; joined via CSI_SYSTEMS_H.SECURITY_GROUP_ID.
• CSI_SYSTEMS_H_PK — Primary key constraint on SYSTEM_HISTORY_ID.
• CSI_SYSTEMS_H_U01 — Unique index on SYSTEM_HISTORY_ID.
• Install Base APIs — Public CSI PL/SQL APIs (e.g., system creation/update) write to both CSI_SYSTEMS_B and CSI_SYSTEMS_H to keep current and history consistent.