Results for “ams_cpag_query_usages”

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

Overview

The AMS_CPAG_QUERY_USAGES table resides in the AMS (Marketing) schema and forms part of the Oracle E-Business Suite Advanced Marketing / Campaign Planning and Administration Group (CPAG) data model. Its documented purpose is to capture query usages by different objects, such as Questions or Data Source Fields, thereby recording the relationship between a stored query definition and the business objects that consume it. This usage-tracking role makes it an associative structure that binds queries to their consumers within marketing segmentation, campaign audience building, and data-source configuration flows.

From a heuristic Data Vault modeling perspective, the FK/PK mining in the ETRM metadata classifies this object as standalone. Were it modeled under Data Vault conventions, its dependency on FND_SECURITY_GROUPS and its pattern of pairing a usage identifier with a queried-by identifier and a query identifier suggest a candidate link-style table, with the descriptive and auditing columns acting as satellite-style attributes. This classification is offered as a modeling suggestion only; the object as delivered is a conventional relational table within EBS.

Key Information Stored

• QUERY_USAGE_ID — Surrogate primary key of the table, enforced by the AMS_CPAG_QUERY_USAGES_PK1 constraint and also carried by the unique index AMS_CPAG_QUERY_USAGES_U1, making it the single documented business-key candidate.
• QUERY_ID — Reference to the underlying query definition whose usage is being recorded. This column anchors the usage record to the query that is being consumed.
• QUERY_USED_BY_TYPE — Discriminator identifying the category of object that uses the query, such as a Question or a Data Source Field.
• QUERY_USED_BY_ID — Identifier of the specific object instance (for example, a particular question or data source field) that consumes the query.
• SECURITY_GROUP_ID — Security group partitioning column, documented with a foreign key to FND_SECURITY_GROUPS; it governs multi-organization and security-group access to the usage record.
• CREATION_DATE, CREATED_BY — Standard audit columns recording when and by whom the usage record was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns capturing the most recent modification and the login context of the updater.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework to detect concurrent updates.

Common Use Cases and Queries

The primary practical use of AMS_CPAG_QUERY_USAGES is impact analysis: determining which questions, data source fields, or other marketing objects will be affected before a query is modified or deleted. A typical query lists all consumers of a given query:

• SELECT query_usage_id, query_used_by_type, query_used_by_id FROM ams.ams_cpag_query_usages WHERE query_id = :query_id;
• Reverse lookup — find which queries a specific question or data source field depends on: filter by QUERY_USED_BY_TYPE and QUERY_USED_BY_ID.
• Audit reporting — join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to present usage records under the correct security-group context.
• Housekeeping and validation — identify orphaned usage rows whose referenced query or consuming object no longer exists, and review stale records by CREATION_DATE.

Related Objects

• FND_SECURITY_GROUPS — joined on AMS_CPAG_QUERY_USAGES.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; the only documented foreign key relationship.
• AMS query definition table(s) referenced by QUERY_ID — the parent query whose definition is being consumed; join on QUERY_ID.
• Consuming marketing objects identified by QUERY_USED_BY_TYPE and QUERY_USED_BY_ID — the polymorphic child references (questions, data source fields) resolved according to the type discriminator.
• AMS_CPAG_QUERY_USAGES_PK1 / AMS_CPAG_QUERY_USAGES_U1 — the primary key constraint and unique index enforcing uniqueness on QUERY_USAGE_ID.
• FND audit and concurrency framework objects — implicit dependencies through CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.

Because the documented relationships are limited to the FND_SECURITY_GROUPS foreign key, integrators should confirm the exact parent table behind QUERY_ID and the object tables behind the QUERY_USED_BY_TYPE discriminator against the delivered AMS data model before constructing cross-module joins.