Search Results ams_listgeneration_pub_w




Overview
Explore in AI Chat

Overview

The APPS.AMS_LISTGENERATION_PUB_W package is a public wrapper over the Oracle Marketing (AMS) list generation engine. Its business function is to create marketing lists — the working sets of customers, prospects, or contacts against which campaigns, telemarketing efforts, and fulfillment activities are executed — from a caller-supplied SQL query rather than from manually curated membership. The package belongs to the AMS module's list management subsystem and is owned by APPS in a 12.1.1 / 12.2.2 environment. The _W suffix indicates a wrapper-style entry point, layered above the internal list generation implementation to expose a stable, validated public contract to external callers. The two procedures it publishes, both named CREATE_LIST_FROM_QUERY, provide the supported integration surface for building a list header and populating its membership by executing a query passed in as a PL/SQL collection of SQL fragments.

Key Procedures and Functions

The package exposes two overloads of CREATE_LIST_FROM_QUERY:

  • CREATE_LIST_FROM_QUERY (base overload) — accepts the standard API housekeeping parameters (API version, message list initialization, commit flag, validation level) together with the attributes that define the list: list name, list type, owning user, and an optional list header identifier for reuse or update. The query that drives membership is supplied through a SQL string table, and the caller also identifies the primary key column, the source object, and the master type that characterize the underlying entity being listed. Standard OUT parameters return the API status, message count, and message data for error handling.
  • CREATE_LIST_FROM_QUERY (extended overload) — identical to the base overload but adds a query parameter collection. This allows bind-style values to be passed alongside the SQL string table, enabling parameterized query text rather than literal-only statements.

Both procedures follow the Oracle EBS API conventions: p_init_msg_list, p_commit, and p_validation_level control message stack behavior, transaction control, and the depth of validation respectively; x_return_status, x_msg_count, and x_msg_data communicate the outcome. Callers are expected to check x_return_status for the standard success, unexpected error, and expected error values before proceeding.

Tables Accessed

The ETRM metadata for this package does not enumerate the base tables it references through APPS synonyms. Functionally, a list generation API of this type writes the list header record and generates list membership rows, and it reads the source objects named by p_source_object_name and p_master_type in order to resolve the keys identified by p_primary_key and materialize list entries. The absence of an explicit table list in the documentation means the internal table usage should be treated as an implementation detail; direct DML against AMS list tables is not a supported substitute for invoking this API.

Usage Notes

AMS_LISTGENERATION_PUB_W is typically invoked from Oracle Marketing forms and concurrent programs, and from custom PL/SQL integration code that needs to build lists programmatically from an existing query. Because the SQL and bind collections are passed as JTF_VARCHAR2_TABLE_4000, callers must chunk query text and parameter values into that structure. The package is referenced by zero other packages in the documented metadata, confirming it is a top-level public entry point rather than an internal building block. Custom callers should always pass a meaningful p_validation_level, honor the commit flag rather than relying on ambient transaction state, and inspect the message stack on any non-success return.