Results for “csi_ii_relationships_h”

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

Overview

The CSI_II_RELATIONSHIPS_H table, owned by the CSI schema, stores the complete change history of instance-to-instance (II) relationships within the Oracle E-Business Suite Install Base module. In Oracle EBS 12.1.1 and 12.2.2, the Install Base tracks customer assets, configurations, and the relationships between them—such as parent-child, component-to-assembly, or peer associations recorded in CSI_II_RELATIONSHIPS. Whenever a relationship is created, updated, or ended, the prior and new values are captured in this history table, keyed to the transaction that drove the change.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure suggests this object behaves as a link—it records associations between the relationship entity and the transaction/security-group entities. It carries descriptive old/new value pairs that give it a satellite-like quality, but the documented FK topology supports treating it primarily as a link capturing relationship-state transitions over time.

Key Information Stored

The table contains 54 columns. The most significant are grouped below:

• RELATIONSHIP_HISTORY_ID — surrogate primary key (CSI_II_RELATIONSHIPS_H_PK) uniquely identifying each history row.
• RELATIONSHIP_ID — foreign key to CSI_II_RELATIONSHIPS, linking the history row to its parent relationship.
• TRANSACTION_ID — foreign key to CSI_TRANSACTIONS, identifying the transaction that produced the change.
• OLD_SUBJECT_ID / NEW_SUBJECT_ID — the source instance before and after the change.
• OLD_POSITION_REFERENCE / NEW_POSITION_REFERENCE — positional context of the relationship, before and after.
• OLD_ACTIVE_START_DATE / NEW_ACTIVE_START_DATE and OLD_ACTIVE_END_DATE / NEW_ACTIVE_END_DATE — the effective-dating window of the relationship across the change.
• OLD_MANDATORY_FLAG / NEW_MANDATORY_FLAG — whether the relationship was mandatory.
• OLD_CONTEXT / NEW_CONTEXT — the descriptive context of the relationship.
• OLD_ATTRIBUTE1..15 / NEW_ATTRIBUTE1..15 — flexible descriptive attributes captured before and after the change.
• FULL_DUMP_FLAG and MIGRATED_FLAG — indicators for full-dump capture and data migration.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for multi-org data partitioning.
• OBJECT_VERSION_NUMBER — optimistic locking column.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Two unique indexes are documented: CSI_II_RELATIONSHIPS_H_U01 on RELATIONSHIP_HISTORY_ID, and CSI_II_RELATIONSHIPS_H_U02 on (TRANSACTION_ID, RELATIONSHIP_ID), the latter serving as the business-key candidate that guarantees one history row per relationship per transaction.

Common Use Cases and Queries

This table is queried to reconstruct the lifecycle of an Install Base relationship, perform audit and compliance reporting, and diagnose configuration changes attributable to specific transactions.

• Audit trail of a single relationship:

  SELECT relationship_history_id, transaction_id,
         old_subject_id, new_subject_id,
         old_active_end_date, new_active_end_date,
         last_update_date
  FROM   csi.csi_ii_relationships_h
  WHERE  relationship_id = :relationship_id
  ORDER  BY last_update_date;

• Changes produced by a given transaction:

  SELECT r.relationship_id, h.old_context, h.new_context
  FROM   csi.csi_ii_relationships_h h,
         csi.csi_ii_relationships   r
  WHERE  h.relationship_id = r.relationship_id
  AND    h.transaction_id  = :transaction_id;

• Reverse chronological change feed for security-group-scoped reporting, joining FND_SECURITY_GROUPS via SECURITY_GROUP_ID.
• Reconciliation between current state (CSI_II_RELATIONSHIPS) and historical snapshots for data-quality checks.

Related Objects

• CSI_II_RELATIONSHIPS — parent relationship table; join on RELATIONSHIP_ID.
• CSI_TRANSACTIONS — originating transaction; join on TRANSACTION_ID.
• FND_SECURITY_GROUPS — multi-org security partitioning; join on SECURITY_GROUP_ID.
• CSI_ITEM_INSTANCES — instance records referenced via subject IDs (OLD/NEW_SUBJECT_ID).
• CSI_RELATIONSHIP_TYPES — defines the relationship semantics referenced by CSI_II_RELATIONSHIPS.
• CSI_II_RELATIONSHIPS_PUB — public API package used to create and maintain relationships, which writes history rows here.