Search Results ams_list_queries_new_pkg




Overview
Explore in AI Chat

Overview

AMS_LIST_QUERIES_NEW_PKG is an APPS-owned PL/SQL package within the Oracle E-Business Suite Advanced Marketing (AMS) module. It serves as a foundational data-maintenance layer for managing list query definitions — the stored query criteria that drive marketing list generation, segmentation, and targeted campaign execution. The package encapsulates the standard EBS "table handler" pattern, exposing a consistent set of row-level operations that allow forms, concurrent programs, and external integration routines to insert, update, delete, lock, and load records into the underlying AMS_LIST_QUERIES tables without writing direct SQL against those base tables.

The package is registered as VALID in the ETRM repository and is classified as an OTHER API (not a public, business-facing API), meaning it is intended primarily for internal AMS framework consumption rather than third-party custom development. Its dependency graph is minimal: the only external reference is to SYS.STANDARD, the implicit PL/SQL package, confirming that the package operates largely self-contained against its own AMS schema objects.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions within this package:

  • INSERT_ROW — Creates a new list query record in the base table, populating primary key and descriptive columns.
  • LOCK_ROW — Acquires a row-level lock on an existing list query, typically used to enforce optimistic concurrency control between a form session and concurrent updates.
  • UPDATE_ROW — Modifies the attributes of an existing list query record.
  • DELETE_ROW — Removes a list query record from the base table.
  • LOAD_ROW — Performs an upsert-style load, commonly used by concurrent programs or data migration/seed routines to populate rows idempotently.
  • ADD_LANGUAGE — Inserts translated (TL) rows for a list query in the languages defined in FND_LANGUAGES.
  • TRANSLATE_ROW — Maintains translated descriptive columns for an existing list query, keyed by language.

Each procedure corresponds to the standard EBS table-handler lifecycle and mirrors the DML affordances of the AMS list query maintenance form.

Tables Accessed

The package references the following tables through APPS synonyms:

  • AMS_LIST_QUERIES_ALL — the primary base table storing list query definitions; target of INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOAD_ROW.
  • AMS_LIST_QUERIES_ALL_S — the sequence that generates primary keys for new list query records.
  • AMS_LIST_QUERIES_TL — the translation table holding language-specific descriptive text; populated by ADD_LANGUAGE and TRANSLATE_ROW.
  • FND_LANGUAGES — the EBS language registry, used to enumerate installed/active languages when adding translations.
  • DUAL — the standard single-row utility table used for sequence lookups.

The read/write pattern is transactional: the _ALL table stores the canonical row, the _TL table stores per-language text, and the _S sequence supplies surrogate keys.

Usage Notes

AMS_LIST_QUERIES_NEW_PKG is invoked indirectly through the AMS list query maintenance form and any concurrent program that seeds or migrates list query metadata. Custom code should generally avoid calling it directly; the AMS public API layer is preferred for external integration. Because the package is registered as OTHER, Oracle Support treats its signature as an internal implementation detail that may change between patch levels — custom callers assume upgrade risk. The package is referenced by no other PL/SQL packages in the ETRM graph, indicating a leaf dependency with no upstream consumers inside the database. Standard EBS concurrent manager profile options (FND: Debug Log, FND: Log Enabled) apply if instrumentation is enabled. As with all AMS DML packages, callers should manage transaction boundaries carefully and commit only after a full logical unit of work, since ADD_LANGUAGE and TRANSLATE_ROW span both base and TL tables.