Search Results field_data_type




Overview
Explore in AI Chat

Overview

APPS.AMS_DATASRC_ATTBINFO_VL is a database view in the Oracle E-Business Suite Oracle Marketing (AMS) module. It presents attribute-level metadata for marketing data sources, exposing the fields that can be selected when a user defines a data source, target group, or analytics query. The view is a "_VL" object, meaning it enforces language-specific (translated) display values, and it derives its contents from the marketing source type and source field configuration tables.

The view's practical role is to serve as a metadata catalog. Rather than storing transactional marketing data, it describes the columns available on each source object — their internal identifiers, display names, meanings, data types, and associated LOV identifiers. Reporting and integration components, as well as the Oracle Marketing user interface, read this view to populate attribute pickers and to resolve source field references. The SRC_FLD_ID column is central in this respect, because it identifies the specific source field record that an attribute selection maps back to.

Underlying Base Objects

The view is defined over three documented base objects:

  • AMS_LIST_SRC_TYPES_VL (VIEW) — holds the list source type definitions, including the source object name, list source name, and source type code. It supplies the object-level metadata used in both halves of the union.
  • AMS_LIST_SRC_FIELDS_VL (VIEW) — holds the individual field definitions belonging to each list source type, including the source column name, source column meaning, field data type, and attribute LOV identifier.
  • AMS_LIST_SRC_TYPE_ASSOCS (SYNONYM) — holds associations between master and sub source types, and contributes the sub source type identifier and master source type identifier.

Internally the view is a UNION ALL of two branches. The first branch returns one row per associated sub source type, built from AMS_LIST_SRC_TYPES_VL joined to AMS_LIST_SRC_TYPE_ASSOCS, with placeholder values such as a literal -1 for SRC_FLD_ID and the constant 'DUMMY' for SELECT_WHERE and FIELD_DATA_TYPE. The second branch returns one row per enabled source field, built from AMS_LIST_SRC_TYPES_VL joined to AMS_LIST_SRC_FIELDS_VL, and supplies the real SRC_FLD_ID value from LIST_SOURCE_FIELD_ID. Both branches filter on LIST_SOURCE_TYPE in ('ANALYTICS','TARGET') and on ENABLED_FLAG = 'Y', so only active analytics and target sources are exposed.

Key Columns

  • ID — the sub source type identifier in the type-level branch, or the literal -1 in the field-level branch.
  • NAME — the display name; the list source name for type rows, or the source column meaning for field rows.
  • SRC_FLD_ID — the source field identifier. This is -1 for source-type-level rows and the actual field ID for field-level rows, allowing consumers to distinguish between the two row types and to reference a specific source field.
  • MASTER_SRC_TYPE_ID — identifies the master source type to which the row belongs.
  • DISABLED — 'Y' for the type-level rows and 'N' for the field-level rows, reflecting that type rows are not directly selectable as attributes.
  • SOURCE_COLUMN_NAME and SOURCE_COLUMN_MEANING — the underlying database column and its business meaning.
  • ALIAS — the source type code used as a query alias.
  • FROM_CLAUSE — the source object name used to build the SQL from clause.
  • FIELD_DATA_TYPE and ATTB_LOV_ID — the attribute's data type and, where applicable, the identifier of the associated attribute LOV.
  • DATA_SOURCE_NAME — the list source name, useful for grouping attributes by source.
  • SELECT_WHERE — a placeholder in both branches.

Common Use Cases and Queries

Typical uses include validating which source fields exist for a given marketing source type, resolving a field meaning from a stored source field ID, and driving attribute selection in custom integrations or reports. The following query lists all enabled source fields for analytics and target sources:

SELECT id, name, src_fld_id, master_src_type_id,
       source_column_name, source_column_meaning,
       field_data_type, attb_lov_id, data_source_name
FROM   apps.ams_datasrc_attbinfo_vl
WHERE  src_fld_id > 0
ORDER  BY data_source_name, name;

To search a known source field, filter directly on the identifier:

SELECT name, source_column_name, source_column_meaning,
       field_data_type, attb_lov_id
FROM   apps.ams_datasrc_attbinfo_vl
WHERE  src_fld_id = :p_src_fld_id;

To list field-level attributes for a single master source type:

SELECT name, src_fld_id, source_column_name
FROM   apps.ams_datasrc_attbinfo_vl
WHERE  master_src_type_id = :p_master_src_type_id
AND    disabled = 'N'
ORDER  BY name;

Because the view already filters on ENABLED_FLAG and restricts source types to ANALYTICS and TARGET, consumers can rely on it returning only currently usable attribute metadata, making it a convenient and safe source for attribute-level lookups.