Search Results hz_orig_sys_references_u1

Overview

The AR.HZ_ORIG_SYS_REFERENCES table is a core component of the Trading Community Architecture (TCA) model in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the cross-reference mapping between source system identifiers and the corresponding TCA owner tables that hold the migrated or integrated records. The source system reference is the identifier assigned by an external or legacy source system, while the owner table name and owner table ID point to the specific TCA record that the reference maps to. The STATUS column indicates whether a mapping is currently valid (A for Active) or has been superseded or retired (I for Inactive). This enables TCA to reconcile records arriving from multiple feeder systems against a single, unified party or account representation.

From a Data Vault modeling perspective, the metadata's heuristic classification is link. This reflects the table's role as an associative construct joining a source system reference to a TCA owner record, rather than serving as a standalone hub of a business entity. Parties, accounts, and locations are hubs; this table captures the relationship between an originating system and a TCA record.

Key Information Stored

The table is keyed by ORIG_SYSTEM_REF_ID, a sequentially generated surrogate number (15) that forms the primary key HZ_ORIG_SYS_REFERENCES_PK. The unique index HZ_ORIG_SYS_REFERENCES_U1 is built on this column alone, confirming it as the documented business-key candidate.

• ORIG_SYSTEM — the source system name (foreign key to HZ_ORIG_SYS_MAPPING), identifying where the record originated.
• ORIG_SYSTEM_REFERENCE — the source system's own identifier for the logical entity.
• OWNER_TABLE_NAME — the TCA table that carries the source system reference column, such as HZ_PARTIES.
• OWNER_TABLE_ID — the unique identifier of the row within the named owner table.
• STATUS — A for Active or I for Inactive; defaults to Active.
• REASON_CODE — the reason a mapping was set to Inactive.
• OLD_ORIG_SYSTEM_REFERENCE — the prior source reference value that was replaced.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range of the mapping.
• PARTY_ID — foreign key to HZ_PARTIES, directly associating the reference with a party.
• OBJECT_VERSION_NUMBER — optimistic locking counter used by the TCA API layer.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• APPLICATION_ID, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program and request context.
• ATTRIBUTE1 through ATTRIBUTE20 plus ATTRIBUTE_CATEGORY — the standard DFF extension block.

Common Use Cases and Queries

Typical uses include reconciling legacy data migrations, tracing a TCA party back to its originating system key, and auditing which mappings have been retired.

• Resolve a source key to its TCA owner record:
  SELECT owner_table_name, owner_table_id, party_id, status FROM hz_orig_sys_references WHERE orig_system = :sys AND orig_system_reference = :ref;
• Find all source references for a given party:
  SELECT orig_system, orig_system_reference FROM hz_orig_sys_references WHERE party_id = :party_id AND status = 'A';
• Audit retired mappings and their replacement values:
  SELECT orig_system, old_orig_system_reference, orig_system_reference, reason_code FROM hz_orig_sys_references WHERE status = 'I';

Reporting often joins this table to HZ_PARTIES for a cross-system party reconciliation report, or to HZ_ORIG_SYS_MAPPING to validate that every reference points to a registered source system.

Related Objects

The most significant related objects, evidenced by documented foreign keys and TCA dependencies, are:

• HZ_ORIG_SYS_MAPPING — joined via ORIG_SYSTEM; registers valid source systems.
• HZ_PARTIES — joined via PARTY_ID; the primary TCA party hub.
• HZ_PARTY_SITES, HZ_LOCATIONS — additional owner tables referenced through OWNER_TABLE_NAME and OWNER_TABLE_ID.
• HZ_CUST_ACCOUNTS — a common owner table for customer account cross-references.
• HZ_ORG_CONTACTS — owner table for contact cross-references.
• FND_USER — joined via CREATED_BY to resolve the creating user.

The TCA party, account, and location APIs maintain this table programmatically; direct DML should generally be avoided in favor of those public interfaces.