Search Results ahl_user_statuses_tl_u1

Overview

AHL.AHL_USER_STATUSES_TL is the translation (TL) table for Oracle E-Business Suite's Oracle Complex Maintenance, Repair and Overhaul (CMRO) application. It stores language-specific, user-defined status definitions that are attached to asset maintenance activities, work orders, and related lifecycle transitions. The base table, AHL_USER_STATUSES_B, holds the language-independent attributes of each user status, while this TL table holds the translatable text — the name and description — for each installed language. In Oracle EBS 12.1.1 and 12.2.2 the two tables are paired through the USER_STATUS_ID foreign key, and both reside in the APPS_TS_SEED tablespace, which confirms that user statuses are treated as seeded, reference-style configuration data rather than high-volume transactional data.

Because the table carries standard WHO columns, it participates in the Multi-Org and audit conventions that govern all EBS application tables. Under a heuristic Data Vault classification derived from its foreign key structure, AHL_USER_STATUSES_TL is satellite-leaning: it hangs off the AHL_USER_STATUSES_B parent (which behaves as a hub for the user status identity) and contains descriptive, language- and time-versioned attributes. Modelers treating the CMRO schema as a Data Vault should therefore view AHL_USER_STATUSES_B as the hub and this TL table as a satellite keyed by USER_STATUS_ID plus LANGUAGE.

Key Information Stored

The documented physical schema defines ten columns. The most significant are:

• USER_STATUS_ID — numeric surrogate identifier and part of the composite primary-key structure. The single-column primary key AHL_USER_STATUSES_TL_PK is documented on this column, and it also forms the foreign key to AHL_USER_STATUSES_B.
• LANGUAGE — the installed language code for the translated row. Together with USER_STATUS_ID it forms the business-key candidate captured by unique index AHL_USER_STATUSES_TL_U1, the index the user searched for. This uniqueness constraint guarantees exactly one translated row per status per language.
• SOURCE_LANG — records the language in which the translated text was originally entered, supporting the EBS language-derivation and fallback mechanism.
• NAME — the user-defined display name of the status (maximum 120 characters) shown to end users in the application.
• DESCRIPTION — the user-defined textual explanation of the status (maximum 2000 characters).
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns capturing when and by whom each translation row was created, changed, and last accessed.

The surrogate USER_STATUS_ID and the LANGUAGE attribute together constitute the effective business key; the unique index AHL_USER_STATUSES_TL_U1 enforces that relationship at the database level.

Common Use Cases and Queries

The primary operational use case is presentation: the EBS framework uses the LANGUAGE and SOURCE_LANG columns to resolve the correct localized status name and description for the session language. Reporting and integration scenarios typically join the TL table back to the base table and filter on the language to avoid duplicate rows.

• Retrieve all translated rows for a status: SELECT USER_STATUS_ID, LANGUAGE, SOURCE_LANG, NAME, DESCRIPTION FROM AHL.AHL_USER_STATUSES_TL WHERE USER_STATUS_ID = :id;
• Return the current session's translation only: ... WHERE USER_STATUS_ID = :id AND LANGUAGE = USERENV('LANG');
• List distinct status names across languages for a bilingual or multi-country rollout: SELECT LANGUAGE, NAME FROM AHL.AHL_USER_STATUSES_TL ORDER BY LANGUAGE, NAME;
• Validate that translations exist for every status: outer-join AHL_USER_STATUSES_B to the TL table on USER_STATUS_ID and LANGUAGE to find untranslated or missing rows — a common data-quality check during implementation and patching.
• Audit activity by joining on the WHO columns or by comparing LAST_UPDATE_DATE against the base table to identify stale translations.

Related Objects

• AHL.AHL_USER_STATUSES_B — the base table; joined on USER_STATUS_ID (documented foreign key) and the mandatory parent of this translation table.
• AHL_USER_STATUSES_TL_U1 — the unique index on (USER_STATUS_ID, LANGUAGE) that enforces one translation per language per status.
• AHL_USER_STATUSES_TL_PK — the documented primary key structure used for row identity.
• AHL_USER_STATUSES_TL (APPS synonym/view) — the APPS-owned synonym through which the table is queried by the application and by custom code, as shown in the dependency metadata.
• FND_LANGUAGES / FND_TERRITORIES — reference tables that resolve LANGUAGE codes into readable names for reporting.
• The CMRO status-assignment entities within the AHL schema that store USER_STATUS_ID values and rely on this table for display text.