Search Results hr_item_properties_tl




Overview
Explore in AI Chat

Overview

HR_ITEM_PROPERTIES_TL is the translatable (TL) child table in the Oracle E-Business Suite HR schema that stores language-specific display text for items rendered by Oracle's configurable forms framework within the PER (Human Resources) product. While the companion table HR_ITEM_PROPERTIES_B holds the language-independent definition of each item property, HR_ITEM_PROPERTIES_TL holds the translated prompt, label, and hint text that end users actually see on the form. The object is classified as VALID in ETRM for both 12.1.1 and 12.2.2.

Following Oracle EBS multi-language design conventions, a TL table stores one row per base record per installed language, joined back to its base (B) table by the surrogate ITEM_PROPERTY_ID. Based on the foreign key structure (a single FK from HR_ITEM_PROPERTIES_TL.ITEM_PROPERTY_ID to HR_ITEM_PROPERTIES_B.ITEM_PROPERTY_ID), the heuristic Data Vault classification for this object is satellite-leaning. That is a modeling suggestion: the table behaves as a language-dependent descriptive satellite hanging off the HR_ITEM_PROPERTIES_B hub, not as an independent hub or a link between entities.

Key Information Stored

  • ITEM_PROPERTY_ID — Surrogate identifier for the parent item property. Together with LANGUAGE it forms the primary key HR_ITEM_PROPERTIES_TL_PK, and it is the FK joining to HR_ITEM_PROPERTIES_B.
  • LANGUAGE — The NLS language code for the translated content of this row.
  • SOURCE_LANG — Indicates the language in which the row was originally authored, used by the translation framework to track untranslated or copied content.
  • INFORMATION_PROMPT — Extended informational prompt text presented to the user for the item property.
  • LABEL — The short display label for the form item.
  • PROMPT_TEXT — The primary field prompt string shown adjacent to the item on the configurable form.
  • TOOLTIP_TEXT — Hover/help text displayed for the form item.
  • DEFAULT_VALUE — The language-specific default value surfaced when the form initialises.
  • ZD_EDITION_NAME — Editioning column introduced in 12.2.x for Online Patching support; it also appears in the unique index HR_ITEMS_PROPERTIES_TL_PK (ITEM_PROPERTY_ID, LANGUAGE, ZD_EDITION_NAME).
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE supply WHO-column auditing on translation edits.

The documented unique index HR_ITEMS_PROPERTIES_TL_PK (ITEM_PROPERTY_ID, LANGUAGE, ZD_EDITION_NAME) is the business-key candidate, superseding the two-column primary key by adding the edition dimension. The surrogate key remains ITEM_PROPERTY_ID in combination with LANGUAGE.

Common Use Cases and Queries

Typical usage centres on extracting the rendered form labels for a given language, or auditing where a prompt has been translated versus left at source language. A common join pattern resolves the base definition to its translation:

SELECT b.item_property_id, t.language, t.label, t.prompt_text, t.tooltip_text
FROM hr.hr_item_properties_b b, hr.hr_item_properties_tl t
WHERE b.item_property_id = t.item_property_id
AND t.language = USERENV('LANG');

Reporting scenarios include: generating a translation-completeness report (rows where SOURCE_LANG equals LANGUAGE but the intended target language is missing); producing a label dictionary for user documentation; and auditing recent label changes via LAST_UPDATE_DATE and LAST_UPDATED_BY. In 12.2.x, queries should account for ZD_EDITION_NAME so that only the current edition's rows are returned.

Related Objects

  • HR_ITEM_PROPERTIES_B — Base table; joined via ITEM_PROPERTY_ID. Holds the language-independent property definition.
  • HR_ITEM_PROPERTIES_TL — Itself, via the TL/B pairing pattern used throughout Oracle EBS.
  • HR_ITEM_PROPERTIES_VL — View (where present) that unions B and TL to present translated properties in a single selectable object.
  • FND_LANGUAGES — Reference table validating the LANGUAGE and SOURCE_LANG codes.
  • FND_FORM / HR form configuration tables — Consume the label and prompt values when rendering configurable forms.
  • FND_TERRITORIES / FND_NLS conventions — Define the language conventions applied to SOURCE_LANG.

Integrations and reports should join through ITEM_PROPERTY_ID and always filter on LANGUAGE to avoid Cartesian expansion across installed languages.