Results for “hr_form_items_tl_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.HR_FORM_ITEMS_TL is the translation (TL) table for configurable form items in Oracle E-Business Suite Human Resources. It stores the language-dependent, translated definitions of form items that are declared in the base table HR_FORM_ITEMS_B. Each row represents one form item translated into one language, allowing the user-facing name and description of a configurable form field to be presented in the session language of the user.

The table resides in the APPS_TS_SEED tablespace, reflecting its role as seed/reference data shipped with the application, and it is registered under FND Design Data as PER.HR_FORM_ITEMS_TL with a status of VALID in 12.1.1 and 12.2.2. In a Data Vault-style heuristic classification, the foreign key structure (FORM_ITEM_ID referencing HR_FORM_ITEMS_B) suggests a satellite-leaning object: it carries descriptive, language-specific attributes attached to a business key owned by the base table rather than acting as an independent hub or an associative link.

Key Information Stored

The table combines surrogate and business-key identifiers with translated text attributes and standard audit columns:

• FORM_ITEM_ID — NUMBER(15), the system-generated identifier for the form item, sourced from sequence HR_FORM_CANVASES_S. It is also the foreign key to HR_FORM_ITEMS_B (FORM_ITEM_ID).
• LANGUAGE — VARCHAR2, the language of the translated row, typically the installed language code or the special value US for the source language.
• SOURCE_LANG — VARCHAR2, the language in which the base (source) definition was originally authored; used by the translation framework to determine which rows are translations and which is the base row.
• USER_ITEM_NAME — VARCHAR2(80), the translated display name for the item as it appears to the user.
• DESCRIPTION — VARCHAR2(200), the translated description of the information type represented by the item.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard Who columns that capture the audit trail for each translated row.

The documented primary key is HR_FORM_ITEMS_TL_PK on (FORM_ITEM_ID, LANGUAGE), which uniquely identifies a translation of a given item in a given language. Effective-dating metadata present in the 12.2.2 schema also includes ZD_EDITION_NAME, and the unique index listing for that release shows the business-key candidate as (FORM_ITEM_ID, LANGUAGE, ZD_EDITION_NAME), reflecting Oracle's editioning support in 12.2.x. Additional nonunique indexes, HR_FORM_ITEMS_TL_FK2 on SOURCE_LANG and HR_FORM_ITEMS_TL_FK3 on LANGUAGE, support translation framework lookups.

Common Use Cases and Queries

The table is queried primarily to resolve the display name and description of configurable form items in a given language, and to report on translation completeness across installed languages.

• Retrieving translated item names for a session language:

  SELECT b.form_item_id, tl.user_item_name, tl.description
  FROM   hr.hr_form_items_b  b,
         hr.hr_form_items_tl tl
  WHERE  b.form_item_id = tl.form_item_id
  AND    tl.language     = USERENV('LANG');

• Identifying missing translations by outer-joining TL rows against the base table and filtering on NULL language.
• Comparing the source-language row (LANGUAGE = SOURCE_LANG) with target-language rows to audit divergent translations of USER_ITEM_NAME.
• Seeding or validating custom form items during configuration of configurable forms, ensuring each item has a corresponding translated definition.

Because the table is seed data, queries are generally read-only reporting patterns; direct DML should be performed only through supported configuration and translation utilities.

Related Objects

The most significant dependencies for this object are:

• HR.HR_FORM_ITEMS_B — the base table referenced via FORM_ITEM_ID; the TL table inherits its business identity from this parent.
• HR.HR_FORM_ITEMS_TL_PK — the primary key index on (FORM_ITEM_ID, LANGUAGE), used by the translation framework for direct row resolution.
• HR_FORM_ITEMS_TL_FK2 and HR_FORM_ITEMS_TL_FK3 — the nonunique indexes on SOURCE_LANG and LANGUAGE that support language-based access paths.
• APPS.HR_FORM_ITEMS_TL — the APPS synonym through which form and OAF code typically accesses the table.
• HR_FORM_ITEMS_TL041434_WHO — the Who column audit object that tracks changes to translated form item definitions.

Other configurable-form tables such as HR_FORM_CANVASES and the related translation tables for canvases and form templates share the same design pattern and are commonly joined when generating a complete multilingual definition of a configurable form.