Search Results hr_doument_types_tl_pk

Overview

HR_DOCUMENT_TYPES_TL is the translated (language-specific) child table of the Human Resources module in Oracle E-Business Suite, holding the displayable text for Document Types of Record. In Oracle EBS, "Documents of Record" are the HR-required pieces of documentation tracked against a person or assignment, such as identification, eligibility, or certification records. The base (non-translated) definition of each document type lives in HR_DOCUMENT_TYPES_B, while HR_DOCUMENT_TYPES_TL stores the translated values for the translatable columns (DOCUMENT_TYPE and DESCRIPTION) for every installed language, enabling multi-language end-user presentation. The table resides in the HR schema under the PER (Human Resources) product and is documented as VALID in both 12.1.1 and 12.2.2.

From a modeling perspective, the heuristic Data Vault classification mined from the FK structure is standalone. Because it carries no outbound foreign-key relationships in the documented graph, it does not function as a link; it behaves conceptually like a descriptive satellite hanging off the base entity keyed by DOCUMENT_TYPE_ID and LANGUAGE, and it should be modeled as a reference/translation lookup rather than a hub.

Key Information Stored

The documented physical schema of the 12.2.2 ETRM record contains 11 columns. The most important are:

• DOCUMENT_TYPE_ID — the surrogate/business identifier of the parent document type; the first component of the primary key and the join key back to HR_DOCUMENT_TYPES_B.
• LANGUAGE — the language code of the translation row; combined with DOCUMENT_TYPE_ID it forms the PK component. Distinguishes one translated row from another for the same document type.
• SOURCE_LANGUAGE — the language from which the translated text was derived, used in the standard EBS multi-language (TL) pattern to indicate translation origin.
• DOCUMENT_TYPE — the translated short name/display name of the document type; this is the user-facing label.
• DESCRIPTION — the translated longer description of the document type.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit/who columns recording who created or last modified the row and when.
• ZD_EDITION_NAME — the edition-name column used in the Oracle 12.2.x editioning/online-patching scheme; it appears in both unique indexes.

Two unique indexes are documented: HR_DOCUMENT_TYPES_TL_PK (business-key candidate on DOCUMENT_TYPE_ID, LANGUAGE, ZD_EDITION_NAME) and HR_DOCUMENT_TYPES_TL_U1 (LANGUAGE, DOCUMENT_TYPE, ZD_EDITION_NAME). The primary key formally declared is HR_DOUMENT_TYPES_TL_PK on (DOCUMENT_TYPE_ID, LANGUAGE). The uniqueness of LANGUAGE + DOCUMENT_TYPE in U1 ensures a given translated name is not duplicated within a language.

Common Use Cases and Queries

HR_DOCUMENT_TYPES_TL is typically joined to the base table and filtered by the session language to return the correctly localized label. A representative query:

• SELECT b.document_type_id, t.document_type, t.description FROM hr.hr_document_types_b b, hr.hr_document_types_tl t WHERE b.document_type_id = t.document_type_id AND t.language = USERENV('LANG') ORDER BY t.document_type;
• Localization QA: comparing a translation row against SOURCE_LANGUAGE to confirm which languages are translated.
• Reporting: listing all translated document-type names for a DFF/quickcode-style lookup, or feeding a document-of-record checklist report.
• Data migration/extract: pulling DOCUMENT_TYPE_ID plus translated DOCUMENT_TYPE to map legacy codes to EBS document types.

Related Objects

• HR_DOCUMENT_TYPES_B — base table; join on DOCUMENT_TYPE_ID.
• HR_DOCUMENT_TYPES_VL — the translated view combining B and TL rows; commonly used in forms and reports.
• HR_DOCUMENT_TYPES / HR_DOCUMENTS_OF_RECORD — parent/child tables referencing the document-type definition.
• PER_DOCUMENT_TYPES — reference by DOCUMENT_TYPE_ID in dependent HR entities.
• FND_LANGUAGES — join on LANGUAGE for language names.
• HR_LOOKUPS — related HR reference data for document-type classification.