Search Results lock_list_query

Overview

AMS_LIST_QUERY_PUB is a public PL/SQL API package owned by the APPS schema within the Oracle E-Business Suite Marketing (AMS) product family. Its purpose is to provide a programmatic interface for the creation and maintenance of list query definitions used by Oracle Marketing list management. The package follows the standard Oracle EBS API design conventions defined in FND_API, employing the _PUB public wrapper pattern that separates the externally callable interface from the underlying private implementation package, AMS_LIST_QUERY_PVT. The header comment block identifies the source file as amspliqs.pls, with a version history dating to 2002/11/22 (revision 115.4), indicating this package has been part of the AMS schema from the early releases of the product and is retained across both 12.1.1 and 12.2.2. The package declares AUTHID CURRENT_USER, meaning it executes with the privileges of the calling user rather than the definer, which is significant when invoking it from custom code or concurrent programs.

Key Procedures and Functions

The ETRM documentation records seven documented procedures and functions within AMS_LIST_QUERY_PUB. The central API is CREATE_LIST_QUERY, which accepts a list query record (list_query_rec_type defined in AMS_LIST_QUERY_PVT) and returns the newly assigned x_list_query_id. It follows the standard EBS API signature with p_api_version_number, p_init_msg_list, and p_commit as IN parameters, and x_return_status, x_msg_count, and x_msg_data as OUT parameters. UPDATE_LIST_QUERY modifies an existing list query definition. DELETE_LIST_QUERY removes a list query, while DELETE_PARENT_LIST_QUERY handles the removal of parent-level list query records, reflecting the hierarchical structure of marketing lists. LOCK_LIST_QUERY obtains a lock on a list query record, supporting concurrency control when multiple sessions manipulate the same definition. The package also exposes G_DEFAULT_NUM_REC_FETCH, a global constant set to 30, which governs the default number of records fetched per call in query operations. Note that the documented procedure list exceeds the five named entries above, indicating additional internal or supporting routines exist in the published specification.

Tables Accessed

The ETRM metadata for this package does not enumerate the underlying tables directly. As a list query definition API within the AMS schema, it operates against the marketing list query tables owned by APPS, accessed through APPS synonyms in accordance with EBS standards. The package is documented as being referenced by one other package, which confirms it functions as a dependency within the broader AMS list management framework rather than as a standalone utility.

Usage Notes

AMS_LIST_QUERY_PUB is typically invoked from Oracle Marketing forms, from concurrent programs that build or refresh list query definitions, and from custom PL/SQL code developed to integrate external systems with Oracle Marketing. Because the package is classified as PUB and uses the FND_API message stack, callers must supply a valid API version number and should inspect x_return_status and the message count/data outputs to detect and handle errors. The p_commit parameter controls whether the API issues a database commit or leaves transaction control to the caller, allowing the package to participate in larger unit-of-work transactions. Given the AUTHID CURRENT_USER declaration, execute privileges and any dependent object access are evaluated against the invoking schema, so custom code should be granted appropriate privileges on the package and its associated objects before invocation.