Results for “per_time_period_types_tl”

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

Overview

PER_TIME_PERIOD_TYPES_TL is a translation (TL) table in the Oracle E-Business Suite Human Resources (PER) schema. It stores the language-specific, translated names and descriptions for the period types that underlie payroll and HR budget calendars. A period type is the abstract definition of a repeating time unit — for example a weekly, bi-weekly, semi-monthly, or monthly cycle — that drives calendar generation throughout Oracle Payroll and Oracle HRMS. Because end users must see these period type names in their own language, Oracle EBS separates the language-independent definition from its translations, and this table holds the translated half.

The object is owned by the HR schema and is documented as a predefined list of translated period types. In the ETRM 12.2.2 physical schema the table exposes eleven columns and a single unique index, PER_TIME_PERIOD_TYPES_TL_PK, whose key is (PERIOD_TYPE, LANGUAGE, ZD_EDITION_NAME). From a data-modelling perspective, the metadata classifies this object as standalone under the heuristic Data Vault classification. That classification is best treated as a modelling suggestion: it indicates the table has no mined foreign-key dependencies and behaves as a reference/lookup entity rather than a hub, link, or satellite. In a Data Vault design it would most naturally be modelled as a reference table supplying descriptive attributes to the base period type hub.

Key Information Stored

The table’s identity is defined by PERIOD_TYPE and LANGUAGE, which together form the conceptual primary key. PERIOD_TYPE is the language-independent code identifying the period type; LANGUAGE is the ISO/NLS language code of the translation row. Under the ZD_EDITION_NAME multi-tenant/editioning column, these same attributes make up the documented unique index PER_TIME_PERIOD_TYPES_TL_PK (PERIOD_TYPE, LANGUAGE, ZD_EDITION_NAME), which is the business-key candidate rather than a system-generated surrogate.

The most important descriptive columns are:

• DISPLAY_PERIOD_TYPE — the translated, user-facing name shown in payroll and budget calendar windows.
• DESCRIPTION — the translated explanatory text for the period type.
• SOURCE_LANG — the language from which the row was originally derived, used by the translation framework.
• LANGUAGE — the target language of this translated row.
• PERIOD_TYPE — the business key linking back to the base definition.
• ZD_EDITION_NAME — the editioning column that participates in the unique key.

The remaining columns are standard Oracle EBS audit attributes: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE. These record who created and last modified each translated row, and they are essential for audit and data-lineage reporting.

Common Use Cases and Queries

Typical usage centres on presenting period type names in the user’s session language and on validating that translations exist for every supported language. A reporting query joining the translation to its base definition commonly restricts by language:

• List translated period types for a language: SELECT period_type, display_period_type, description FROM per_time_period_types_tl WHERE language = USERENV('LANG');
• Detect missing translations by comparing against the base table for a target language.
• Feed calendar-generation and payroll period reporting where the display name must match the NLS setting.
• Audit changed translations using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Because the table is small and static, queries are inexpensive; the principal risk is joining without a LANGUAGE predicate, which returns duplicate rows per period type.

Related Objects

The most significant related object is the base, language-independent period type definition table in the HR schema, joined on PERIOD_TYPE. Calendar and payroll period entities that consume period types — including HR calendar and budget calendar definitions — reference the base period types that this table translates. The ZD_EDITION_NAME column links to the editioning framework. Administrators and developers should treat PER_TIME_PERIOD_TYPES_TL as the display-name companion to the base period type definition, always joined on PERIOD_TYPE and filtered by LANGUAGE.