Search Results ota_activity_versions_tl

Overview

The OTA_ACTIVITY_VERSIONS_TL table is a core translation table within the Oracle E-Business Suite (EBS) Learning Management module (OTA). It stores language-specific, translated values for activity version records. In Oracle EBS, the "_TL" suffix denotes a translation table that holds one row per language for each translatable entity, enabling multilingual support across the application. In this case, the entity is an activity version (a specific, versioned iteration of a learning activity), and OTA_ACTIVITY_VERSIONS_TL provides the descriptive text associated with that version in each installed language.

The table is present and valid in both Oracle EBS 12.1.1 and 12.2.2, owned by the OTA schema. The documented ETRM metadata classifies this object heuristically as "standalone" under the Data Vault reference model. In practical terms, a translation table such as this is best modeled as a satellite: it carries descriptive, language-dependent attributes attached to an underlying business key (the activity version identifier) rather than acting as a hub (which would hold the distinct business key) or a link (which would associate two hubs). Because the translation row is tightly bound to its parent entity, modeling it as a satellite keyed on the combination of ACTIVITY_VERSION_ID and LANGUAGE is the suggested approach.

The primary key, OTA_ACTIVITY_VERSIONS_TL_PK, is composed of ACTIVITY_VERSION_ID and LANGUAGE, which also functions as the business-key candidate via the unique index of the same name. This composite key guarantees exactly one translation row per activity version per language.

Key Information Stored

The documented physical schema contains 13 columns. The most important are:

• ACTIVITY_VERSION_ID — Surrogate/foreign key identifying the parent activity version. Part of the composite primary key.
• LANGUAGE — The language code for the translation. Part of the composite primary key.
• SOURCE_LANG — The source language from which the translation was derived, supporting the standard EBS translation pattern.
• VERSION_NAME — The translated display name of the activity version.
• DESCRIPTION — The translated long description of the activity version.
• INTENDED_AUDIENCE — The translated target audience narrative for the version.
• OBJECTIVES — The translated learning objectives.
• KEYWORDS — Translated keywords used for search and categorization.

The remaining columns are standard EBS audit and concurrency fields: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE. Together, the composite primary key (ACTIVITY_VERSION_ID, LANGUAGE) distinguishes the surrogate identity from the documented business-key unique index, which resolves to the same two columns in this case.

Common Use Cases and Queries

Translation tables such as this are queried whenever multilingual display text is required for activity versions. Typical use cases include report localization, course catalog display, and interface integration where users work in a non-base language.

• Retrieve the translated name and description for a specific version in a given language:

SELECT VERSION_NAME, DESCRIPTION FROM OTA.OTA_ACTIVITY_VERSIONS_TL WHERE ACTIVITY_VERSION_ID = :p_id AND LANGUAGE = USERENV('LANG');

• Produce a side-by-side comparison of all translations for a version to verify terminology consistency.
• Audit translations that are missing or that still point at a stale SOURCE_LANG value.
• Feed downstream reporting extracts that join translated columns back to the base activity version table.

Because the table is keyed on ACTIVITY_VERSION_ID and LANGUAGE, join predicates should always include both columns to avoid row multiplication.

Related Objects

The following objects are most significant in relation to OTA_ACTIVITY_VERSIONS_TL:

• OTA_ACTIVITY_VERSIONS — The base activity version table; join on ACTIVITY_VERSION_ID.
• OTA_ACTIVITIES — Parent activity definitions referenced through the activity version.
• OTA_ACTIVITY_VERSIONS_TL_PK — The primary key constraint/unique index enforcing the composite key.
• FND_LANGUAGES — Reference for valid LANGUAGE and SOURCE_LANG values.
• Standard EBS multilingual views that expose translated activity version text to forms and concurrent programs.
• Learning Management APIs and DML routines that maintain translation rows alongside the base version record.