Search Results hz_timezones_tl




Overview
Explore in AI Chat

Overview

HZ_TIMEZONES_TL is the translation table for time zone definitions within the Oracle E-Business Suite Receivables (AR) schema. In Oracle EBS 12.1.1 and 12.2.2, the _TL suffix denotes a translation-enabled table that stores language-specific (translatable) descriptive attributes for a base entity. Here, the base entity is the time zone, keyed by TIMEZONE_ID, while the translated attributes — primarily NAME and DESCRIPTION — are stored per LANGUAGE. This design allows the same underlying time zone record to carry distinct display names across the languages installed in the EBS instance, supporting global, multi-lingual deployments.

From a modeling perspective, the ETRM metadata heuristic classifies HZ_TIMEZONES_TL as a standalone object, meaning no foreign-key relationships were mined from its structure. Under Data Vault conventions, this object is best modeled as a satellite: its natural business key is the composite (TIMEZONE_ID, LANGUAGE), and it holds descriptive, language-dependent attributes tied to that key. It does not function as a hub or link in the mined relationship structure.

Key Information Stored

The documented physical schema contains eleven columns. The most significant are:

  • TIMEZONE_ID — Surrogate identifier of the base time zone; part of the primary key.
  • LANGUAGE — The language code in which the translated values are expressed; the second component of the primary key.
  • NAME — The localized display name of the time zone.
  • DESCRIPTION — The localized descriptive text for the time zone.
  • TRANSLATED — Indicator of whether the row has been translated from its source language.
  • SOURCE_LANG — The language from which the translation originated, typically the base (e.g., US) language.
  • CREATION_DATE, CREATED_BY — Audit columns recording row creation.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Audit columns recording the most recent modification and the session that performed it.

The surrogate primary key is defined by HZ_TIMEZONES_TL_PK on the composite (TIMEZONE_ID, LANGUAGE). A unique index, HZ_TIMEZONES_TL_U1, mirrors this composite key and serves as the business-key candidate, ensuring exactly one translation exists per time zone per language.

Common Use Cases and Queries

The table is most commonly accessed to render localized time zone labels in application pages, reports, and interfaces, and to validate that a translation exists for a given language before display. A typical lookup filters on the base TIMEZONE_ID and the session LANGUAGE:

  • Join to the base time zone table on TIMEZONE_ID to retrieve NAME and DESCRIPTION for a specific language.
  • Detect untranslated entries by filtering where TRANSLATED is not set to the translated flag.
  • Report translation coverage by counting rows per LANGUAGE or comparing against SOURCE_LANG.

A representative query pattern: SELECT t.timezone_id, t.name, t.description FROM hz_timezones_tl t WHERE t.language = :p_lang ORDER BY t.name;. Because the primary key is composite, lookups should always constrain on both TIMEZONE_ID and LANGUAGE for optimal index usage.

Related Objects

The translation table is logically paired with its base (non-translated) time zone definition, joined by TIMEZONE_ID. The most significant referencing relationships include the base HZ_TIMEZONES(_B) table, from which the translatable attributes originate, and application lookups or views that resolve time zone names for display. Because the mined relationship structure is standalone, no foreign keys were documented; joins should be established on TIMEZONE_ID and LANGUAGE rather than through declared constraints. Downstream consumers include Receivables setup pages, concurrent programs, and reporting views that present localized time zone descriptions.