Search Results hz_orig_systems_tl




Overview
Explore in AI Chat

Overview

HZ_ORIG_SYSTEMS_TL is the translation (TL) table that provides multilingual support for source system information within the Oracle E-Business Suite Receivables (AR) module. In the Oracle Trading Community Architecture (TCA) data model, source systems identify the external or legacy systems from which party, account, and contact records originate, enabling data lineage tracking and origin-based filtering across customer and prospect data. The base table, HZ_ORIG_SYSTEMS_B, stores language-independent attributes, while HZ_ORIG_SYSTEMS_TL holds the language-dependent descriptive text — most notably the translated source system name and description — keyed by ORIG_SYSTEM_ID and LANGUAGE, with SOURCE_LANG recording the language in which the row was originally entered.

Under a heuristic Data Vault classification mined from its foreign key structure, this object is assessed as standalone, meaning it does not participate in a parent-child hub or link relationship through documented foreign keys. From a dimensional modeling perspective, it is best treated as a reference or lookup satellite attached to the HZ_ORIG_SYSTEMS_B hub, since its rows describe and translate the attributes of the underlying source system entity rather than representing an independent business concept.

Key Information Stored

The documented physical schema in ETRM 12.2.2 contains 11 columns. The surrogate primary key is defined by the HZ_ORIG_SYSTEMS_PK constraint on the composite of ORIG_SYSTEM_ID and SOURCE_LANG. A separate unique index, HZ_ORIG_SYSTEMS_TL_U1 on ORIG_SYSTEM_ID, LANGUAGE, and ZD_EDITION_NAME, serves as the business-key candidate and enforces uniqueness of the translated row per language and edition.

  • ORIG_SYSTEM_ID — Surrogate identifier of the parent source system; part of the primary key and a join back to HZ_ORIG_SYSTEMS_B.
  • ORIG_SYSTEM_NAME — The language-specific display name of the source system, the primary descriptive attribute exposed in list of values and lookups.
  • DESCRIPTION — Translated free-text description providing additional context about the source system.
  • LANGUAGE — The language of the translated row; a component of the unique business-key index.
  • SOURCE_LANG — The language in which the record was originally entered; part of the composite primary key and used by the translation framework to detect the base-language row.
  • ZD_EDITION_NAME — Editioning column supporting Oracle EBS online patching and edition-based redefinition; participates in the unique index.
  • CREATED_BY, CREATION_DATE — Standard audit columns recording who created the translation row and when.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns capturing the most recent update context.

Common Use Cases and Queries

The primary use case is retrieving a user-facing source system name in a specific language while filtering out rows in other languages and the base SOURCE_LANG row where appropriate. A typical query joins the translation table to its base table:

  • SELECT b.orig_system_id, t.orig_system_name, t.description FROM hz_orig_systems_b b, hz_orig_systems_tl t WHERE b.orig_system_id = t.orig_system_id AND t.language = USERENV('LANG');
  • Filtering origin records by source system — for example, isolating all parties imported from a legacy CRM by resolving the ORIG_SYSTEM_NAME through this table.
  • Reporting data lineage and onboarding metrics, grouping customer records by their originating system for migration and stewardship dashboards.
  • Data quality audits that detect missing translations, where a base row in HZ_ORIG_SYSTEMS_B has no matching TL row for a required LANGUAGE, which surfaces untranslated source system names to end users.

Because the table is largely static reference data, queries are typically low-volume lookups rather than high-frequency transactional access.

Related Objects

  • HZ_ORIG_SYSTEMS_B — The base table holding language-independent columns; joined on ORIG_SYSTEM_ID.
  • HZ_ORIG_SYSTEMS_VL — The translated view that unions the base and TL tables and is the recommended query interface.
  • HZ_PARTIES and HZ_PARTY_SITES — Party entities that reference ORIG_SYSTEM and ORIG_SYSTEM_REFERENCE values resolved through this table.
  • HZ_CUST_ACCOUNTS — Customer accounts carrying origination references whose display names derive from this translation table.
  • HZ_ORIG_SYSTEMS_TL_U1 — Unique index enforcing the ORIG_SYSTEM_ID, LANGUAGE, ZD_EDITION_NAME business key.
  • HZ_ORIG_SYSTEMS_PK — Primary key constraint on ORIG_SYSTEM_ID and SOURCE_LANG.

Application programming interfaces and concurrent programs that create source systems, such as the TCA party import routines, populate the base and translation tables together to maintain consistent multilingual reference data.