Search Results hr_all_organization_unts_tl_pk




Overview
Explore in AI Chat

Overview

The HR_ALL_ORGANIZATION_UNITS_TL table is a critical multilingual support (MLS) table within the Oracle E-Business Suite Human Resources (HRMS) module. It stores translated names for business groups and all organization units defined within a business group, enabling the global deployment of EBS by supporting multiple languages. This table works in tandem with its base table, HR_ALL_ORGANIZATION_UNITS, which holds language-independent organization data. The primary role of this table is to provide the correct, user-friendly organization name based on the session language of the application user, a core function for international implementations of Oracle EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to manage translations efficiently. Its key columns include ORGANIZATION_ID, a foreign key to HR_ALL_ORGANIZATION_UNITS that identifies the specific organization; LANGUAGE, which stores the ISO code for the language of the translated record; and SOURCE_LANG, which indicates the original language in which the data was entered. The most business-critical column is NAME (VARCHAR2(240)), which holds the translated name of the organization. The table also contains standard Oracle "Who" columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) for auditing. Data integrity is enforced by the primary key constraint HR_ALL_ORGANIZATION_UNTS_TL_PK, which uniquely identifies a record by the combination of ORGANIZATION_ID and LANGUAGE.

Common Use Cases and Queries

A primary use case is retrieving the organization hierarchy with names in the user's preferred language for reports, lists of values (LOVs), and application forms. Developers often join this table with HR_ALL_ORGANIZATION_UNITS to get a complete organizational picture. A common reporting query pattern is:

  • SELECT hou.NAME, hout.NAME, hou.location_id
  • FROM hr_all_organization_units hou,
  • hr_all_organization_units_tl hout
  • WHERE hou.organization_id = hout.organization_id
  • AND hout.language = USERENV('LANG')
  • AND hou.business_group_id = &business_group_id;

Another critical use is during data migration or interface development, where this table must be populated with translated names for each supported language after the base organization record is created in HR_ALL_ORGANIZATION_UNITS. The non-unique index on LANGUAGE and NAME (HR_ALL_ORGANIZATION_UNTS_TL_N2) supports efficient searches for organization names within a specific language.

Related Objects

This table is central to the HRMS organization model. Its primary relationship is with the base table HR_ALL_ORGANIZATION_UNITS, via the ORGANIZATION_ID foreign key. As noted in the dependencies, it is referenced by other key objects, including the materialized view LNS_LOAN_DTLS_ALL_MV in the Loans module and the table HR_ALL_ORGANIZATION_UNITS_TL itself, indicating potential recursive relationships or triggers. For comprehensive organization data, it is often used alongside HR_ORGANIZATION_INFORMATION, which stores additional classifications and information types. The APPS synonym HR_ALL_ORGANIZATION_UNITS_TL provides global access to the table within the application database schema.