Search Results ahl_unit_effectivities_tl_u1

Overview

AHL.AHL_UNIT_EFFECTIVITIES_TL is the translation (TL) table for Oracle EBS Asset Lifecycle / Complex Maintenance, Repair and Overhaul (AHL) unit effectivities. In the Oracle EBS multilanguage architecture, a designated base table (here AHL_UNIT_EFFECTIVITIES_B) holds one row per business entity containing language-independent columns, while the paired _TL table holds one row per entity per installed language containing the language-sensitive descriptive columns. AHL_UNIT_EFFECTIVITIES_TL therefore stores the translatable text — principally remarks — associated with each unit effectivity, enabling the same effectivity definition to be presented in multiple languages without duplicating the base record.

From a Data Vault modeling perspective, the ETRM metadata classifies this object as satellite-leaning. This is a reasonable modeling suggestion: the table is dependent on a parent entity, keyed by the parent identifier plus a language discriminator, and it carries descriptive, time-stamped, non-key attributes. In practice it behaves as a language-specific descriptive satellite attached to the AHL_UNIT_EFFECTIVITIES_B hub/entity, with the LANGUAGE column acting as the additional qualifier that distinguishes one translated version of the record from another.

Key Information Stored

The table is physically stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10 and comprises ten documented columns. The most significant columns are:

• UNIT_EFFECTIVITY_ID — NUMBER, mandatory. Foreign key to AHL_UNIT_EFFECTIVITIES_B and the first component of the composite primary key. It identifies the parent unit effectivity record to which the translated row belongs.
• LANGUAGE — VARCHAR2, mandatory. The defined language code for the row's translated columns; the second component of the composite primary key and of the unique index.
• SOURCE_LANG — VARCHAR2. Indicates the actual language in which the translated columns were originally authored, which may differ from the display LANGUAGE.
• REMARKS — VARCHAR2(4000). The principal translatable text payload of the row.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. These provide the standard Oracle EBS audit and concurrency-control metadata for each row.
• SECURITY_GROUP_ID — NUMBER. Used in application hosting and foreign keyed to FND_SECURITY_GROUPS.

The surrogate identifier documented for uniqueness is the unique index AHL_UNIT_EFFECTIVITIES_TL_U1 on (UNIT_EFFECTIVITY_ID, LANGUAGE), which also corresponds to the composite primary key AHL_UNIT_EFFECTIVITIES_TL_PK. Because the unique index columns together form the primary key, the business-key candidate coincides with the primary key; there is no separate surrogate-only column exposed for business identification.

Common Use Cases and Queries

Typical usage includes resolving the localized remarks for a given unit effectivity, auditing translation completeness across languages, and joining translated text back to the base record for reporting. A common query pattern joins the base and translation tables on the shared identifier and language:

• Retrieve translated remarks for a specific effectivity: SELECT b.unit_effectivity_id, t.language, t.source_lang, t.remarks FROM ahl.ahl_unit_effectivities_b b, ahl.ahl_unit_effectivities_tl t WHERE b.unit_effectivity_id = t.unit_effectivity_id AND t.language = USERENV('LANG');
• Identify rows missing translations in a required language by comparing languages present in the _TL table against the set of installed languages.
• Audit stale translations using LAST_UPDATE_DATE relative to the base record's update timestamp.
• Report the original authoring language using SOURCE_LANG when verifying translation provenance.
• Filter by SECURITY_GROUP_ID in hosted or multi-tenant deployments.

Because the table is small and keyed by entity plus language, queries typically drive from the base or a business key and let the translation row supply descriptive text. Analysts should generally avoid reporting directly from the _TL table without joining to AHL_UNIT_EFFECTIVITIES_B, since the base table carries the language-independent definition.

Related Objects

• AHL.AHL_UNIT_EFFECTIVITIES_B — the base table; AHL_UNIT_EFFECTIVITIES_TL.UNIT_EFFECTIVITY_ID references it. This is the primary join partner.
• FND_SECURITY_GROUPS — referenced by the SECURITY_GROUP_ID column for application hosting.
• APPS.AHL_UNIT_EFFECTIVITIES_TL — the APPS synonyms/view that reference this table for runtime access.
• AHL_UNIT_EFFECTIVITIES_TL_U1 / AHL_UNIT_EFFECTIVITIES_TL_PK — the unique index and primary key supporting the language-based uniqueness of rows.

Together these objects form the base/translation pair that underpins multilingual unit effectivity data in the AHL module.