Search Results hr_all_organization_units_tl




Overview
Explore in AI Chat

Overview

HR.HR_ALL_ORGANIZATION_UNITS_TL is the translation (TL) table for organization units in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It stores the language-dependent name definitions for records held in the base table HR_ALL_ORGANIZATION_UNITS, which is the primary repository for the internal organization hierarchy, business groups, inventory organizations, legal entities, and operating units used throughout the PER and related application modules. For every organization unit row, one translation record exists per installed language, enabling multi-language display of organization names without duplicating the base organization definition.

The ETRM metadata classifies this object, by heuristic Data Vault mining of its foreign key structure, as standalone. In Data Vault modeling terms this suggests the table behaves as a satellite-style attribute relation rather than a hub or link: it carries descriptive, language-specific attributes keyed to a parent entity. Modelers should treat it as a dependent descriptive store attached to the organization unit hub, not as an independent business key source.

Key Information Stored

The table is documented with ten physical columns. The most significant are:

  • ORGANIZATION_ID — Together with LANGUAGE, forms the translation primary key. This is the identifier linking each translated row back to its parent organization unit in HR_ALL_ORGANIZATION_UNITS.
  • LANGUAGE — The language code for this translated row, the second component of the translation key.
  • SOURCE_LANG — The language of the source (base) record, used by the translation framework to indicate the origin of the seeded text.
  • NAME — The organization unit name rendered in the target language. This is the principal business-facing attribute of the table.
  • ZD_EDITION_NAME — The edition identifier used by the E-Business Suite editioning and online patching architecture. It appears as the third column in the documented unique index and is essential in 12.2.x environments where multiple editions may coexist.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide the standard WHO-column audit trail for the row.

The documented unique index HR_ALL_ORGANIZATION_UNTS_TL_PK defines the business-key candidate. In the 12.2.2 schema it is documented as (ORGANIZATION_ID, LANGUAGE, ZD_EDITION_NAME), while earlier listings present the primary key as (ORGANIZATION_ID, LANGUAGE). This evolution reflects the introduction of editioning in the 12.2 architecture. The ORGANIZATION_ID and LANGUAGE pair remains the fundamental surrogate-to-business linkage; the TL row itself does not generate a new surrogate identifier.

Common Use Cases and Queries

Translation tables are queried whenever organization names must be presented in a specific language, or when the base name and translated names must be reconciled. A typical pattern joins the base table to the TL table on ORGANIZATION_ID and constrains LANGUAGE:

  • Multilingual reporting — join HR_ALL_ORGANIZATION_UNITS to HR_ALL_ORGANIZATION_UNITS_TL and filter LANGUAGE = 'US' (or the session language) to list organization names.
  • Translation completeness audit — compare distinct LANGUAGE values in the TL table against the installed languages to detect organizations missing a translation.
  • Edition-aware extraction — in 12.2.x, include ZD_EDITION_NAME in the predicate to retrieve rows valid for the active edition, avoiding duplicate results during online patching.
  • Name change tracking — use the audit columns to report organization names modified after a given LAST_UPDATE_DATE.

Queries should always constrain LANGUAGE; omitting it returns one row per installed language and produces duplicate organization names.

Related Objects

  • HR.HR_ALL_ORGANIZATION_UNITS — the base (non-translated) organization unit table; join on ORGANIZATION_ID. This is the parent entity for every TL row.
  • HR_ALL_ORGANIZATION_UNITS_F and related _F/_TL pairs in other modules follow the same translation pattern.
  • HR_ORGANIZATION_INFORMATION — holds additional organization attributes and is commonly joined alongside for classification data.
  • HR_ORGANIZATION_UNITS views and HR_ORG_UNIT_API / organization unit PL/SQL APIs maintain the base and translation rows together.
  • FND_LANGUAGES — provides the valid values for LANGUAGE and SOURCE_LANG.
  • PER_ALL_PEOPLE_F and HR_ALL_POSITIONS_F — reference organization units for assignment and position reporting, indirectly depending on this table for organization name display.