Search Results ota_certifications_tl




Overview
Explore in AI Chat

Overview

OTA_CERTIFICATIONS_TL is the translation (TL) table for certification definitions within the Oracle E-Business Suite Learning Management module (OTA). In the Oracle EBS multilanguage architecture, every translatable entity is split into a base table that stores language-independent attributes and one or more TL tables that store the language-dependent descriptive text. OTA_CERTIFICATIONS_TL holds the translated certification text for each installed language, allowing the same certification to present localized names, descriptions, and structured content to users whose session language differs.

The table resides in the OTA schema and is documented as VALID in ETRM 12.2.2, containing 16 columns. Its primary key, OTA_CERTIFICATIONS_TL_PK, is composed of CERTIFICATION_ID and LANGUAGE, and the table carries a foreign key from CERTIFICATION_ID to OTA_CERTIFICATIONS_B, the base table. Based on the foreign-key structure, the heuristic Data Vault classification for this object is satellite-leaning: it behaves as a descriptive satellite attached to the certification hub/link represented by the base table, capturing context (language-specific text) that changes independently of the core certification identity.

Key Information Stored

The most significant columns are:

  • CERTIFICATION_ID — Surrogate identifier of the parent certification; part of the composite primary key and the FK to OTA_CERTIFICATIONS_B.
  • LANGUAGE — Installed language code; the second component of the primary key and the discriminator that makes each row a translation.
  • SOURCE_LANG — The language from which the row was originally created, used by the translation framework to determine whether content is a base or a translated record.
  • NAME — Localized certification name.
  • DESCRIPTION — Localized certification description.
  • OBJECTIVES — Localized learning objectives associated with the certification.
  • PURPOSE — Localized statement of the certification's purpose.
  • KEYWORDS — Localized searchable keywords.
  • END_DATE_COMMENTS — Localized comments about certification expiration.
  • INITIAL_PERIOD_COMMENTS — Localized comments about the initial validity period.
  • RENEWAL_PERIOD_COMMENTS — Localized comments about the renewal period.
  • CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns for audit and concurrency tracking.

The business-key candidate is the unique index over (CERTIFICATION_ID, LANGUAGE), which coincides with the primary key; the surrogate key proper is CERTIFICATION_ID, inherited from the base table and not independently generated in this table.

Common Use Cases and Queries

Typical usage centers on multilingual reporting, translation completeness audits, and certification catalog extracts. A join to the base table is almost always required to combine language-independent attributes with translated text.

  • Retrieve the translated certification for a specific language: SELECT t.name, t.description FROM ota_certifications_tl t WHERE t.certification_id = :id AND t.language = USERENV('LANG');
  • Audit for missing translations: compare distinct LANGUAGE values against FND_LANGUAGES for a given certification_id.
  • Find rows still held in the source language: query where LANGUAGE = SOURCE_LANG.
  • Report on certifications by keyword in a given language using the KEYWORDS column.

Related Objects

  • OTA_CERTIFICATIONS_B — Base table; joined on OTA_CERTIFICATIONS_TL.CERTIFICATION_ID = OTA_CERTIFICATIONS_B.CERTIFICATION_ID (the documented FK).
  • OTA_CERTIFICATIONS_VL — The translated view typically defined over the _B and _TL pair for DML and reporting.
  • FND_LANGUAGES — Reference for the LANGUAGE column values.
  • FND_APPLICATION / FND_TERRITORIES — Supporting lookups for localization context.
  • HR_API / OTA translation APIs — PL/SQL packages that insert and update TL rows via the base-table translation flow.