Results for “ams_query_tmpl_condition_vl”

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

Overview

The AMS_QUERY_TMPL_CONDITION_VL view is an Oracle E-Business Suite (EBS) Marketing (AMS) database object owned by the APPS schema. It is documented as a valid view in both EBS 12.1.1 and 12.2.2. Per the ETRM metadata, this view "stores the information on the query template conditions in Audience Workbench." In practice, it exposes the set of selectable source fields and source types that a user may add as filter conditions when building or editing a query template within the Audience Workbench (also called Audience Workbench / List Builder) in Oracle Marketing.

The view does not persist data itself; it is a read-only, virtual construct. It presents a normalized union of two logical groupings: (1) list source types (for example, Analytics or Target data sources), and (2) the individual fields belonging to those source types. This makes it the primary lookup source for condition-building UI components and for any reporting or integration process that must resolve which fields are available for query template conditions. The view name carries the _VL suffix, indicating a translation-enabled ("VL" = value list / translated) view where user-facing name columns are exposed through the MLS (Multi-Lingual Support) layer.

Underlying Base Objects

The ETRM metadata identifies three referenced base objects, all in the APPS schema:

• AMS_LIST_SRC_TYPES_VL (VIEW) — the translated list of source types (Analytics, Target, and so on) with their LIST_SOURCE_NAME, SOURCE_OBJECT_NAME, SOURCE_TYPE_CODE, and ENABLED_FLAG.
• AMS_LIST_SRC_FIELDS_VL (VIEW) — the translated list of individual fields available within each source type, including SOURCE_COLUMN_NAME, SOURCE_COLUMN_MEANING, FIELD_DATA_TYPE, and ATTB_LOV_ID.
• AMS_LIST_SRC_TYPE_ASSOCS (SYNONYM) — the association table linking master source types to sub source types, exposing MASTER_SOURCE_TYPE_ID and SUB_SOURCE_TYPE_ID; a synonym to the underlying base table.

The view body is a UNION of two SELECT statements. The first derives "master" source rows by joining AMS_LIST_SRC_TYPES_VL to AMS_LIST_SRC_TYPE_ASSOCS on LIST_SOURCE_TYPE_ID = SUB_SOURCE_TYPE_ID, restricted to LIST_SOURCE_TYPE IN ('ANALYTICS','TARGET') where both records have ENABLED_FLAG = 'Y'. The second derives individual field rows by joining AMS_LIST_SRC_TYPES_VL to AMS_LIST_SRC_FIELDS_VL on LIST_SOURCE_TYPE_ID, again filtered for enabled Analytics/Target types and enabled fields.

Key Columns

• ID — In the first UNION branch, this is ASSOC.SUB_SOURCE_TYPE_ID; in the second branch it is -1 (a placeholder indicating a field-level row).
• NAME — The display name, from LIST_SOURCE_NAME for source types and SOURCE_COLUMN_MEANING for fields.
• SELECT_WHERE — A condition expression; populated as 'DUMMY' for source types and as SOURCE_COLUMN_NAME for fields.
• FROM_CLAUSE / SOURCE_COLUMN_NAME — Both resolve to SOURCE_OBJECT_NAME.
• ALIAS — The SOURCE_TYPE_CODE, used as the table alias in generated SQL.
• SRC_FLD_ID — -1 for source types; LIST_SOURCE_FIELD_ID for fields.
• MASTER_SRC_TYPE_ID — The parent source type identifier; the column referenced by the user's search of master_src_type_id. In branch one it is ASSOC.MASTER_SOURCE_TYPE_ID; in branch two it is TYPES1.LIST_SOURCE_TYPE_ID. It groups individual fields under their owning source type.
• DISABLED — 'Y' for source types, 'N' for fields.
• SOURCE_COLUMN_MEANING, FIELD_DATA_TYPE, ATTB_LOV_ID, DATA_SOURCE_NAME — Meaning, datatype, attribute LOV linkage, and the governing data source name for condition rendering.

Common Use Cases and Queries

Typical scenarios include populating the condition LOV in the Audience Workbench query template builder, validating that a field belongs to an enabled source type, and extracting the physical source object and column needed to generate dynamic query SQL.

SELECT id, name, master_src_type_id, src_fld_id, disabled
FROM   apps.ams_query_tmpl_condition_vl
WHERE  disabled = 'N'
ORDER BY master_src_type_id, name;

SELECT v.name, v.source_column_name, v.field_data_type, v.data_source_name
FROM   apps.ams_query_tmpl_condition_vl v
WHERE  v.master_src_type_id = :p_master_src_type_id
AND    v.disabled = 'N';

Because the view already filters on ENABLED_FLAG = 'Y', any query built against it returns only active source types and fields, removing the need for callers to re-check enabled status.