Results for “campaign_theme”

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

Overview

AMS_CAMPAIGNS_ALL_TL is the translation (TL) table for marketing campaigns within the Oracle Marketing (AMS) module of Oracle E-Business Suite, as documented for releases 12.1.1 and 12.2.2. It stores all translatable attributes of a campaign, most notably the campaign name, theme, and descriptive message text, for each supported language. The table is owned by the AMS schema and holds 12 documented columns.

The "_TL" suffix follows the standard Oracle EBS multilingual design pattern, in which language-independent attributes reside in a base table and language-dependent attributes are separated into a companion TL table keyed by LANGUAGE. In this case, the base table is AMS_CAMPAIGNS_ALL_B, which holds the campaign definition itself; AMS_CAMPAIGNS_ALL_TL carries the human-readable, translatable text associated with each campaign.

From a Data Vault modeling perspective, the mined FK structure suggests a satellite-leaning classification. The table is not a hub (campaign identity lives in AMS_CAMPAIGNS_ALL_B), nor does it resolve many-to-many relationships like a link. Instead, it behaves as a language-scoped satellite attached to the campaign hub, carrying descriptive, versioned-by-language attributes.

Key Information Stored

The documented columns of primary interest include:

• CAMPAIGN_ID — Surrogate foreign key to AMS_CAMPAIGNS_ALL_B, identifying the parent campaign.
• LANGUAGE — The language code for the translated row.
• CAMPAIGN_NAME — The translatable campaign name; part of the unique business key.
• CAMPAIGN_THEME — The translatable theme or headline for the campaign.
• DESCRIPTION — Long descriptive or message text for the campaign.
• SOURCE_LANG — The source language of the record, used by the translation framework.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security group partitioning.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns.

Two keys are documented. The primary key AMS_CAMPAIGNS_ALL_TL_PK is defined on (CAMPAIGN_ID, LANGUAGE), the canonical TL keying. A second unique index, AMS_CAMPAIGNS_ALL_TL_U1, is defined on (CAMPAIGN_NAME, LANGUAGE) and functions as a business-key candidate — it prevents duplicate campaign names within the same language. The metadata also references AMS_CAMPAIGNS_ALL_TL_UK1 on the same columns. Foreign keys point to AMS_CAMPAIGNS_ALL_B (via CAMPAIGN_ID) and to FND_SECURITY_GROUPS (via SECURITY_GROUP_ID).

Common Use Cases and Queries

This table is queried whenever campaign text must be presented in a specific language, or when joining campaign definitions to their localized labels. Typical scenarios include translation completeness auditing, campaign listings in user-facing reports, and cross-language searches.

Retrieve a campaign's translated name and description for a given language:

• SELECT t.campaign_id, t.campaign_name, t.campaign_theme, t.description FROM ams.ams_campaigns_all_tl t WHERE t.campaign_id = :p_campaign_id AND t.language = USERENV('LANG');

List campaigns without a translation in a target language:

• SELECT b.campaign_id FROM ams.ams_campaigns_all_b b WHERE NOT EXISTS (SELECT 1 FROM ams.ams_campaigns_all_tl t WHERE t.campaign_id = b.campaign_id AND t.language = 'FR');

Search campaigns by localized name:

• SELECT campaign_id, campaign_name FROM ams.ams_campaigns_all_tl WHERE language = USERENV('LANG') AND UPPER(campaign_name) LIKE UPPER(:pattern);

Related Objects

The objects most significant to this table are:

• AMS_CAMPAIGNS_ALL_B — Base campaign definition; joined on CAMPAIGN_ID (AMS_CAMPAIGNS_ALL_TL.CAMPAIGN_ID → AMS_CAMPAIGNS_ALL_B.CAMPAIGN_ID).
• FND_SECURITY_GROUPS — Security group reference; joined on SECURITY_GROUP_ID.
• AMS_CAMPAIGNS_ALL_VL — The multilingual view that consolidates base and TL rows for convenient querying.
• AMS_CAMPAIGN_SCHEDULES and related AMS campaign child tables — depend on the campaign identity defined by the base table and localized here.
• AMS_DELIVERABLES_ALL_TL — Companion translation table for campaign deliverables, following the identical TL pattern.
• Oracle Marketing APIs (e.g., AMS_Campaign_PUB) — create and maintain campaign records, populating the TL table through the standard translation framework.

Queries against translated campaign text should generally resolve LANGUAGE through USERENV('LANG') or the session's NLS settings to ensure the correct row is returned.