Search Results csi_ii_relationships_h




Overview
Explore in AI Chat

Overview

The table CSI_II_RELATIONSHIPS_H is a core data object within the Oracle E-Business Suite (EBS) Install Base (CSI) module, specifically for versions 12.1.1 and 12.2.2. It functions as the historical audit table for instance-to-instance relationships. In Install Base, an "instance" represents a tangible or intangible item tracked through its lifecycle, such as a piece of equipment or a serialized component. Relationships define how these instances are connected (e.g., parent-child, component-of, connected-to). This table records a historical entry for every change made to a relationship, providing a complete audit trail for compliance, troubleshooting, and lifecycle analysis. Its role is critical for maintaining data integrity and enabling historical reporting on asset configurations over time.

Key Information Stored

While the provided metadata does not list all columns, the structure is defined by its primary and foreign keys, which indicate the essential data points stored. The central column is RELATIONSHIP_HISTORY_ID, the unique primary key for each historical record. The table stores the foreign key RELATIONSHIP_ID, which links back to the current definition of the relationship in the CSI_II_RELATIONSHIPS table. It also stores TRANSACTION_ID, linking to the CSI_TRANSACTIONS table that records the details of the Install Base transaction (e.g., date, type, who performed it) that caused the relationship change. Each row effectively captures a snapshot of the relationship's attributes (like start date, end date, relationship type) at the moment a transaction modified it, allowing the system to reconstruct any past configuration state.

Common Use Cases and Queries

The primary use case is auditing and historical analysis of asset structures. For example, to trace all changes made to a specific parent-child relationship, one would query this history table filtered by the RELATIONSHIP_ID. Reporting on the complete configuration history of a top-level asset requires joining through this table to see all historical component relationships. A common SQL pattern involves joining to CSI_II_RELATIONSHIPS for the relationship type details and to CSI_TRANSACTIONS for the transaction timestamp and responsible party.

SELECT h.relationship_history_id,
       r.relationship_type_id,
       t.transaction_date,
       t.transaction_type_id
FROM   csi_ii_relationships_h h,
       csi_ii_relationships r,
       csi_transactions t
WHERE  h.relationship_id = r.relationship_id
AND    h.transaction_id = t.transaction_id
AND    r.subject_id = :p_instance_id; -- To find history for a specific instance

Related Objects

The table has defined dependencies on two key Install Base tables, as documented by its foreign keys:

  • CSI_II_RELATIONSHIPS: This is the current, active relationship table. The column CSI_II_RELATIONSHIPS_H.RELATIONSHIP_ID references the primary key in CSI_II_RELATIONSHIPS. This link ties each historical record to the specific relationship definition it audits.
  • CSI_TRANSACTIONS: This is the master transaction table for the Install Base module. The column CSI_II_RELATIONSHIPS_H.TRANSACTION_ID references CSI_TRANSACTIONS. This link provides the context for the change—the who, when, and what type of business action (e.g., shipment, repair, upgrade) created the historical entry.

These relationships are fundamental, ensuring that every piece of history is anchored to both a defined business event (transaction) and a specific data entity (relationship).