Results for “master_ds_rec_numbers”

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

Overview

AMS_QUERY_TEMPLATE_VL is a translation-enabled (VL) view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS – Marketing product family. It exposes query template information maintained through the Audience Workbench, the Oracle Marketing component used to define, store, and manage audience selection criteria. In EBS Release 12.1.1 and 12.2.2 the view is documented as VALID, meaning it is a supported dictionary object that can be referenced by reports, concurrent programs, personalizations, and external integrations that need read access to template definitions without querying the underlying transactional tables directly.

The VL suffix indicates that the view joins a language-independent base table to its translation table and filters the result by the session language. This is why TEMPLATE_NAME and DESCRIPTION are surfaced through the view: they are resolved at runtime according to USERENV('LANG'). Developers should therefore treat this object as the localization-aware entry point for query template metadata rather than as a simple projection of a single table.

Underlying Base Objects

The ETRM 12.2.2 metadata identifies two referenced base objects, both accessed through APPS synonyms: AMS_QUERY_TEMPLATE_ALL and AMS_QUERY_TEMPLATE_TL. The view definition joins them on TEMPLATE_ID and restricts the translation row to the current language:

FROM AMS_QUERY_TEMPLATE_TL T, AMS_QUERY_TEMPLATE_ALL B WHERE B.TEMPLATE_ID = T.TEMPLATE_ID AND T.LANGUAGE = USERENV('LANG')

AMS_QUERY_TEMPLATE_ALL holds the language-independent attributes of each query template, including workflow-style audit columns, seeded and enabled flags, security group, and the materialized-view related fields. AMS_QUERY_TEMPLATE_TL holds the translatable text attributes. The view selects B.ROWID as ROW_ID, giving each row a pseudo-key that reflects the base row in AMS_QUERY_TEMPLATE_ALL.

Key Columns

• ROW_ID – the ROWID of the base row in AMS_QUERY_TEMPLATE_ALL.
• TEMPLATE_ID – primary identifier of the query template; the join key between the _ALL and _TL tables.
• TEMPLATE_NAME, DESCRIPTION – language-dependent attributes sourced from AMS_QUERY_TEMPLATE_TL.
• AQE_ID – the Advanced Query Engine identifier associated with the template, relevant because it links the template to its AQE definition and is a frequent search term for this object.
• TEMPLATE_TYPE, LIST_SRC_TYPE, IN_USE_FLAG, ENABLED_FLAG, SEEDED_FLAG – classification and lifecycle/status attributes of the template.
• MV_NAME, MV_AVAILABLE_FLAG, SAMPLE_PCT, MASTER_DS_REC_NUMBERS, SAMPLE_PCT_RECORDS, RECALC_TABLE_STATUS – materialized-view and sampling controls used when the template is executed against large audiences.
• SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER – multi-organization security and optimistic locking columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, VIEW_APPLICATION_ID – standard EBS who-columns and concurrent program audit trail.

Common Use Cases and Queries

The view is typically used to list available query templates for an organization, to inspect a template's AQE linkage, or to review sampling and materialized-view settings before running an audience build. Because it excludes the LANGUAGE filter logic, callers never need to add translation joins themselves.

To locate a template by its Advanced Query Engine identifier:

SELECT template_id, template_name, aqe_id, enabled_flag, mv_name
FROM apps.ams_query_template_vl
WHERE aqe_id = :aqe_id;

To list enabled templates with their type and sampling configuration:

SELECT template_id, template_name, template_type, list_src_type,
sample_pct, recalc_table_status
FROM apps.ams_query_template_vl
WHERE enabled_flag = 'Y'
ORDER BY template_name;

To review materialized-view readiness for templates that use one:

SELECT template_id, template_name, mv_name, mv_available_flag,
last_update_date
FROM apps.ams_query_template_vl
WHERE mv_name IS NOT NULL;

Because the view filters translation rows by the session language, any external integration should connect with an NLS_LANG value matching a loaded language; otherwise TEMPLATE_NAME and DESCRIPTION may return no rows for templates that lack a translation in that language.