Search Results ahl_unit_effectivities_tl_pk

Overview

AHL_UNIT_EFFECTIVITIES_TL is the translation (language) table for unit effectivity records within the Oracle E-Business Suite product AHL — Complex Maintenance Repair and Overhaul (CMRO). In the EBS multilanguage architecture, translatable descriptive text is segregated from the base transactional table so that a single logical record can carry language-specific attributes for each installed language. This table stores the translated content associated with a unit effectivity, while the corresponding base row resides in AHL_UNIT_EFFECTIVITIES_B. Unit effectivities define the applicability windows or effectivity ranges used to control which maintenance, configuration, or MRO tasks apply to a given unit, and the translated attributes make those definitions readable in the operator's session language.

From a Data Vault modeling perspective, the relationship metadata classifies this object as satellite-leaning. That is a heuristic suggestion: the table is dependent on the parent unit effectivity entity and carries descriptive, language-dependent attributes rather than acting as an independent hub or a link between hubs. Its grain is the combination of unit effectivity and language, reinforcing its role as a dependent descriptive satellite.

Key Information Stored

The table is defined in the AHL schema and contains ten documented columns. The most significant are:

• UNIT_EFFECTIVITY_ID — the identifier of the parent unit effectivity. This is part of the primary key and the foreign key to AHL_UNIT_EFFECTIVITIES_B, binding each translated row to its base record.
• LANGUAGE — the installed language code for the translated content. Together with UNIT_EFFECTIVITY_ID it forms the composite primary key AHL_UNIT_EFFECTIVITIES_TL_PK and the unique index AHL_UNIT_EFFECTIVITIES_TL_U1, which serve as the business-key candidates.
• SOURCE_LANG — the language in which the source content was originally authored, used by the translation framework to track derivation and re-translation needs.
• REMARKS — the primary translatable descriptive column, holding the language-specific text associated with the unit effectivity.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, controlling row-level access according to the EBS multitenancy/security model.
• Standard audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN record who created and last modified the translation row and when, supporting audit and concurrency reporting.

Note that the surrogate backbone of the entity is the UNIT_EFFECTIVITY_ID inherited from the base table; the true composite key of the translation row is (UNIT_EFFECTIVITY_ID, LANGUAGE).

Common Use Cases and Queries

Typical usage centers on joining the translation table to its base table and restricting by the session or reporting language. A representative query retrieves the translated remarks for a given unit effectivity:

• SELECT b.unit_effectivity_id, t.remarks, t.language FROM ahl_unit_effectivities_b b, ahl_unit_effectivities_tl t WHERE b.unit_effectivity_id = t.unit_effectivity_id AND t.language = USERENV('LANG');
• Translation completeness reporting, to find base records with no translation for a required language, uses an outer join from AHL_UNIT_EFFECTIVITIES_B to this table.
• Language audit or re-translation workflows compare SOURCE_LANG against LANGUAGE to identify rows needing refresh after source content changes.
• Security-scoped extracts filter on SECURITY_GROUP_ID to return only rows visible to the responsible organization.

Related Objects

The following objects are most significant in relation to this table:

• AHL_UNIT_EFFECTIVITIES_B — the base table; joined on UNIT_EFFECTIVITY_ID. Every translation row must have a corresponding base row.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for access control.
• AHL_UNIT_EFFECTIVITIES_TL_PK / _U1 — the primary key constraint and unique index enforcing the (UNIT_EFFECTIVITY_ID, LANGUAGE) grain.
• AHL_UNIT_EFFECTIVITIES_VL (where present in CMRO) — the language view that unions translated and base columns for user-facing queries.
• The AHL unit effectivity public APIs and concurrent programs that create or maintain effectivity definitions, which write to the base table and cascade descriptive updates to this translation table.