Results for “system_status_type”

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

Overview

BIM_DIMV_USER_STATUSES is a read-only dimensional view owned by the APPS schema within the BIM (Marketing Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the set of user-defined statuses maintained in Oracle Marketing, presenting the descriptive and control attributes of each status record alongside its surrogate identifier. In an EBS reporting and integration context, the view functions as a denormalized, consumer-friendly access point for status reference data that would otherwise require joins across the underlying multilingual base objects. Because the view is defined with the WITH READ ONLY clause, it is intended exclusively for query and extract purposes and cannot be used for DML. The presence of a STATUS_NAME column reflects that the view resolves translated status names, making it suitable for direct display in reports, list of values, and analytical queries without requiring the caller to handle translation tables explicitly.

Underlying Base Objects

The documented ETRM metadata identifies a single referenced base object: AMS_USER_STATUSES_VL, which is itself a view rather than a physical table. The VL suffix denotes a "view with language" construct typical of Oracle EBS, where the base table (AMS_USER_STATUSES_B) stores language-independent columns such as USER_STATUS_ID, SYSTEM_STATUS_TYPE, SYSTEM_STATUS_CODE, ENABLED_FLAG, START_DATE_ACTIVE, and END_DATE_ACTIVE, while a related translation table (AMS_USER_STATUSES_TL) stores the translated NAME and DESCRIPTION. The VL view stitches these together and exposes the translated values. BIM_DIMV_USER_STATUSES selects from this VL view, meaning it inherits the language-resolution logic of the session and the underlying base table structure. No other base tables or views are documented as dependencies, and the view definition contains no joins, filters, or aggregation, so it is a straightforward projection of the VL object with a column alias applied to NAME as STATUS_NAME.

Key Columns

• USER_STATUS_ID — The surrogate primary key uniquely identifying each user-defined status. This is the value referenced by foreign keys in transaction and detail tables across the marketing data model.
• SYSTEM_STATUS_TYPE — Classifies the status according to the system-defined status category it extends or maps to, distinguishing groups of user-defined statuses.
• SYSTEM_STATUS_CODE — The code of the corresponding system status, used to align user-defined statuses with seeded Oracle behavior.
• STATUS_NAME — The translated, user-facing name of the status, aliased from the underlying NAME column of AMS_USER_STATUSES_VL. This is the column most frequently searched by report authors and integrators.
• DESCRIPTION — The translated free-text description of the status, typically displayed as supplementary information in lists of values and lookups.
• ENABLED_FLAG — Indicates whether the status is currently active and selectable (Y/N), used to suppress obsolete statuses from transactional pick lists.
• START_DATE_ACTIVE — The date from which the status becomes valid for use.
• END_DATE_ACTIVE — The date after which the status is no longer valid; a null value generally indicates an open-ended status.

Common Use Cases and Queries

Typical use cases include populating parameter lists of values with current, enabled statuses, joining status identifiers to marketing transaction facts in BI Publisher reports, and extracting status reference data for warehouse or dimensional model loading. Because the view is read only, all interaction is query based.

• Enabled statuses for a list of values: SELECT user_status_id, status_name FROM apps.bim_dimv_user_statuses WHERE enabled_flag = 'Y' ORDER BY status_name;
• Resolving a status name from an identifier: SELECT status_name, description FROM apps.bim_dimv_user_statuses WHERE user_status_id = :p_id;
• Statuses active on a given date: SELECT user_status_id, status_name FROM apps.bim_dimv_user_statuses WHERE enabled_flag = 'Y' AND (start_date_active IS NULL OR start_date_active <= SYSDATE) AND (end_date_active IS NULL OR end_date_active >= SYSDATE);
• Grouping by system status type: SELECT system_status_type, system_status_code, COUNT(*) FROM apps.bim_dimv_user_statuses GROUP BY system_status_type, system_status_code;

These patterns provide the standard mechanism for presenting user-defined status information consistently across BIM reports and integrations in both 12.1.1 and 12.2.2.