Search Results per_person_types_tl




Overview
Explore in AI Chat

Overview

PER_PERSON_TYPES_TL is the translated (language-specific) child table of the HR schema's person type definition model in Oracle E-Business Suite 12.1.1 and 12.2.2. In Oracle EBS, a person type classifies individuals tracked in the HRMS repository — for example employees, applicants, contingent workers, dependents, or contacts. Person type definitions are stored in the base table PER_PERSON_TYPES, while their translatable display text is stored separately in PER_PERSON_TYPES_TL, one row per language. This separation is the standard Oracle EBS MLS (Multi-Language Support) design: the base table holds non-translated attributes, and the _TL table holds the user-facing name that varies by language.

From a heuristic Data Vault perspective, the metadata classifies this object as standalone, meaning no foreign key relationships were mined from its constraint structure. As a modeling suggestion, the object is best viewed as a satellite attached to the person type entity: its grain is person type plus language, and it carries descriptive, language-dependent text. It retains a direct relationship to the base table through PERSON_TYPE_ID, but that link is not expressed as a documented FK.

Key Information Stored

The table holds 10 documented columns and is keyed by the unique index PER_PERSON_TYPES_TL_PK, whose documented composition is (PERSON_TYPE_ID, LANGUAGE, ZD_EDITION_NAME). This composite key is the effective unique index / business-key candidate; it also serves as the primary key constraint PER_PERSON_TYPES_TL_PK. Important columns include:

  • PERSON_TYPE_ID — Surrogate reference to the parent person type in PER_PERSON_TYPES; the primary join key.
  • LANGUAGE — The language code identifying which locale's translation this row provides.
  • USER_PERSON_TYPE — The translated, user-facing name of the person type as displayed in that language.
  • SOURCE_LANG — The language of the source text from which the translation was derived, used by the MLS translation process.
  • ZD_EDITION_NAME — The edition identifier used by EBS Online Patching in 12.2.x to support edition-based redefinition; it also appears in the unique index.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle EBS "Who" columns providing audit and concurrency tracking.

Common Use Cases and Queries

This table is queried whenever a report, form, or interface must display a person type name in a specific language. Typical scenarios include HR self-service display, applicant and recruiting screens, and multi-lingual reporting where the translated name must be returned. A common pattern joins the translated table to the base table and filters on the session language:

  • Retrieve translated names: SELECT p.person_type_id, t.user_person_type FROM per_person_types p, per_person_types_tl t WHERE p.person_type_id = t.person_type_id AND t.language = USERENV('LANG').
  • Audit translation coverage: count rows per LANGUAGE to confirm all required locales are populated.
  • Validate seed data or MLS synchronization by comparing USER_PERSON_TYPE and SOURCE_LANG entries.
  • In 12.2.x, filter WHERE ZD_EDITION_NAME = 'ORA$BASE' to isolate the run-edition rows.

Because the table is a pure lookup, read access is sufficient for most reporting; direct DML should be avoided in favor of the supported person type setup forms.

Related Objects

The most significant related objects are those in the person type model and the broader HR person model:

  • PER_PERSON_TYPES — Base table; join on PERSON_TYPE_ID. Holds non-translated attributes and the SYSTEM_PERSON_TYPE flag.
  • PER_PERSON_TYPES_TL_PK — Primary key constraint / unique index (PERSON_TYPE_ID, LANGUAGE, ZD_EDITION_NAME).
  • PER_ALL_PEOPLE_F — Person records; join on PERSON_TYPE_ID to derive the type of each person.
  • PER_PERSON_TYPE_USAGES — Tracks which person types a person is associated with over time.
  • FND_LANGUAGES — Language reference; join on LANGUAGE.
  • HR_PERSON_TYPE_API / per_person_types_pkg — Supported setup and validation APIs for maintaining person type definitions and their translations.

Together these objects form the person type reference framework used throughout PER and dependent modules such as Payroll, OTL, and Recruiting.