Search Results ams_query_template_instance




Overview
Explore in AI Chat

Overview

AMS_QUERY_TEMPLATE_INSTANCE is a Marketing (AMS) module table that persists query template instance information used by the Oracle EBS Audience Workbench. A query template defines the criteria used to select audience members; an instance represents a concrete materialization of that template within a specific consumer context. The table enforces a simple governance model: there can be only one administrative instance per template, and every subsequent use of the template by a List or similar consumer creates a distinct end-user instance. This design allows the seeded or administrator-defined definition to remain stable while individual lists, campaigns, and segmentation runs hold independent copies that can be inspected, audited, or regenerated.

Under the heuristic Data Vault classification supplied in the ETRM metadata, this table is modeled as standalone. No parent-child hub or link relationships were inferred from the foreign key topology, so it may reasonably be treated as an independent satellite-style record keyed by its own identifier, with descriptive attributes and audit columns attached. The classification is a modeling suggestion only; the operational relationships are still enforced through the foreign keys listed below.

Key Information Stored

The table contains 18 documented columns. The most significant are:

The unique index AMS_QUERY_TEMPLATE_INSTANCE_U1 on (TEMPLATE_INSTANCE_ID, ZD_EDITION_NAME) is the documented business-key candidate; TEMPLATE_INSTANCE_ID alone acts as the operational primary key.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include auditing which lists or campaigns have instantiated a query template, verifying that only one administrative instance exists per template, and tracing which concurrent program created a given instance.

  • Count instances per template, splitting administrative from end-user records: SELECT TEMPLATE_ID, SUM(CASE WHEN ADMIN_INDICATOR_FLAG='Y' THEN 1 ELSE 0 END) admin_cnt, SUM(CASE WHEN ADMIN_INDICATOR_FLAG='N' THEN 1 ELSE 0 END) enduser_cnt FROM AMS.AMS_QUERY_TEMPLATE_INSTANCE GROUP BY TEMPLATE_ID;
  • Locate the consumer of a given instance: SELECT INSTANCE_USED_BY, INSTANCE_USED_BY_ID, CREATION_DATE FROM AMS.AMS_QUERY_TEMPLATE_INSTANCE WHERE TEMPLATE_INSTANCE_ID = :id;
  • Trace batch creation: SELECT TEMPLATE_INSTANCE_ID, REQUEST_ID, PROGRAM_ID FROM AMS.AMS_QUERY_TEMPLATE_INSTANCE WHERE PROGRAM_UPDATE_DATE > :since;
  • Verify security group assignment: join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to confirm the instance is visible within the correct operating unit context.

Because the table is standalone and modest in width, it is well suited to standard Oracle BI Publisher extracts and to ad hoc SQL against the AMS schema.

Related Objects

The following objects are the most significant dependencies for this table:

  • ASO_SUP_TMPL_INSTANCE — Referenced by AMS_QUERY_TEMPLATE_INSTANCE.TEMPLATE_INSTANCE_ID. It is the primary upstream source for instance definition and the key join path for administrative-instance lookups.
  • FND_SECURITY_GROUPS — Referenced by AMS_QUERY_TEMPLATE_INSTANCE.SECURITY_GROUP_ID; governs security-group scoping and multi-org visibility.
  • AMS_QUERY_TEMPLATES (logical) — Parent of TEMPLATE_ID, providing the template definition shared across instances.
  • AMS_LIST_HEADERS (logical) — The consumer identified by INSTANCE_USED_BY_ID when INSTANCE_USED_BY indicates a List.
  • FND_CONCURRENT_REQUESTS — Joined via REQUEST_ID to identify the batch process that created or refreshed an instance.
  • FND_APPLICATION — Joined via VIEW_APPLICATION_ID or PROGRAM_APPLICATION_ID to resolve application short names.