Search Results per_shared_types_tl




Overview
Explore in AI Chat

Overview

PER_SHARED_TYPES_TL is the translation (language-specific) table for shared types within the Oracle E-Business Suite Human Resources (PER) product family. Shared types provide a common vocabulary of reusable, user-defined classifications that multiple Oracle EBS products can reference instead of each maintaining its own redundant lookup. The _TL suffix designates that this table stores the translatable text attributes — primarily the display name — for each shared type defined in the base table PER_SHARED_TYPES_B. Oracle maintains one row per shared type per installed language, enabling multilingual presentation of the same underlying business entity.

The table resides in the HR schema and holds a status of VALID in both 12.1.1 and 12.2.2. Its documented physical schema in 12.2.2 comprises 10 columns. From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone, meaning it is not a pure hub, link, or satellite in the classic sense; it is best modeled as a satellite of a shared-type hub, carrying descriptive, language-dependent attributes keyed to the parent entity and the language code. This classification should be treated as a modeling suggestion rather than a prescriptive implementation detail.

Key Information Stored

The table's identity and descriptive content are captured by the following columns documented in the ETRM schema:

  • SHARED_TYPE_ID — Surrogate identifier of the parent shared type; the foreign-key-style linkage back to the base definition. It forms part of the composite primary key.
  • LANGUAGE — The language code identifying which translation this row provides. Together with SHARED_TYPE_ID it constitutes the composite primary key PER_SHARED_TYPES_TL_PK.
  • SHARED_TYPE_NAME — The translated, user-facing name of the shared type. This is the principal business attribute the table exists to hold.
  • SOURCE_LANG — Indicates the source language from which the translation was derived, supporting Oracle's translation-refresh mechanism.
  • ZD_EDITION_NAME — The editioning column introduced under the 12.2 online-patching (AD Online Patching) architecture. It participates in the unique index PER_SHARED_TYPES_TL_PK (SHARED_TYPE_ID, LANGUAGE, ZD_EDITION_NAME) and isolates rows per edition.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE record who created and last modified each translation row and when.

The composite primary key (SHARED_TYPE_ID, LANGUAGE) distinguishes the surrogate-plus-language identity, while the unique index additionally incorporating ZD_EDITION_NAME represents the effective business-key candidate in the 12.2 editioning model.

Common Use Cases and Queries

Typical scenarios include retrieving the localized name of a shared type for a given language, validating that translations exist for all installed languages, and joining shared-type values to dependent transactional tables for reporting. Because shared types are consumed across products, reporting often needs the display name rather than the numeric identifier.

A representative query joining the translation table to its base table:

  • SELECT b.shared_type_id, t.shared_type_name, t.language
  • FROM per_shared_types_b b, per_shared_types_tl t
  • WHERE b.shared_type_id = t.shared_type_id
  • AND t.language = USERENV('LANG');

Other common patterns: detecting missing translations with an outer join between the base table and this table filtered by language; auditing stale translations using LAST_UPDATE_DATE; and joining SHARED_TYPE_ID to the various intersection or detail tables that reference shared-type values.

Related Objects

  • PER_SHARED_TYPES_B — The base (non-translated) table; joins on SHARED_TYPE_ID. Together the _B and _TL tables form the standard Oracle translated-entity pattern.
  • PER_SHARED_TYPES_VL — The translated view that exposes the current-language name by joining the base and translation tables.
  • PER_SHARED_TYPES_TL_PK — The primary-key constraint/index enforcing uniqueness on (SHARED_TYPE_ID, LANGUAGE).
  • PER_SHARED_TYPES_TL unique index (SHARED_TYPE_ID, LANGUAGE, ZD_EDITION_NAME) — the editioning-aware business-key candidate.
  • Shared-type intersection and detail tables within the PER and other product schemas that consume SHARED_TYPE_ID as a foreign-key reference.
  • FND_LANGUAGES — Provides the valid language codes used in the LANGUAGE column for validation and reporting.

Because the ETRM metadata documents this object as standalone, no additional foreign-key dependencies beyond the parent shared-type relationship are formally recorded.