Search Results hz_orig_sys_mapping_u1

Overview

The AR.HZ_ORIG_SYS_MAPPING table is a TCA (Trading Community Architecture) seed table that governs which source systems are permitted to insert data into specific owner tables within the Oracle E-Business Suite Receivables and Trading Community schemas. Because TCA party, account, and site data can originate from legacy applications, external CRM systems, or third-party integrations, this table acts as an authorization and mapping registry. Each row associates an ORIG_SYSTEM identifier with an OWNER_TABLE_NAME, defining whether that source system is active and whether a single source-system reference can resolve to multiple owner records.

From a Data Vault modeling perspective, the metadata heuristic classifies this object as hub-leaning. In practice, ORIG_SYSTEM behaves as a durable business key, and the combination of ORIG_SYSTEM with OWNER_TABLE_NAME forms a natural composite identifier suitable for a hub or link construct, with descriptive attributes such as STATUS and MULTIPLE_FLAG forming a satellite. This classification is a modeling suggestion rather than a physical constraint imposed by EBS.

Key Information Stored

The table's primary key is HZ_ORIG_SYS_MAPPING_PK, defined on (ORIG_SYSTEM, OWNER_TABLE_NAME). A unique business-key candidate, HZ_ORIG_SYS_MAPPING_U1, is also documented. In the 12.2.2 physical schema, this unique index expands to (ORIG_SYSTEM, OWNER_TABLE_NAME, ZD_EDITION_NAME), reflecting the editioning column used for online patching.

• ORIG_SYSTEM (VARCHAR2, 30) — the name of the source system registering for access.
• OWNER_TABLE_NAME (VARCHAR2, 30) — the TCA table that the source system may populate.
• STATUS (VARCHAR2) — A for Active (insert permitted) or I for Inactive; defaults to A.
• MULTIPLE_FLAG (VARCHAR2) — Y/N controlling whether one source-system reference may map to multiple owner table IDs. Defaults to N for party-layer tables and HZ_CUST_ACCOUNTS, and Y for HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, and HZ_CUST_ACCOUNT_ROLES.
• MULTI_OSR_FLAG — a related flag documented in the physical schema for multiple original-system-reference handling.
• OBJECT_VERSION_NUMBER — version counter supporting optimistic concurrency control.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard who audit columns.
• CREATED_BY_MODULE, APPLICATION_ID — TCA who-columns identifying the module and application that created the row.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE20 — descriptive flexfield (DFF) columns for customer-defined extensions.
• ZD_EDITION_NAME — editioning column supporting EBS 12.2 online patching.

Common Use Cases and Queries

Administrators and integrators query this table to determine whether a given legacy or external system is authorized to load data into a specific TCA table, and whether multiple-record mappings are permitted. A typical validation query checks active mappings before an inbound interface run:

• SELECT status, multiple_flag FROM ar.hz_orig_sys_mapping WHERE orig_system = :p_source AND owner_table_name = :p_table;
• Listing all active source systems authorized for a target table: SELECT orig_system FROM ar.hz_orig_sys_mapping WHERE owner_table_name = 'HZ_CUST_ACCOUNTS' AND status = 'A';
• Auditing which systems may create multi-record references: SELECT orig_system, owner_table_name FROM ar.hz_orig_sys_mapping WHERE multiple_flag = 'Y';

Reporting use cases include compliance reviews of external data sources, migration planning when retiring a legacy system (setting STATUS='I'), and troubleshooting HZ_ORIG_SYS_REFERENCES errors caused by missing or inactive mappings.

Related Objects

• HZ_ORIG_SYS_REFERENCES — references this table via ORIG_SYSTEM; stores the actual source-system references assigned to party and account entities. Join on ORIG_SYSTEM.
• HZ_CUST_ACCOUNTS — a primary owner table gated by this mapping (default MULTIPLE_FLAG='N').
• HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_CUST_ACCOUNT_ROLES — owner tables where multi-record mappings default to Y.
• HZ_PARTIES and other party-layer tables — owner tables restricted to single-record mappings by default.
• FND_USER and FND_LOGINS — referenced by the who columns for audit reconciliation.