Search Results ams_campaigns_vl




Overview
Explore in AI Chat

Overview

AMS_CAMPAIGNS_VL is a marketing campaign view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. It is a "_VL" view, meaning it is a "view with translation" join that overlays language-specific descriptive columns on top of the base campaign entity table. The view exposes the complete logical record of a campaign — its identity, status, organizational ownership, budget and revenue figures, schedule dates, channel and media assignments, and the descriptive name, theme, and description held in the translation layer.

Because it presents campaign header information in a single, denormalized read structure, AMS_CAMPAIGNS_VL serves as the primary reference point for reporting, custom concurrent programs, Discoverer worksheets, BI Publisher data models, and integration interfaces that must read or reconcile campaign master data. It is also the standard view used by Oracle's own marketing forms and by API-based extraction logic where a multi-language view is required rather than the underlying base table.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over two synonym objects:

  • AMS_CAMPAIGNS_ALL_B — the base table holding language-independent campaign attributes (identified in the view text by alias B).
  • AMS_CAMPAIGNS_ALL_TL — the translation table holding language-dependent descriptive columns (identified by alias T).

The view text joins the two on the campaign identifier and exposes CAMPAIGN_NAME, CAMPAIGN_THEME, and DESCRIPTION from the _TL table, with the remainder of the columns sourced from the _B table. The ROWID of the _B row is surfaced as ROW_ID. In 12.1.1 and 12.2.2 the object is documented as VALID and is not editioned; it behaves consistently across both releases.

Key Columns

Common Use Cases and Queries

Typical uses include campaign performance dashboards, budget-versus-actual variance reports, and extraction of campaign metadata into external marketing automation or data-warehouse systems.

SELECT c.campaign_id,
       c.campaign_name,
       c.status_code,
       c.campaign_type,
       c.forecasted_plan_start_date,
       c.forecasted_plan_end_date,
       c.budget_amount_fc,
       c.actual_cost,
       (c.actual_cost - c.budget_amount_fc) AS variance
FROM   apps.ams_campaigns_vl c
WHERE  c.org_id = :p_org_id
AND    c.active_flag = 'Y'
ORDER  BY c.campaign_name;

For response analysis, aggregate actual against target response by campaign type:

SELECT c.campaign_type,
       SUM(c.target_response)       AS target_resp,
       SUM(c.actual_response)       AS actual_resp,
       SUM(c.actual_revenue)        AS revenue
FROM   apps.ams_campaigns_vl c
WHERE  c.actual_exec_start_date BETWEEN :p_from AND :p_to
GROUP  BY c.campaign_type;

Because the view resolves translation rows, queries executed in a specific language session return the appropriate campaign name and description automatically. Reporting logic should always filter on ORG_ID for multi-org compliance and join to AMS_CAMPAIGN_SCHEDULES or AMS_DELIVERABLES on CAMPAIGN_ID when schedule-level or deliverable-level detail is required.