Results for “ams_dm_target_values_b”

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

Overview

AMS_DM_TARGET_VALUES_B is a base (non-translated) table in the AMS schema belonging to the Oracle Marketing module. It stores the definition of valid target values that may be assigned to a target field within Oracle's Targeted Marketing and campaign segmentation framework. Target values represent positive values for a given target field, meaning that for any given target, the allowable or expected values against which segments, lists, and campaigns are evaluated are persisted here. The _B suffix indicates that this is the base table in a base/translation pair; user-facing, language-dependent text is held in the companion table AMS_DM_TARGET_VALUES_TL.

The metadata carries a heuristic Data Vault classification of satellite-leaning. In Data Vault terms, this is a modeling suggestion only: the table behaves like a satellite attached to a hub-like parent (AMS_DM_TARGETS_B), holding descriptive and qualifying attributes that change over time rather than representing an independent business entity. It also carries a security dimension foreign key, which reinforces its role as an attribute-bearing dependent rather than an autonomous hub.

Key Information Stored

The table contains 13 documented columns in the 12.2.2 physical schema. The most significant are:

• TARGET_VALUE_ID — the surrogate primary key (constraint AMS_DM_TARGET_VALUES_B_PK). It is also the first column of unique index AMS_DM_TARGET_VALUES_B_U1.
• TARGET_ID — foreign key to AMS_DM_TARGETS_B, identifying the parent target to which this value belongs. This is the principal join column.
• TARGET_VALUE — the actual value being defined for the target field.
• TARGET_OPERATOR — the operator applied to the target value (for example, comparison semantics used during segmentation).
• RANGE_VALUE — supports range-style target definitions, complementing TARGET_VALUE for banded criteria.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing Oracle EBS multi-org / data security access control.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/BC4J framework to detect concurrent updates.
• ZD_EDITION_NAME — editioning column introduced under the EBS 12.2 online patching (Edition-Based Redefinition) architecture; it appears in both unique indexes U1 and U2.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

The business-key candidate of interest is unique index AMS_DM_TARGET_VALUES_B_U2 (TARGET_ID, TARGET_OPERATOR, TARGET_VALUE, ZD_EDITION_NAME), which prevents duplicate value/operator combinations within a single target.

Common Use Cases and Queries

Typical usage centers on retrieving and validating the allowable values for a given target, and on joining to the translation table to surface user-visible labels.

• List all values for a target:

  SELECT tv.target_value_id, tv.target_value, tv.target_operator
  FROM   ams.ams_dm_target_values_b tv
  WHERE  tv.target_id = :target_id;

• Retrieve display text via the translation table:

  SELECT b.target_value, t.target_value_name
  FROM   ams.ams_dm_target_values_b  b,
         ams.ams_dm_target_values_tl t
  WHERE  b.target_value_id = t.target_value_id
  AND    t.language = USERENV('LANG');

• Walk from the parent target to its values:

  SELECT t.target_name, v.target_value
  FROM   ams.ams_dm_targets_b t,
         ams.ams_dm_target_values_b v
  WHERE  t.target_id = v.target_id;

• Reporting and security-scoped extracts filter on SECURITY_GROUP_ID to respect EBS data security policies.

Related Objects

• AMS_DM_TARGETS_B — parent table; joined on TARGET_ID. Defines each target field.
• AMS_DM_TARGET_VALUES_TL — translation table; joined on TARGET_VALUE_ID. Holds language-specific value descriptions.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for data security.
• AMS_DM_TARGETS_TL — translation partner of the parent target, commonly used alongside this table for descriptive reporting.
• The AMS targeted-marketing segmentation and list-building APIs consume these definitions indirectly through the target framework rather than querying the base table directly.