Search Results ams_user_statuses_vl




Overview
Explore in AI Chat

Overview

AMS_USER_STATUSES_VL is a seeded, VALID view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the AMS (Marketing) product family and exposes the user-defined statuses that are attached to the system statuses used by Marketing objects such as events, registrations, and campaigns. In the Oracle Applications data model, a system status represents the internally enforced lifecycle stage of a marketing object, whereas a user status is the customer-configurable label that the business applies to that stage. AMS_USER_STATUSES_VL is the translation-enabled (VL, or "view with language") interface through which that mapping is presented.

Because the view resolves the current session language automatically, it returns only the name and description rows relevant to the caller's environment. It is therefore the appropriate source for concurrent programs, PL/SQL APIs, OAF pages, and ad hoc reporting that must display user statuses in the user's own language without manually joining the translation table. In integration scenarios, it provides a stable, denormalized read surface over the two underlying tables, allowing external systems to retrieve status definitions by ID or by status type without knowledge of the TL/B table split.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms: AMS_USER_STATUSES_B (the base table holding language-independent attributes) and AMS_USER_STATUSES_TL (the translation table holding NAME and DESCRIPTION per language). The defining SQL is an inner join between the two on USER_STATUS_ID, constrained by the condition T.LANGUAGE = USERENV('LANG'). This means every row returned reflects one user status in exactly one language, and a status lacking a translation row for the session language will not appear. Both source objects are recorded as VALID, and the view carries no additional filtering or aggregation beyond the join and language predicate.

Key Columns

  • ROW_ID – the ROWID of the underlying AMS_USER_STATUSES_B record; useful for direct row addressing.
  • USER_STATUS_ID – primary key of the user status and the join key between the base and translation tables.
  • NAME / DESCRIPTION – translated display text sourced from AMS_USER_STATUSES_TL.
  • SYSTEM_STATUS_TYPE – identifies the marketing object family (for example event, registration, or campaign) to which the system status belongs.
  • SYSTEM_STATUS_CODE – the internal code of the system status that this user status decorates.
  • ENABLED_FLAG – indicates whether the user status is currently active for use.
  • START_DATE_ACTIVE / END_DATE_ACTIVE – the effective date range of the user status.
  • DEFAULT_FLAG – marks the user status designated as default for its system status.
  • SEEDED_FLAG – distinguishes Oracle-seeded definitions from customer-created ones.
  • OBJECT_VERSION_NUMBER – optimistic locking token maintained by the underlying table.
  • Audit columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN record who created and last modified each row.

Common Use Cases and Queries

Typical uses include building value lists for marketing setup screens, validating that a status is enabled and within its active dates before assignment to an event or campaign, and extracting status definitions for data warehouse or CRM integration loads. The DEFAULT_FLAG and ENABLED_FLAG columns support filtering to the currently usable statuses, while SYSTEM_STATUS_TYPE and SYSTEM_STATUS_CODE allow grouping by object family and lifecycle stage.

The following query lists all enabled user statuses for a given system status type:

SELECT user_status_id, name, system_status_code, default_flag
FROM apps.ams_user_statuses_vl
WHERE enabled_flag = 'Y'
AND system_status_type = :p_status_type
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
ORDER BY name;

To identify the default user status for a specific system status code:

SELECT user_status_id, name
FROM apps.ams_user_statuses_vl
WHERE default_flag = 'Y'
AND system_status_type = :p_status_type
AND system_status_code = :p_status_code;

Because the view answers only in the session language, reports intended for multilingual audiences should either run under the target language or query AMS_USER_STATUSES_TL directly when all translations are required.