Search Results hz_orig_system_references

Overview

The HZ_ORIG_SYSTEM_REFERENCES table is a core component of the Oracle Trading Community Architecture (TCA) data model, residing in the AR (Receivables) schema within Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to maintain the mapping between source system references and the owner tables that consume them. In practical terms, this table enables Oracle EBS to record and track the identifiers used by external or legacy systems when those identifiers are associated with TCA entities such as parties. By preserving this cross-reference information, the application supports data migration, integration, and reconciliation scenarios where records originating outside the standard Oracle EBS applications must be linked to native TCA records.

From a heuristic Data Vault modeling perspective, the mined foreign key structure suggests HZ_ORIG_SYSTEM_REFERENCES is best classified as a link entity. It connects an originating system (via ORIG_SYSTEM, referencing HZ_ORIG_SYS_MAPPING) to a TCA owner record (via PARTY_ID, referencing HZ_PARTIES), and it carries descriptive attributes such as status, reason codes, and active date ranges. The object is marked VALID and is owned by the AR schema, with 44 documented columns in the ETRM 12.2.2 physical schema.

Key Information Stored

The table is keyed by the surrogate primary key ORIG_SYSTEM_REF_ID, which is enforced through the HZ_ORIG_SYS_REFERENCES_PK constraint and also represented as the unique index HZ_ORIG_SYS_REFERENCES_U1. This surrogate identifier is the primary business-key candidate for uniquely resolving each reference record. The most significant columns include:

• ORIG_SYSTEM_REF_ID — Surrogate primary key uniquely identifying each source system reference record.
• ORIG_SYSTEM — Foreign key to HZ_ORIG_SYS_MAPPING; identifies the source system from which the reference originates.
• ORIG_SYSTEM_REFERENCE — The actual reference value used by the source system.
• OLD_ORIG_SYSTEM_REFERENCE — Retains a prior reference value, supporting change tracking and historical reconciliation.
• OWNER_TABLE_NAME — The name of the owner table to which the reference maps, forming part of the relationship to HZ_ORIG_SYS_MAPPING.
• OWNER_TABLE_ID — The identifier of the record within the referenced owner table.
• PARTY_ID — Foreign key to HZ_PARTIES; links the reference to the TCA party record.
• STATUS — Indicates the current state of the reference (for example, active or inactive).
• REASON_CODE — Captures the reason associated with a status change.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Define the effective date range during which the reference is considered valid.
• OBJECT_VERSION_NUMBER — Supports optimistic locking for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard audit columns.
• CREATED_BY_MODULE, APPLICATION_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrency and program-execution context columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — Descriptive flexfield (DFF) storage for extensible reference attributes.

Common Use Cases and Queries

Typical use cases include resolving a legacy or external system identifier back to a TCA party, auditing the history of system references, and validating mappings during data migration or integration.

• Resolve a party from an external reference: query HZ_ORIG_SYSTEM_REFERENCES joined to HZ_PARTIES on PARTY_ID, filtering by ORIG_SYSTEM and ORIG_SYSTEM_REFERENCE.
• List all references for a party: SELECT orig_system, orig_system_reference, status FROM hz_orig_sys_references WHERE party_id = :p_party_id;
• Validate mappings to the source system configuration: join to HZ_ORIG_SYS_MAPPING on ORIG_SYSTEM and OWNER_TABLE_NAME to confirm the reference is registered against a known owner table.
• Audit active references: filter on START_DATE_ACTIVE and END_DATE_ACTIVE to identify references effective as of a given date, and use STATUS and REASON_CODE for exception reporting.
• Integration reconciliation: compare ORIG_SYSTEM_REFERENCE against OLD_ORIG_SYSTEM_REFERENCE to detect reference changes propagated from upstream systems.

Related Objects

The FK and PK relationship data identify the most significant dependent and referenced objects:

• HZ_PARTIES — Referenced by HZ_ORIG_SYS_REFERENCES.PARTY_ID; the primary TCA entity to which references are tied.
• HZ_ORIG_SYS_MAPPING — Referenced via ORIG_SYSTEM and OWNER_TABLE_NAME; defines the source system and owner table mapping.
• HZ_ORIG_SYS_REFERENCES_PK / HZ_ORIG_SYS_REFERENCES_U1 — Primary key constraint and unique index on ORIG_SYSTEM_REF_ID.
• TCA PL/SQL APIs — The TCA party/reference APIs (for example, HZ_PARTY_V2PUB and related reference-handling routines) read and write this table when registering external system references.
• AR schema objects — Other TCA tables in the AR schema that depend on party and original system reference resolution.