Search Results ams_list_fields_b




Overview
Explore in AI Chat

Overview

The AMS_LIST_FIELDS_B table is a core reference data repository within the Oracle E-Business Suite Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. It functions as a master catalog of database fields that are designated as available for use in key marketing automation processes. Its primary role is to provide a centralized, controlled set of field definitions that other application components can reference. This enables standardized operations across different marketing features, ensuring consistency and integrity when handling customer and list data. The table is a base table, meaning it stores the fundamental, non-translatable attributes of a list field, and is typically joined to its corresponding translation table, AMS_LIST_FIELDS_TL, for language-specific labels.

Key Information Stored

The table's structure is designed to uniquely identify and describe a database field that can be leveraged by the application. The critical columns include the surrogate primary key, LIST_FIELD_ID, which uniquely identifies each record. The defining business keys are FIELD_TABLE_NAME and FIELD_COLUMN_NAME, which together specify the exact database column (e.g., HZ_PARTIES.PARTY_NAME) that is registered as an available field. This combination is enforced by a unique key constraint (AMS_LIST_FIELDS_B_UK1). While the provided metadata does not list all columns, typical attributes in such a table would likely include metadata like creation dates, last update dates, and enabled flags to control the active status of the field for use in various processes.

Common Use Cases and Queries

The table is primarily queried by the application to populate lists of available fields for configuration screens related to de-duplication rules, list source mapping, and list import word fields. For instance, when an administrator configures a de-duplication rule to identify duplicate leads, the application queries this table to present a selection of valid fields (like email, last name) to compare. A common reporting query might involve joining the base table with its translation table to get a user-friendly list of all enabled fields.

SELECT b.field_table_name,
       b.field_column_name,
       tl.description
  FROM ams_list_fields_b b,
       ams_list_fields_tl tl
 WHERE b.list_field_id = tl.list_field_id
   AND tl.language = USERENV('LANG')
   AND [some enabled condition];

Another critical use case is data validation; before processing a list import or applying a rule, the system can validate that the referenced field names exist in this registry.

Related Objects

AMS_LIST_FIELDS_B is a central hub with several key foreign key relationships, as documented in the ETRM metadata. The primary dependent objects are:

  • AMS_LIST_FIELDS_TL: The translation table that stores language-specific descriptions for the fields defined in the base table. It joins via LIST_FIELD_ID.
  • AMS_LIST_RULE_FIELDS: References fields used in defining list rules (likely for de-duplication), joining on the business key combination of FIELD_COLUMN_NAME and FIELD_TABLE_NAME.
  • AMS_LIST_SRC_FIELDS: References fields used in configuring list source mappings, joining on FIELD_COLUMN_NAME and FIELD_TABLE_NAME.
  • AMS_LIST_WORD_FIELDS: References fields used in list import word definitions, joining on FIELD_COLUMN_NAME and FIELD_TABLE_NAME.

These relationships underscore the table's role as the single source of truth for approved field definitions across multiple list management subsystems within Oracle Marketing.