Search Results ahl_user_statuses_vl




Overview
Explore in AI Chat

Overview

The view AHL_USER_STATUSES_VL is a seeded, VALID database object owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AHL – Complex Maintenance Repair and Overhaul (CMRO) product family. It is a "_VL" (view with translation) object, meaning it obscures the underlying normalized storage model by joining a base entity table with its corresponding translation table and applying the session language filter. The result is a single, denormalized, language-aware record set that presents user status information along with translatable name and description attributes.

User statuses in the CMRO module represent configurable lifecycle states applied to maintainable assets, work orders, and related entities. As a _VL view, AHL_USER_STATUSES_VL is the canonical access point for reports, concurrent programs, Oracle Forms blocks, and integration layers that need to resolve a user status identifier to a human-readable, locale-appropriate label. The view follows the standard EBS MLS (Multi-Language Support) pattern and inherits the standard WHO audit columns, DFF (Descriptive Flexfield) attribute columns, and the Multi-Org SECURITY_GROUP_ID column, making it suitable for both reporting and Multi-Org aware processing.

Underlying Base Objects

The view is defined over two documented base objects, both exposed to APPS through synonyms:

  • AHL_USER_STATUSES_B — the base table holding the non-translatable attributes of each user status, including its surrogate key (USER_STATUS_ID), audit columns, system status mapping, active/default/seeded flags, and the 15 DFF attribute columns.
  • AHL_USER_STATUSES_TL — the translation table holding the language-dependent NAME and DESCRIPTION.

The join is performed on USER_STATUS_ID, with the additional predicate UST.LANGUAGE = USERENV('LANG'). This filter restricts the result set to the translation matching the session's language environment, ensuring each base row returns exactly one translated row. The view also exposes USB.ROWID as ROW_ID, which is important because the base table's physical ROWID is surfaced for use by Oracle Forms and other row-identification mechanisms.

Key Columns

  • USER_STATUS_ID — Primary identifier for the user status. Join key to other CMRO entities that reference a user status.
  • NAME / DESCRIPTION — Translatable attributes sourced from AHL_USER_STATUSES_TL, returned in the session language.
  • SYSTEM_STATUS_TYPE / SYSTEM_STATUS_CODE — Map a user status to a seeded system status, allowing custom statuses to drive standard CMRO processing logic.
  • ACTIVE_FLAG, DEFAULT_FLAG, SEEDED_FLAG — Control availability: whether the status is selectable, whether it is the default applied status, and whether it is a seeded (non-deletable) record. Seed flags are commonly filtered in reports to exclude Oracle-delivered rows.
  • START_DATE_ACTIVE / END_DATE_ACTIVE — Effective-dating window for the status's usability.
  • SECURITY_GROUP_ID — Multi-Org security group association.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield segments available for client-specific extensions.
  • ROW_ID, OBJECT_VERSION_NUMBER, and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include populating status LOVs in custom forms, validating status references in interfaces, and generating CMRO configuration and audit reports. Because the view is language-filtered, queries automatically return the appropriate translation without additional handling.

List all currently usable statuses:

  • SELECT user_status_id, name, description, system_status_type, system_status_code FROM apps.ahl_user_statuses_vl WHERE active_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE) ORDER BY name;

Retrieve the configured default status:

  • SELECT user_status_id, name FROM apps.ahl_user_statuses_vl WHERE default_flag = 'Y' AND active_flag = 'Y';

Identify customer-defined (non-seeded) statuses for a system status mapping:

  • SELECT user_status_id, name, system_status_code FROM apps.ahl_user_statuses_vl WHERE seeded_flag = 'N' ORDER BY system_status_code, name;

As with all _VL objects, join predicates should always reference USER_STATUS_ID rather than NAME, since names are translatable and may not be unique across languages or configurations.