Results for “ams_user_statuses_tl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_USER_STATUSES_TL is the translation table for user-defined statuses within the Oracle E-Business Suite Marketing (AMS) module. In the Oracle EBS multi-language architecture, the "_TL" suffix denotes a table that stores language-dependent (translatable) attribute values separately from language-independent data. The base, language-neutral rows for each user status are held in AMS_USER_STATUSES_B, while AMS_USER_STATUSES_TL carries the descriptive text that varies by installed language, such as the status NAME and DESCRIPTION. This separation allows a single user status definition to be presented in multiple languages without duplicating its structural attributes.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is satellite-leaning. In this interpretation, AMS_USER_STATUSES_TL functions primarily as a descriptive satellite that hangs off the hub-like parent record keyed by USER_STATUS_ID, capturing the language-specific descriptive context rather than acting as an independent business entity or an associative link. This classification is a modeling suggestion derived from key structure, not a physical mandate.

Key Information Stored

The table is documented with twelve columns. The most significant are summarized below.

• USER_STATUS_ID — The surrogate identifier foreign-keying back to the parent base table, AMS_USER_STATUSES_B. It distinguishes each user status.
• LANGUAGE — The language code identifying which translation the row represents; central to the multilingual design.
• NAME — The translated, language-specific name of the user status. It participates in the unique business key index AMS_USER_STATUSES_TL_UK1 (LANGUAGE, NAME).
• DESCRIPTION — The translated descriptive text for the status.
• SOURCE_LANG — Indicates the source language of the translated content, supporting translation-maintenance workflows.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, governing row-level access control.
• ZD_EDITION_NAME — The editioning column supporting Oracle EBS 12.2 online patching, present in the documented 12.2.2 physical schema.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN record who created and modified each translation row and when.

The documented primary key is AMS_USER_STATUSES_TL_PK, composed of (LANGUAGE, USER_STATUS_ID). A unique index, AMS_USER_STATUSES_TL_U1, covers (USER_STATUS_ID, LANGUAGE, ZD_EDITION_NAME), reflecting the edition-aware business-key candidate. A secondary unique key AMS_USER_STATUSES_TL_UK1 enforces uniqueness on (LANGUAGE, NAME).

Common Use Cases and Queries

This table is queried wherever localized status labels must be surfaced for Marketing user statuses. A typical reporting join retrieves the language-independent parent record together with its translation:

• Retrieving status names by language: SELECT b.user_status_id, t.name, t.description FROM ams_user_statuses_b b, ams_user_statuses_tl t WHERE b.user_status_id = t.user_status_id AND t.language = USERENV('LANG').
• Listing available languages for a status: filter AMS_USER_STATUSES_TL by a specific USER_STATUS_ID and select LANGUAGE, NAME to enumerate all installed translations.
• Translation completeness audit: compare row counts in AMS_USER_STATUSES_B against AMS_USER_STATUSES_TL per LANGUAGE to detect statuses missing translations.
• Edition-aware queries on 12.2: constrain ZD_EDITION_NAME (typically the run edition) to isolate the active patched edition.

Because NAME enforcement is unique per LANGUAGE via AMS_USER_STATUSES_TL_UK1, lookups by display name can rely on the language plus name combination.

Related Objects

• AMS_USER_STATUSES_B — The parent base table; joined on USER_STATUS_ID. It holds the language-independent status definition and is the hub-equivalent source for the satellite relationship.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID, providing row-level security context.
• AMS_USER_STATUSES_TL_PK — The composite primary key (LANGUAGE, USER_STATUS_ID) that the user searched for, enforcing row uniqueness.
• AMS_USER_STATUSES_TL_UK1 — Unique key on (LANGUAGE, NAME), guaranteeing unique display names per language.
• AMS_USER_STATUSES_TL_U1 — Edition-aware unique index on (USER_STATUS_ID, LANGUAGE, ZD_EDITION_NAME).

Together these objects form the complete multilingual representation of Marketing user statuses, with the _TL table supplying all descriptive, language-dependent content.