Search Results per_shared_types_tl_pk

Overview

HR.PER_SHARED_TYPES_TL is the translation (TL) table for shared person types in Oracle E-Business Suite. It stores the language-dependent, user-defined descriptive text associated with each shared type record defined in the base table HR.PER_SHARED_TYPES_B. The "_TL" suffix indicates that this table follows the standard Oracle EBS multi-language translation pattern: one row exists per shared type per installed language, with a SOURCE_LANG column identifying the language in which the row was originally authored. This design allows the same underlying shared type to present a different SHARED_TYPE_NAME to users depending on their session language.

The table resides in the HR schema, is delivered as part of the PER (People) product design data (PER.PER_SHARED_TYPES_TL), and is stored in the APPS_TS_TX_DATA tablespace with an associated unique index in APPS_TS_TX_IDX. Its status is documented as VALID in ETRM 12.2.2, and it remains applicable to 12.1.1 and 12.2.2 environments.

From a Data Vault modeling perspective, the heuristic classification of this object is standalone. In practice, because the table carries descriptive name attributes keyed by type identifier and language, it is most naturally modeled as a satellite attached to the shared-type business key, with LANGUAGE acting as a descriptive discriminator rather than part of the core hub key. This is offered as a modeling suggestion only; the physical table itself carries no foreign-key references to other database objects, as confirmed by the dependency metadata.

Key Information Stored

The primary key constraint PER_SHARED_TYPES_TL_PK is defined over SHARED_TYPE_ID and LANGUAGE. SHARED_TYPE_ID is a system-generated NUMBER(15) that identifies the shared type; LANGUAGE is the VARCHAR2 language code and forms the second component of the key. Together they guarantee a single translated row per type per language. The documented unique index also lists ZD_EDITION_NAME as a component, reflecting the editioning column present in 12.2.x schemas that support edition-based redefinition.

• SHARED_TYPE_ID – System-generated primary key value linking the translation back to the base shared type record.
• LANGUAGE – The language of the translated row; part of the primary key.
• SOURCE_LANG – The language in which the row was originally created, used by the translation framework to determine whether a row requires re-translation.
• SHARED_TYPE_NAME – VARCHAR2(80) holding the translated, user-defined name presented to the user for the shared type.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard Who columns auditing the most recent modification.
• CREATED_BY, CREATION_DATE – Standard Who columns recording row creation.
• ZD_EDITION_NAME – Editioning discriminator present in 12.2.2, supporting edition-based redefinition.

The surrogate identifier is SHARED_TYPE_ID, while the combination of SHARED_TYPE_ID and LANGUAGE constitutes the documented business-key candidate exposed through the unique index.

Common Use Cases and Queries

The most frequent use of this table is resolving the display name of a shared person type for a given language. A typical query joins the translation table to the base table or filters on a specific language:

• Language-specific lookup: SELECT shared_type_name FROM hr.per_shared_types_tl WHERE shared_type_id = :id AND language = USERENV('LANG');
• Names across all languages for one type: SELECT language, source_lang, shared_type_name FROM hr.per_shared_types_tl WHERE shared_type_id = :id;
• Audit and change tracking of translated names using LAST_UPDATE_DATE and LAST_UPDATED_BY.
• Identifying rows still carrying an obsolete SOURCE_LANG value ahead of a translation refresh cycle.

Reporting scenarios include multilingual extracts of shared type definitions, data-quality checks for missing translations, and comparisons against the base table to detect orphaned or untranslated rows.

Related Objects

Because the object is documented as standalone, it does not reference other database objects through foreign keys. The most significant related objects are:

• HR.PER_SHARED_TYPES_B – The base (non-translated) table holding shared type definitions; joined on SHARED_TYPE_ID.
• APPS.PER_SHARED_TYPES_TL – The APPS-layer synonym or view exposing the translation data to application code.
• PER_SHARED_TYPES_TL041512_WHO – A dependent object listed in the ETRM dependency metadata.
• PUBLIC.PER_SHARED_TYPES_TL – The public synonym variant referenced in the dependency data.
• Translation framework views in the FND/PER layer that consume SOURCE_LANG and LANGUAGE to render localized names.

Collectively, these objects support the multilingual presentation of shared person types across the EBS application stack.