Search Results hr_document_types_tl_pk

Overview

HR.HR_DOCUMENT_TYPES_TL is the translation (multi-language) child table of the HR document types definition in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the translatable descriptive attributes of each document type that can be assigned to a person, assignment, or applicant record within Oracle HRMS. The base, non-translatable definition resides in HR.HR_DOCUMENT_TYPES, while this table carries the language-dependent name and description text. The FND Design Data reference is PER.HR_DOCUMENT_TYPES_TL, confirming its ownership by the PER product under the HR schema. Physically it is stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index resides in APPS_TS_TX_IDX.

The supplied metadata classifies this object heuristically as standalone within a Data Vault style model. In Data Vault terms, the table most naturally behaves as a satellite attached to the HR_DOCUMENT_TYPES hub: it holds descriptive, language-qualified attributes keyed by the hub's surrogate identifier (DOCUMENT_TYPE_ID) plus the LANGUAGE discriminator. This is a modeling suggestion rather than a fixed classification.

Key Information Stored

The table carries eleven documented columns, of which the following are most significant:

• DOCUMENT_TYPE_ID — NUMBER(15). The foreign key to HR.HR_DOCUMENT_TYPES. It identifies the parent document type to which the translated row belongs.
• LANGUAGE — VARCHAR2. The defined (session) language in which the translated text is supplied. It forms part of the primary key.
• SOURCE_LANGUAGE — VARCHAR2. The actual language of the source text, used for the language-independent default.
• DOCUMENT_TYPE — VARCHAR2(40). The translatable name of the document type.
• DESCRIPTION — VARCHAR2(240). The translatable description of the document type.
• ZD_EDITION_NAME — The editioning column introduced under the 12.2 Online Patching (ADOP) architecture. It is part of both unique indexes in the 12.2.2 physical schema.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Who audit columns present on all Oracle HRMS transactional tables.

The surrogate primary key is HR_DOCUMENT_TYPES_TL_PK, documented as (DOCUMENT_TYPE_ID, LANGUAGE) in 12.1.1 and extended to (DOCUMENT_TYPE_ID, LANGUAGE, ZD_EDITION_NAME) in 12.2.2. Two unique indexes act as business-key candidates: HR_DOCUMENT_TYPES_TL_PK itself and HR_DOCUMENT_TYPES_TL_U1 on (LANGUAGE, DOCUMENT_TYPE, ZD_EDITION_NAME), which enforces uniqueness of the translated name per language.

Common Use Cases and Queries

Typical usage retrieves the translated display name of a document type for a given user session, joining the translation table to the base definition and filtering by LANGUAGE. The query below returns the document type names and descriptions in a target language:

• SELECT t.document_type_id, t.document_type, t.description FROM hr.hr_document_types_tl t WHERE t.language = USERENV('LANG');
• Join pattern: SELECT b.document_type_id, t.document_type, t.description FROM hr.hr_document_types b, hr.hr_document_types_tl t WHERE b.document_type_id = t.document_type_id AND t.language = :p_lang;
• Reporting: driving LOVs in the Documents of Payroll / Person form and in HRMS self-service pages, where only the language-appropriate label should appear.
• Verification of DFF or descriptive-flexfield prompts that are language sensitive.

Because SOURCE_LANGUAGE and LANGUAGE must be reconciled for the DFF-based translation flow, developers frequently query the pair to detect missing or mismatched translation rows when the application incorrectly shows a default label.

Related Objects

The most significant related objects include the following:

• HR.HR_DOCUMENT_TYPES — the base table; joined on DOCUMENT_TYPE_ID. This is the parent in the foreign key relationship.
• HR_DOCUMENT_TYPES_TL (APPS synonym/view) — the APPS-owned synonym that exposes this table to the application schema.
• HR_DOCUMENT_TYPES_TL041447_WHO — the seed data / Who-column loader used to maintain the Standard Who audit columns.
• PUBLIC synonym HR_DOCUMENT_TYPES_TL — a public synonym granting cross-schema visibility.
• APPS.HR_DOCUMENT_TYPES_TL — the APPS wrapper referenced in dependency metadata.
• The Oracle HRMS Document Types DFF / form definition and the Person / Assignment document assignment logic, which resolve the language-specific DOCUMENT_TYPE label at runtime.

No database object is referenced by HR.HR_DOCUMENT_TYPES_TL beyond its parent, confirming its role as a leaf-level translation satellite.