Search Results igi_dos_trx_headers_hist_u1

Overview

IGI.IGI_DOS_TRX_HEADERS_HIST is a historical archive table within the Oracle EBS Treasury (ETRM/IGI) module. It stores prior-state snapshots of dossier transaction headers, preserving the attributes that existed at each point in a transaction's lifecycle. The table is owned by the IGI schema, resides in the APPS_TS_TX_DATA tablespace, and is documented as VALID in the ETRM 12.2.2 design data. It sits alongside its transactional counterpart, IGI_DOS_TRX_HEADERS, and its purpose is to retain an auditable history of dossier transactions as they evolve through status changes, packet assignments, or fund status transitions.

From a dimensional modeling perspective, the table's structure — a descriptive payload keyed by a dedicated surrogate and populated by standard WHO columns — carries a heuristic Data Vault classification of satellite-leaning. This classification is a suggestion: the object behaves like a satellite that records the changing descriptive attributes of a dossier transaction over time, rather than acting as a hub or link. Designers building a vault model should treat IGI_DOS_TRX_HEADERS_HIST as a candidate satellite keyed to the transaction or dossier hub present in the operational schema.

Key Information Stored

The table is anchored by its surrogate primary key, HISTORY_TRX_ID, which is enforced by the unique index IGI_DOS_TRX_HEADERS_HIST_U1 (type NORMAL, uniqueness UNIQUE, tablespace APPS_TS_TX_IDX). This column is the single documented business-key candidate and the principal join path to dependent history tables.

Business identifiers carried on the row include TRX_ID (the transaction identifier), TRX_NUMBER (the human-readable transaction number), DOSSIER_ID, DOSSIER_NAME, and DOSSIER_TRANSACTION_NAME. These columns allow the history record to be tied back to a specific dossier and its transaction without relying solely on the surrogate key.

Lifecycle and classification columns include TRX_STATUS and FUNDS_STATUS, which capture the state of the transaction and of associated funds at snapshot time. PACKET_ID and SOB_ID (Set of Books identifier) provide organizational and functional context. DESCRIPTION holds a short free-text note.

Standard ETRM descriptive flexfield support is present via ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30 (the documented schema lists 49 columns, including the extended attribute range). Audit columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN. Parent context is retained through PARENT_TRX_ID and PARENT_TRX_NUMBER, supporting hierarchical transaction relationships.

Common Use Cases and Queries

The most common use is audit and reconciliation reporting: reconstructing the history of a transaction's status or funds status over time. A typical pattern joins the history table to the current header on TRX_ID to compare the latest snapshot against the live record.

• Status change auditing: SELECT trx_number, trx_status, funds_status, creation_date FROM igi.igi_dos_trx_headers_hist WHERE trx_id = :p_trx_id ORDER BY history_trx_id;
• Dossier-level history rollup by joining on DOSSIER_ID, filtering by DOSSIER_NAME or SOB_ID.
• Destructive-change forensics, pairing history rows with IGI_DOS_TRX_DEST_HIST and IGI_DOS_TRX_SOURCES_HIST on HISTORY_TRX_ID.
• Period-over-period reporting of fund status transitions for treasury reconciliation.
• DFF-driven reporting using ATTRIBUTE_CATEGORY and ATTRIBUTEn segments when custom history attributes are enabled.

Because the table is append-only in nature, queries should generally aggregate or order by HISTORY_TRX_ID or CREATION_DATE to obtain chronological sequences rather than expecting a single current row per transaction.

Related Objects

The table participates in a well-defined set of relationships documented in the ETRM metadata:

• IGI.IGI_DOS_TRX_HEADERS — the operational source of dossier transaction headers; joined on TRX_ID, and the presumed parent hub for this satellite.
• IGI_DOS_DOC_TYPES — referenced via DOSSIER_ID, providing the document type classification.
• IGI_DOS_TRX_DEST_HIST — references this table on HISTORY_TRX_ID, carrying historical destination detail.
• IGI_DOS_TRX_SOURCES_HIST — references this table on HISTORY_TRX_ID, carrying historical source detail.
• FND_USER — the standard WHO reference for CREATED_BY and LAST_UPDATED_BY.
• FND_LOGINS — the standard WHO reference for LAST_UPDATE_LOGIN.

Together these objects form the dossier transaction history cluster, centered on IGI_DOS_TRX_HEADERS_HIST and its unique key IGI_DOS_TRX_HEADERS_HIST_U1.