Search Results hz_orig_sys_mapping




Overview
Explore in AI Chat

Overview

The HZ_ORIG_SYS_MAPPING table, owned by the AR schema, stores the mapping between source systems and the owner table names that participate in Oracle E-Business Suite's Trading Community Architecture (TCA) model. It is a foundational reference object in the Receivables product family and is used throughout TCA-driven modules to identify which originating system and which underlying entity table a given party, customer, or relationship record derives from. The table supports the multi-source-system design of TCA, in which records can be created and maintained across heterogeneous feeder systems while retaining their original system-of-record identity.

From a data modeling perspective, the heuristic Data Vault classification for this object is hub-leaning. The composite primary key (ORIG_SYSTEM, OWNER_TABLE_NAME) identifies a unique business combination rather than a transaction or descriptive attribute set, which is consistent with a hub-style reference entity. Analysts building a Data Vault layer should consider treating this as a hub over the (ORIG_SYSTEM, OWNER_TABLE_NAME) business key, with attributes such as STATUS and MULTIPLE_FLAG modeled as satellite or descriptive columns.

Key Information Stored

The table exposes 35 documented columns, dominated by a large set of DFF-style ATTRIBUTE1 through ATTRIBUTE20 columns. The most significant columns for functional understanding are:

  • ORIG_SYSTEM — The identifier of the source system that owns or originated the record; part of the primary key.
  • OWNER_TABLE_NAME — The name of the TCA owner table (for example, party or customer registry tables) that the source system is associated with; part of the primary key.
  • STATUS — Indicates the active or inactive state of the mapping entry.
  • MULTIPLE_FLAG — Flag controlling whether multiple mappings are permitted for the combination.
  • MULTI_OSR_FLAG — Additional flag governing multi-owner-source-reference behavior.
  • APPLICATION_ID — Identifies the EBS application context of the mapping.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.
  • OBJECT_VERSION_NUMBER — Concurrency control column used by the framework.
  • CREATED_BY_MODULE — Identifies the module that created the row.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 to ATTRIBUTE20 — Descriptive flexfield columns for extension data.
  • ZD_EDITION_NAME — Editioning column supporting EBR in 12.2.x.

The surrogate/business key structure is defined by the unique index HZ_ORIG_SYS_MAPPING_U1 (ORIG_SYSTEM, OWNER_TABLE_NAME, ZD_EDITION_NAME), which should be treated as the business-key candidate. The primary key constraint, HZ_ORIG_SYS_MAPPING_PK, is defined on (ORIG_SYSTEM, OWNER_TABLE_NAME).

Common Use Cases and Queries

Typical scenarios include validating that an inbound interface's origin system and target table are legitimately registered before loading TCA records, and resolving the owner table that a given source system feeds. A frequent query pattern joins this table to HZ_ORIG_SYS_REFERENCES to enumerate the valid source-system and owner-table combinations:

  • Look up active mappings for a given source system: SELECT owner_table_name FROM hz_orig_sys_mapping WHERE orig_system = :p_orig_system AND status = 'A';
  • Verify a mapping exists before insert: query on both key columns.
  • Reporting on enabled source systems and their target tables for TCA integration governance dashboards.
  • Cross-referencing inbound staging data against the mapping to reject unmapped origin systems during data conversion.

Related Objects

The principal dependent object is HZ_ORIG_SYS_REFERENCES, which references this table through the columns ORIG_SYSTEM and OWNER_TABLE_NAME. Additional objects that logically depend on this mapping include the TCA party and customer registry tables referenced by OWNER_TABLE_NAME, and the various source-system interfaces that query this table during load. Because the FK appears on HZ_ORIG_SYS_REFERENCES.ORIG_SYSTEM and HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_NAME, joins between the two tables should always be constructed on both columns to respect the composite key.