Search Results ams_list_query_pvt




Overview
Explore in AI Chat

Overview

APPS.AMS_LIST_QUERY_PVT is a private (PVT) PL/SQL package in the Oracle E-Business Suite Marketing (AMS) module. It encapsulates the core business logic used to create, maintain, validate, and copy list query definitions stored by Oracle Marketing. A "list query" is the stored SQL-based selection criterion that drives list generation — the process by which the marketing application identifies a population of customers, prospects, or contacts for a campaign, event, or promotion. Because it is a private package, AMS_LIST_QUERY_PVT is not intended to be called directly by external clients; it is invoked by the public API layer and internal marketing components that manage list query records.

The package is declared AUTHID CURRENT_USER, so its SQL executes under the privileges of the invoking session rather than the package owner. The header carries the version annotation 115.10 and reflects the automatic record-generation conventions used throughout the AMS schema, wherein the full column set of AMS_LIST_QUERIES_ALL is mirrored into a PL/SQL record type (list_query_rec_type) with each attribute defaulted to the corresponding FND_API.G_MISS_* sentinel.

Key Procedures and Functions

The package exposes twelve documented program units. The principal entry points are:

  • CREATE_LIST_QUERY — Inserts a new list query definition, deriving identifiers and audit columns through the standard AMS sequence and WHO-column conventions.
  • UPDATE_LIST_QUERY — Modifies the attributes of an existing list query, including its name, type, enabled flag, and SQL text.
  • DELETE_LIST_QUERY — Removes a list query record, typically following dependency checks against downstream list definitions.
  • DELETE_PARENT_LIST_QUERY — Handles removal of a parent list query together with associated child or dependent records.
  • LOCK_LIST_QUERY — Acquires a row-level lock on the target list query to enforce concurrency control during transactional updates.
  • VALIDATE_LIST_QUERY — Performs business-rule validation on the list query, confirming required attributes and structural integrity before persistence.
  • VALIDATE_LIST_QUERY_REC — Applies the same validation logic directly to an in-memory list_query_rec_type record, supporting validation prior to a DML call.
  • CHECK_LIST_QUERY_ITEMS — Verifies the items or parameters associated with a list query, confirming that referenced selection objects remain valid.
  • COPY_LIST_QUERIES — Duplicates one or more existing list query definitions, the operation most commonly associated with the search term "copy_list_queries." This routine is the mechanism by which a marketing user replicates a proven query for reuse across campaigns without reconstructing its SQL manually.

Tables Accessed

  • AMS_LIST_QUERIES_ALL — The primary transactional table holding list query headers; read and written by the create, update, delete, copy, and validation routines.
  • AMS_LIST_QUERIES_ALL_S — The sequence used to generate list_query_id values for newly created or copied records.
  • AMS_LIST_QUERIES_PARAM and AMS_LIST_QUERIES_PARAM_S — Store and sequence the parameter rows associated with a list query; copied during COPY_LIST_QUERIES.
  • AMS_LIST_SELECT_ACTIONS — Records the selection actions tied to a list query, consulted by CHECK_LIST_QUERY_ITEMS.
  • DUAL — Used for sequence and scalar lookups.
  • PLITBLM — The standard Apps PL/SQL index-by table of character values, used for bulk parameter passing and ID collections.

Usage Notes

Because AMS_LIST_QUERY_PVT is a private package, it is normally reached indirectly. The Oracle Marketing forms that maintain list queries call the corresponding public API, which in turn delegates to these routines. Referential integrity requires that supporting rows in AMS_LIST_QUERIES_PARAM and AMS_LIST_SELECT_ACTIONS be handled in the same transaction as the parent list query, which is why delete, copy, and validation logic is centralized here rather than duplicated in the calling modules. The package is referenced by nine other packages in the AMS schema, confirming its role as a shared internal service. Custom extensions should avoid direct invocation of the private API; where list query replication is required, custom code should prefer the supported public interface or clearly document its reliance on this package, since the signature is subject to change between point releases.