Search Results ams_campaigns_v




Overview
Explore in AI Chat

Overview

AMS_CAMPAIGNS_V is an Oracle E-Business Suite (EBS) reporting view owned by the APPS schema within the AMS (Marketing) product family. As documented in the ETRM metadata for releases 12.1.1 and 12.2.2, the view returns all marketing campaigns and related information, consolidating campaign header attributes into a single queryable interface. It is classified as a VALID database object and serves as the primary reference point for campaign data used by Marketing reporting, integrations, and downstream operational modules. The view abstracts the multilingual and multi-organization storage model of the underlying campaign tables, presenting a denormalized projection that is straightforward for concurrent programs, Oracle Reports, BI Publisher data templates, and custom SQL to consume. Because the view resolves lookup codes into their human-readable meanings inline, it reduces the join effort required by report authors and improves the readability of extracted data sets.

Underlying Base Objects

According to documented view metadata, AMS_CAMPAIGNS_V is defined over four referenced objects:

The view text confirms this structure: campaign columns are sourced from the campaign alias (CAM), the parent campaign name from a correlated reference to CTL.CAMPAIGN_NAME, user status from STAT.NAME, and decoded values via AMS_UTILITY_PVT.GET_LOOKUP_MEANING for AMS_CAMPAIGN_PURPOSE and AMS_MEDIA_TYPE lookups.

Key Columns

The view exposes a broad set of campaign attributes. Identity and audit columns include ROW_ID, CAMPAIGN_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER for concurrency control. Classification and control columns include CAMPAIGN_PURPOSE_CODE (aliased from CAMPAIGN_TYPE), MEDIA_TYPE_CODE, STATUS_CODE, STATUS_DATE, USER_STATUS_ID and its decoded USER_STATUS, ACTIVE_FLAG, PRIVATE_FLAG, PARTNER_FLAG, TEMPLATE_FLAG, SOURCE_CODE, and ROLLUP_TYPE. Hierarchical relationships are represented by PARENT_CAMPAIGN_ID and PARENT_NAME. Scheduling and financial planning columns include FORECASTED_PLAN_START_DATE, FORECASTED_EXEC_END_DATE, ACTUAL_PLAN_START_DATE, ACTUAL_EXEC_END_DATE, BUDGET_AMOUNT_TC, BUDGET_AMOUNT_FC, FORECASTED_REVENUE, ACTUAL_REVENUE, FORECASTED_COST, ACTUAL_COST, FORECASTED_RESPONSE, ACTUAL_RESPONSE, and TARGET_RESPONSE. Ten descriptive flexfield columns (ATTRIBUTE1 through ATTRIBUTE15 with ATTRIBUTE_CATEGORY) support customer extensions, and COUNTRY_CODE and LANGUAGE_CODE drive the multi-lingual resolution.

Common Use Cases and Queries

Typical uses include campaign calendar reporting, budget-versus-actual analysis, status dashboards, and integration extracts feeding external marketing automation platforms. Because the view already resolves lookups and parent names, it is well suited to direct ad hoc querying.

  • Active campaign listing: SELECT campaign_id, campaign_name, user_status, forecasted_exec_start_date FROM ams_campaigns_v WHERE active_flag = 'Y';
  • Budget analysis by purpose: SELECT campaign_purpose_code, SUM(budget_amount_tc) FROM ams_campaigns_v GROUP BY campaign_purpose_code;
  • Hierarchy traversal: query PARENT_CAMPAIGN_ID against CAMPAIGN_ID to reconstruct campaign hierarchies.
  • Extract for integration: filter on LAST_UPDATE_DATE to drive incremental loads using the audit columns exposed by the view.

Because the view returns all campaigns and related information, report authors should apply the appropriate ACTIVE_FLAG and STATUS_CODE predicates to constrain result sets in production environments.