Results for “ams_dialogs_vl”

4 results




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

Overview

AMS_DIALOGS_VL is a language-striped (VL) view within the AMS – Marketing (Advanced Marketing / Oracle Marketing) product family. In Oracle E-Business Suite 12.1.1 and 12.2.2, a "_VL" view is the standard Oracle convention for a translation-enabled view that joins a base (_B) table to its translation (_TL) table and filters the translation rows to the session's current language. As the ETRM metadata states, "This is the view for dialog and it's also striped by language." In other words, AMS_DIALOGS_VL presents the descriptive and translatable attributes of a marketing dialog (its name, theme, and description) in the run-time language of the querying session, while simultaneously exposing the operational and control attributes stored on the base dialog entity.

Dialogs in Oracle Marketing represent reusable, structured interactions or scripts used in campaign and telemarketing execution. The view therefore serves as a reporting and integration surface: it allows concurrent programs, BI Publisher reports, OAF pages, and custom SQL to retrieve dialog definitions without manually resolving the base/translation relationship or handling language filtering themselves. It is worth noting that the ETRM metadata marks the object as "Not implemented in this database," indicating the view may not be instantiated in every environment; this is common for optional or module-specific Marketing objects and should be confirmed against the target instance before use in production code.

Underlying Base Objects

The documented view text defines AMS_DIALOGS_VL over three underlying base objects:

The join predicates are B.DIALOG_ID = T.DIALOG_ID and B.CUSTOM_SETUP_ID = C.CUSTOM_SETUP_ID. The "_ALL" suffix on the dialog tables indicates that these are multi-organization (multi-Org) tables; consequently the view exposes ORG_ID, and queries should be run under the appropriate operating unit or MO security profile. Because the view is striped by language through USERENV('LANG'), only the translation row matching the session language is returned, giving one row per dialog per session language.

Key Columns

Common Use Cases and Queries

Typical uses include listing active dialogs for a business unit, resolving dialog names in the user's language for reports, and integrating dialog definitions with campaign or telemarketing processes. Example: retrieve active dialogs with their translated name and theme.

  • Basic lookup by language: SELECT dialog_id, dialog_name, dialog_theme FROM ams_dialogs_vl WHERE active_flag = 'Y';
  • Filter by organization and type: SELECT dialog_id, dialog_name, dialog_type FROM ams_dialogs_vl WHERE org_id = :p_org_id AND dialog_type = :p_type;
  • Effective-dated reporting: SELECT dialog_id, dialog_name, start_date, end_date FROM ams_dialogs_vl WHERE SYSDATE BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE+1);
  • Join to activity type: SELECT dialog_name, activity_type_code, purpose FROM ams_dialogs_vl WHERE custom_setup_id IS NOT NULL ORDER BY dialog_name;

Because the view already restricts translation rows to USERENV('LANG'), no additional language predicate is required; to retrieve all languages, query the underlying AMS_DIALOGS_ALL_TL table directly. Always run under a valid MO operating unit context, since ORG_ID is exposed from the "_ALL" base table.