Results for “ams_cpag_queries”

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

Overview

AMS_CPAG_QUERIES is a Marketing (AMS) module table in Oracle E-Business Suite that stores the query definitions used by Content Pages and the Data Source Fields referenced by those Content Pages within Questionnaires. In the Oracle Marketing and Advanced Product Catalog questionnaire framework, a Content Page can be configured to display dynamic content sourced from a stored query; this table is the persistence layer for those query definitions. The object resides in the AMS schema and is fully valid in both 12.1.1 and 12.2.2 releases. The documented physical schema contains twelve columns and is owned by AMS.

From a Data Vault modeling perspective, the supplied metadata classifies this object heuristically as standalone, meaning it does not sit at the center of a broad dependent FK constellation. In practice this suggests treating AMS_CPAG_QUERIES as a small reference or lookup-style structure whose identity is anchored by its own primary key rather than being a hub or link with extensive downstream satellite dependencies. The single foreign key relationship, to FND_SECURITY_GROUPS, is a security/access control reference rather than a transactional dependency.

Key Information Stored

The most significant columns in AMS_CPAG_QUERIES are:

• QUERY_ID — the surrogate primary key. It is enforced by the primary key constraint AMS_CPAG_QUERIES_PK1 and by the unique index AMS_CPAG_QUERIES_U1, making it the definitive business-key candidate for joining to dependent questionnaire and content-page structures.
• QUERY_TEXT — the textual definition of the query executed or presented on behalf of a Content Page or Data Source Field.
• QUERY_CLOB — a CLOB column that stores larger query payloads that exceed the capacity of the QUERY_TEXT column, supporting extended or complex query definitions.
• UPLOAD_DATE and EXPIRATION_DATE — lifecycle dates that indicate when the query definition was loaded and when it should no longer be considered active.
• SECURITY_GROUP_ID — the foreign key reference to FND_SECURITY_GROUPS, controlling visibility of the query across security groups and operating units.
• OBJECT_VERSION_NUMBER — the standard EBS optimistic locking column that protects concurrent updates to the row.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard WHO-column audit trail recording insert and update provenance.

Collectively these columns separate the surrogate identity (QUERY_ID) from the descriptive payload (QUERY_TEXT, QUERY_CLOB), the lifecycle metadata (UPLOAD_DATE, EXPIRATION_DATE), and the standard audit and concurrency controls.

Common Use Cases and Queries

Typical usage centers on validating that a Content Page or Data Source Field is bound to a valid, non-expired query, and on troubleshooting questionnaire rendering where a query returns no data.

• Listing active queries for a Content Page or Data Source Field, filtering out expired definitions: SELECT q.QUERY_ID, q.QUERY_TEXT, q.UPLOAD_DATE, q.EXPIRATION_DATE
FROM AMS.AMS_CPAG_QUERIES q
WHERE q.EXPIRATION_DATE IS NULL OR q.EXPIRATION_DATE > SYSDATE;
• Retrieving a query payload by primary key, using QUERY_CLOB when QUERY_TEXT is truncated: SELECT QUERY_ID, QUERY_TEXT, QUERY_CLOB
FROM AMS.AMS_CPAG_QUERIES
WHERE QUERY_ID = :query_id;
• Auditing recently maintained queries for change control: SELECT QUERY_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
FROM AMS.AMS_CPAG_QUERIES
WHERE LAST_UPDATE_DATE >= :from_date
ORDER BY LAST_UPDATE_DATE DESC;
• Filtering by SECURITY_GROUP_ID to determine which queries are reachable within a given security context.

Related Objects

The documented relationship data establishes one outbound foreign key and the primary/unique key relationships. The most significant related objects are:

• FND_SECURITY_GROUPS — joined via AMS_CPAG_QUERIES.SECURITY_GROUP_ID → FND_SECURITY_GROUPS.SECURITY_GROUP_ID to resolve security-group ownership.
• AMS_CPAG_QUERIES_PK1 — the primary key constraint on QUERY_ID, the anchor for dependent references.
• AMS_CPAG_QUERIES_U1 — the unique index on QUERY_ID, the documented business-key candidate.
• SYS_IL0000192153C00005$$ — the system LOB index supporting the QUERY_CLOB column.
• Content Page and Data Source Field definitions within the AMS questionnaire framework, which reference QUERY_ID as the binding to stored query text.