Results for “ams_query_template_all”

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

Overview

The AMS_QUERY_TEMPLATE_ALL table resides in the AMS (Marketing) schema and stores query template definitions used by the Oracle Marketing Audience Workbench. In Oracle EBS 12.1.1 and 12.2.2, this table underpins the segmentation and audience selection capabilities of the Advanced Marketing module, where marketers define reusable query criteria that filter customer, prospect, and contact populations for campaigns. Each row represents a single query template configuration, including the source objects, sampling parameters, and materialized view artifacts associated with the template.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone, which suggests it behaves as an independent reference or configuration entity rather than a transactional link or dependent satellite. Its single documented foreign key to FND_SECURITY_GROUPS indicates that security partitioning is applied at the multi-org/security-group level, reinforcing its role as a controlled configuration reference rather than a join-heavy fact table.

Key Information Stored

The table contains 26 documented columns. The most significant include:

• TEMPLATE_ID — Surrogate primary key uniquely identifying each query template.
• ZD_EDITION_NAME — Editioning column supporting Oracle EBS 12.2 online patching; combined with TEMPLATE_ID it forms the unique business key documented as AMS_QUERY_TEMPLATE_ALL_U1 (TEMPLATE_ID, ZD_EDITION_NAME).
• TEMPLATE_TYPE — Classifies the type of query template defined in Audience Workbench.
• LIST_SRC_TYPE — Indicates the source list type used by the template.
• VIEW_APPLICATION_ID — Identifies the application owning the source view used by the query.
• MV_NAME and MV_AVAILABLE_FLAG — Name and availability status of the materialized view created to support the template.
• SAMPLE_PCT and SAMPLE_PCT_RECORDS — Sampling percentage parameters applied when selecting audience records.
• MASTER_DS_REC_NUMBERS — Stores record numbers for the master data source, supporting audience recalculation.
• RECALC_TABLE_STATUS — Tracks the status of the recalculation table process.
• AQE_ID — Reference to the Advanced Query Engine identifier.
• SEEDED_FLAG and ENABLED_FLAG — Seed data indicator and enablement status.
• IN_USE_FLAG — Indicates whether the template is currently in active use.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS for row-level security.
• OBJECT_VERSION_NUMBER — Supports optimistic locking and concurrent update control.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — Standard concurrent program audit columns.

Common Use Cases and Queries

Typical use cases include auditing active query templates, verifying materialized view availability, and reporting on sampling configuration. A common reporting query lists enabled templates with their security group context:

SELECT qt.template_id, qt.template_type, qt.mv_name, qt.enabled_flag, qt.security_group_id FROM ams.ams_query_template_all qt WHERE qt.enabled_flag = 'Y';

A join to security groups resolves partition ownership:

SELECT qt.template_id, fsg.security_group_name FROM ams.ams_query_template_all qt, fnd_security_groups fsg WHERE qt.security_group_id = fsg.security_group_id;

Administrators also query by MV_AVAILABLE_FLAG to diagnose stale or missing materialized views that can block campaign audience generation.

Related Objects

• FND_SECURITY_GROUPS — Joined via SECURITY_GROUP_ID; controls security partitioning.
• AMS_QUERY_TEMPLATE_CRITERIA — Child table storing query criteria lines per template.
• AMS_QUERY_TEMPLATE_SOURCES — Child table defining data sources per template.
• AMS_AUDIENCES_ALL — References templates during audience definition.
• AMS_CAMPAIGNS_ALL — Campaigns consume audiences built from templates.
• AMS_LIST_HEADERS — List sources tied to LIST_SRC_TYPE.
• FND_APPLICATION — Resolves VIEW_APPLICATION_ID and PROGRAM_APPLICATION_ID.
• FND_CONCURRENT_REQUESTS — Tracks template recalculation jobs via REQUEST_ID.