Search Results ams_list_queries_all_s

Overview

APPS.AMS_LIST_QUERIES_NEW_PKG is a supporting PL/SQL package body in the Oracle E-Business Suite Marketing (AMS) application. It provides the programmatic interface for maintaining the list query definition records that underlie Oracle Marketing list generation and audience selection functionality. In the AMS data model, list queries define the criteria and selection logic used to build marketing lists, and their descriptive text is stored in a translation-enabled (TL) table so that seeded and user-defined query names and descriptions can be presented in multiple languages.

The package body is documented with a status of VALID and is owned by the APPS schema. It is classified as an OTHER API rather than a public or business API, which indicates that it is primarily an internal maintenance and conversion utility. It is not referenced by any other database object, confirming that it sits at the leaf of the dependency tree and is invoked directly by external callers rather than being called from other PL/SQL units. The package follows the standard Oracle Applications multi-language support (MLS) design pattern used across EBS, in which each logical row has a base (non-translated) record in an _ALL table, a corresponding _ALL_S sequence-backed surrogate identifier row, and one or more translation rows in a _TL table.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions within the package body. Their names and roles are as follows:

• INSERT_ROW — Creates a new list query definition record in the base table and inserts the corresponding translated row for the current session language.
• LOCK_ROW — Acquires a row-level lock on an existing list query record, typically to serialize concurrent updates or deletions and to enforce optimistic locking controls.
• UPDATE_ROW — Modifies an existing list query definition, updating both the base record and the associated translation row.
• DELETE_ROW — Removes a list query definition together with its translation and surrogate identifier records.
• LOAD_ROW — Inserts or updates a list query record from an external source, commonly used by seed data loaders, data conversion scripts, or patch drivers to populate or refresh list query definitions.
• ADD_LANGUAGE — Adds a translation row for an additional installed language, supporting the MLS requirement that every translatable entity have a row in each enabled language.
• TRANSLATE_ROW — Updates the translatable columns (such as name and description) of an existing translation row for a specified language.

No explicit parameter lists are documented in the ETRM metadata, so only the intended purpose of each routine is asserted here.

Tables Accessed

The following tables, exposed to the package through APPS synonyms, are referenced:

• AMS_LIST_QUERIES_ALL — The base table holding list query definitions, including the non-translated attributes and the foreign key to the surrogate key.
• AMS_LIST_QUERIES_ALL_S — The sequence-backed surrogate key table that provides the primary key for each list query record. The user's search term, ams_list_queries_all_s, maps directly to this object.
• AMS_LIST_QUERIES_TL — The translation table storing language-specific query names and descriptions.
• FND_LANGUAGES — The Oracle Applications installed-languages view, queried by the language maintenance routines (ADD_LANGUAGE, TRANSLATE_ROW) to determine which languages are installed and active.
• DUAL — Used for single-row expressions and sequence value retrieval.

Usage Notes

Because the package is not referenced by any other database object, invocation is direct. Typical callers include Oracle Forms-based maintenance screens within Oracle Marketing, concurrent programs that seed or migrate list query definitions, and custom or upgrade scripts that need to install list queries across multiple languages. The standard MLS sequence is to call INSERT_ROW or LOAD_ROW for the base definition, then ADD_LANGUAGE and TRANSLATE_ROW for each additional installed language identified from FND_LANGUAGES. Applications code should treat this package as an internal utility rather than a supported public API, and any custom integration should account for the base, surrogate, and translation tables in a single logical transaction.