Results for “query_alias_id”

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

Overview

AMS_QUERY_ALIAS is a table owned by the AMS schema within the Oracle E-Business Suite Marketing (AMS) module. It stores information about the data source that was cloned through a self-join operation in Audience Workbench. Audience Workbench is the component of Oracle Marketing used to define and manage audience segmentation queries; when a user clones or aliases a data source so that it can be referenced multiple times within a single query construct, the relationship between the original source and its alias is persisted in this table.

From a Data Vault modeling perspective, the mined relationship structure classifies AMS_QUERY_ALIAS as a standalone object. This heuristic reflects that the table does not sit at the center of a heavily referenced hub-and-link web; instead it functions as a self-referencing metadata store in which one row may point to a parent alias row via PARENT_QUERY_ALIAS_ID. In practice this makes the table serve as a lightweight hub for alias identity, with its descriptive and audit attributes behaving satellite-like. The classification is a modeling suggestion rather than an enforced EBS construct.

Key Information Stored

The table contains 16 documented columns in the ETRM 12.2.2 physical schema. The most significant are:

• QUERY_ALIAS_ID — the surrogate primary key of the table (constraint AQA_PK) and the unique identifier for each query alias record.
• ZD_EDITION_NAME — the editioning column. Together with QUERY_ALIAS_ID it forms the unique index AMS_QUERY_ALIAS_U1, the documented business-key candidate. This composite allows the same logical alias to exist under different edition contexts.
• TEMPLATE_ID — identifies the Audience Workbench template or query definition context to which the alias belongs.
• OBJECT_NAME — the name of the underlying data source object (for example, a table, view, or business object) that was cloned.
• ALIAS_SEQ — the sequence number that disambiguates multiple aliases of the same source within one query, supporting the self-join scenario.
• PARENT_QUERY_ALIAS_ID — a self-referencing pointer to the alias from which this row was derived, capturing the clone lineage.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the EBS framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard EBS who-columns providing audit history.
• LAST_UPDATE_LOGIN — the login context of the most recent modification.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program context columns populated when the row is created or refreshed by a batch process rather than online.

The distinction between the surrogate key (QUERY_ALIAS_ID alone) and the business-key candidate (QUERY_ALIAS_ID plus ZD_EDITION_NAME) matters for joins: consumers joining across edition-aware objects should include ZD_EDITION_NAME to avoid multiplying rows across editions.

Common Use Cases and Queries

Typical usage centers on reconstructing or auditing how an audience query was built. A common pattern resolves the alias lineage for a given query template:

• Reporting which data sources were cloned for a segmentation effort, by joining AMS_QUERY_ALIAS to the query definition tables on TEMPLATE_ID.
• Tracing alias lineage via the self-join on PARENT_QUERY_ALIAS_ID to build the full clone tree.
• Identifying rows created by concurrent programs versus online users through REQUEST_ID and PROGRAM_ID.

A representative query resolves alias names for a template:

• SELECT a.QUERY_ALIAS_ID, a.OBJECT_NAME, a.ALIAS_SEQ, a.PARENT_QUERY_ALIAS_ID, p.OBJECT_NAME AS PARENT_OBJECT FROM AMS.AMS_QUERY_ALIAS a LEFT JOIN AMS.AMS_QUERY_ALIAS p ON a.PARENT_QUERY_ALIAS_ID = p.QUERY_ALIAS_ID WHERE a.ZD_EDITION_NAME = (SELECT ZD_EDITION_NAME FROM AMS.AMS_QUERY_ALIAS WHERE QUERY_ALIAS_ID = :id);

Reporting should always filter on the active edition to avoid stale or duplicate edition rows, and should account for NULL PARENT_QUERY_ALIAS_ID values indicating root aliases.

Related Objects

The documented foreign-key relationship shows that AMS_QUERY_COND_DISP_STRUCT_ALL references this table through QUERY_ALIAS_ID, meaning the display structure of query conditions depends on the alias definition. The most significant related objects are:

• AMS_QUERY_COND_DISP_STRUCT_ALL — joins on QUERY_ALIAS_ID to link query condition display structures to their source alias.
• AMS_QUERY_ALIAS (self) — joins on PARENT_QUERY_ALIAS_ID = QUERY_ALIAS_ID to traverse clone lineage.
• Template and query definition tables in the AMS schema, joined on TEMPLATE_ID, which provide the Audience Workbench context.
• EBS concurrent program tables (FND_CONCURRENT_REQUESTS) joined on REQUEST_ID, for batch provenance.
• FND_USER joined on CREATED_BY and LAST_UPDATED_BY for audit attribution.

These relationships position AMS_QUERY_ALIAS as the identity anchor for aliased data sources referenced by downstream query condition structures.