Results for “dialog_name”
2 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:
- AMS_DIALOGS_ALL_B — the base (non-translated) dialog entity, aliased as B. It supplies identity, audit, status, currency, date, and descriptive-flexfield columns.
- AMS_DIALOGS_ALL_TL — the translation table, aliased as T. It supplies DIALOG_NAME, DIALOG_THEME, and DESCRIPTION, filtered by T.LANGUAGE = USERENV('LANG').
- AMS_CUSTOM_SETUPS_B — the custom setup base table, aliased as C, joined to supply ACTIVITY_TYPE_CODE.
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
- DIALOG_ID — primary identifier of the dialog; the key linking the base and translation rows.
- DIALOG_NAME, DIALOG_THEME, DESCRIPTION — translatable attributes sourced from AMS_DIALOGS_ALL_TL and returned in the session language.
- DIALOG_TYPE, SOURCE_CODE, TEMPLATE_FLAG, GLOBAL_FLAG, ACTIVE_FLAG, CONFIDENTIAL_FLAG — classification and control flags defining how the dialog is used and exposed.
- OWNER_ID, USER_STATUS_ID, SYSTEM_STATUS_CODE, STATUS_UPDATE_DATE — ownership and workflow/status tracking.
- DIALOG_CALENDAR, START_PERIOD_NAME, END_PERIOD_NAME — scheduling attributes for calendar-driven dialogs.
- START_DATE, END_DATE — effective date range of the dialog.
- TRANSACTION_CURRENCY_CODE, FUNCTIONAL_CURRENCY_CODE, BUDGET_AMOUNT_FC, BUDGET_AMOUNT_TC — currency and budget context (functional and transaction amounts).
- CUSTOM_SETUP_ID, ACTIVITY_TYPE_CODE — links to the custom setup and its activity type classification.
- PURPOSE, PRIORITY, BUSINESS_UNIT_ID, ORG_ID, COUNTRY_ID, LANGUAGE_CODE — operational context and Org/locale attribution.
- OBJECT_VERSION_NUMBER plus standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — concurrency control and audit.
- ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard Oracle descriptive flexfield (DFF) 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.
-
View: AMS_DIALOGS_VL 12.1.1
This is the view for dialog and it's also striped by language.
Not implemented in this database·Explore AMS module →
-
View: AMS_DIALOGS_VL 12.2.2
This is the view for dialog and it's also striped by language.
Not implemented in this database·Explore AMS module →