Results for “ams_list_src_types”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The AMS_LIST_SRC_TYPES table resides in the AMS schema and belongs to the Oracle Marketing (AMS) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the central reference registry for the various list source types that the Marketing application can use during list generation. Every downstream construct that requires a list source — list headers, target definitions, source field configurations, party source registrations, and import default mappings — resolves its source definition through this table. Because it holds the authoritative catalog of list source types and is referenced broadly by dependent transactional tables, it has been heuristically classified as a hub in Data Vault modeling terms. The table is edition-enabled, carrying the ZD_EDITION_NAME column, which supports the multi-tenant, multi-version semantics introduced in the EBS 12.2.x architecture and is also present in 12.1.1 with edition support.

Key Information Stored

Each row defines a single list source type together with the metadata required to execute list generation. The most consequential columns are:

• LIST_SOURCE_TYPE_ID — the surrogate primary key (AMS_LIST_SRC_TYPES_PK) that all dependent tables use as a foreign key.
• LIST_SOURCE_TYPE — the internal code identifying the source type; part of the business-key unique index U2.
• SOURCE_TYPE_CODE — a secondary classification code combined with LIST_SOURCE_TYPE to form business-key candidate LS_SRC_TYPES_U2.
• LIST_SOURCE_NAME — the display name shown to Marketing users when selecting a list source.
• SOURCE_OBJECT_NAME — the underlying database object (table/view) that supplies the source records.
• SOURCE_OBJECT_PK_FIELD — the primary key field of the source object, used to uniquely identify source rows.
• MASTER_SOURCE_TYPE_FLAG — indicates whether the type can act as a master source in source associations.
• SOURCE_CATEGORY and IMPORT_TYPE — categorize the source and describe how records are imported.
• ENABLED_FLAG and SEEDED_FLAG — status controls; SEEDED_FLAG distinguishes delivered Oracle source types from customer-defined ones.
• BASED_ON_TCA_FLAG — indicates whether the source is based on Trading Community Architecture parties.
• REMOTE_FLAG and DATABASE_LINK — support distributed sourcing through database links.
• JAVA_CLASS_NAME and VIEW_APPLICATION_ID — identify the Java implementation and owning application.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for row-level security.
• ZD_EDITION_NAME — edition discriminator, part of both unique indexes U1 and U2.

The surrogate key (LIST_SOURCE_TYPE_ID) is distinct from the business keys defined by the unique indexes U1 (LIST_SOURCE_TYPE_ID, ZD_EDITION_NAME) and U2 (LIST_SOURCE_TYPE, SOURCE_TYPE_CODE, ZD_EDITION_NAME).

Common Use Cases and Queries

Typical usage patterns include validating which list sources are enabled for list generation, resolving a source name from a foreign key, and joining dependent tables for reporting.

• Lookup of an enabled source by ID:

  SELECT lst.LIST_SOURCE_TYPE_ID, lst.LIST_SOURCE_NAME,
         lst.SOURCE_OBJECT_NAME, lst.SOURCE_CATEGORY
  FROM   AMS.AMS_LIST_SRC_TYPES lst
  WHERE  lst.ENABLED_FLAG = 'Y'
  AND    lst.LIST_SOURCE_TYPE_ID = :p_id;

• Finding all configured source types available to Marketing:

  SELECT LIST_SOURCE_TYPE, LIST_SOURCE_NAME, SOURCE_TYPE_CODE
  FROM   AMS.AMS_LIST_SRC_TYPES
  WHERE  ENABLED_FLAG = 'Y'
  AND    SEEDED_FLAG = 'Y'
  ORDER BY LIST_SOURCE_NAME;

• Reporting on list headers and their source type:

  SELECT h.HEADER_ID, lst.LIST_SOURCE_NAME
  FROM   AMS.AMS_IMP_LIST_HEADERS_ALL h,
         AMS.AMS_LIST_SRC_TYPES lst
  WHERE  h.LIST_SOURCE_TYPE_ID = lst.LIST_SOURCE_TYPE_ID;

• Identifying fields configured for a given source type via AMS_LIST_SRC_FIELDS.

Related Objects

The table participates in a dense network of foreign key relationships, both inbound and outbound, making it a pivotal hub.

• AMS_LIST_SRC_FIELDS — joins on LIST_SOURCE_TYPE_ID; defines the selectable fields for each source type.
• AMS_LIST_SRC_TYPE_ASSOCS — references this table twice via MASTER_SOURCE_TYPE_ID and SUB_SOURCE_TYPE_ID, expressing master/sub source hierarchies.
• AMS_IMP_LIST_HEADERS_ALL — joins on LIST_SOURCE_TYPE_ID; the list generation header records.
• AMS_IMP_DEFAULT_MAPPING — joins on LIST_SOURCE_TYPE_ID; supplies default field mappings during import.
• AMS_PARTY_SOURCES — joins on LIST_SOURCE_TYPE_ID; registers party-based sources.
• AMS_DM_TARGETS_B — references this table via DATA_SOURCE_ID and TARGET_SOURCE_ID.
• AMS_DM_TARGET_SOURCES — references this table via DATA_SOURCE_ID.
• FND_SECURITY_GROUPS — the outbound reference through SECURITY_GROUP_ID.
• AMS_DLG_IDENTIFY_ITEMS and EDW_BIM_LIST_SRCLSTS_LTC — additional dependent objects used in list identification and warehouse extraction.

Collectively these relationships demonstrate that AMS_LIST_SRC_TYPES serves as the master reference for source-driven list generation across the Marketing module.