Results for “csi_i_org_assignments_h”

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

Overview

CSI_I_ORG_ASSIGNMENTS_H is the history (audit) table for Instance Organization Associations within the Oracle E-Business Suite Install Base (CSI) module. It resides in the CSI schema and stores the before-and-after image of every change applied to its transactional counterpart, CSI_I_ORG_ASSIGNMENTS. Each row captures a single modification event: the old relationship values, the new relationship values, and the transaction that produced the change. This design supports full traceability of how an installed base instance was associated with, or reassigned between, operating units over time.

Under the heuristic Data Vault classification derived from the foreign key structure, this table is best modeled as a link. It does not describe a business entity itself; rather, it records associations between an instance-operating unit assignment, its parent transaction, and the security context in which the change occurred.

Key Information Stored

The table contains 52 documented columns. The most significant are:

• INSTANCE_OU_HISTORY_ID — surrogate primary key, defined by index CSI_I_ORG_ASSIGNMENTS_H_PK.
• INSTANCE_OU_ID — the parent assignment record in CSI_I_ORG_ASSIGNMENTS being changed. Combined with TRANSACTION_ID, it forms business-key candidate CSI_I_ORG_ASSIGNMENTS_H_U02.
• TRANSACTION_ID — the CSI_TRANSACTIONS row that drove the modification.
• OLD_OPERATING_UNIT_ID / NEW_OPERATING_UNIT_ID — previous and updated operating unit assignments.
• OLD_RELATIONSHIP_TYPE_CODE / NEW_RELATIONSHIP_TYPE_CODE — prior and revised relationship classification.
• OLD_ACTIVE_START_DATE, NEW_ACTIVE_START_DATE, OLD_ACTIVE_END_DATE, NEW_ACTIVE_END_DATE — the effective-dating envelope before and after the change.
• OLD_CONTEXT / NEW_CONTEXT and OLD_ATTRIBUTE1–15 / NEW_ATTRIBUTE1–15 — descriptive and DFF-style flexfield values.
• FULL_DUMP_FLAG, MIGRATED_FLAG, OBJECT_VERSION_NUMBER — controls indicating snapshot mode, migration origin, and optimistic locking.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level access.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit trail stamping.

The unique index CSI_I_ORG_ASSIGNMENTS_H_U01 on INSTANCE_OU_HISTORY_ID is functionally identical to the primary key.

Common Use Cases and Queries

Typical reporting requirements include reconstructing the operating-unit history of an instance, auditing who moved a record between units, and identifying changes attributable to a specific transaction.

• Reconstruct assignment history for one instance:

  SELECT INSTANCE_OU_HISTORY_ID, OLD_OPERATING_UNIT_ID, NEW_OPERATING_UNIT_ID,
         OLD_RELATIONSHIP_TYPE_CODE, NEW_RELATIONSHIP_TYPE_CODE, CREATION_DATE
  FROM   CSI.CSI_I_ORG_ASSIGNMENTS_H
  WHERE  INSTANCE_OU_ID = :instance_ou_id
  ORDER  BY CREATION_DATE;

• Trace all changes produced by a batch or transaction: filter on TRANSACTION_ID joined to CSI_TRANSACTIONS.
• Identify reassignments across operating units: compare OLD_OPERATING_UNIT_ID with NEW_OPERATING_UNIT_ID where the values differ.
• Effective-date analysis: compare OLD_ACTIVE_END_DATE and NEW_ACTIVE_END_DATE to detect back-dated or future-dated corrections.
• Data migration verification: use MIGRATED_FLAG and FULL_DUMP_FLAG to isolate records created by bulk loads versus online maintenance.

Related Objects

• CSI_I_ORG_ASSIGNMENTS — joined on INSTANCE_OU_ID; the current-state assignment record.
• CSI_TRANSACTIONS — joined on TRANSACTION_ID; the originating change event.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; access control context.
• CSI_ITEM_INSTANCES — reached through the parent assignment for instance-level reporting.
• CSI_OPERATING_UNITS / HR_OPERATING_UNITS — resolved from OLD_/NEW_OPERATING_UNIT_ID for descriptive names.
• FND_USER — resolved from CREATED_BY / LAST_UPDATED_BY for user attribution.