Search Results campaign_source_code




Overview
Explore in AI Chat

Overview

APPS.AMS_P_EVENT_CAMPAIGNS_V is a reporting view within the Oracle E-Business Suite Marketing (AMS) schema. It presents a consolidated, read-only projection that associates marketing event offers or event headers with the campaigns that reference them. The view is defined as a UNION ALL of two complementary query branches, each of which joins a campaign record to an event object through the campaign's related event identifiers. Its principal purpose is to expose campaign-to-event relationships in a single flattened structure suitable for concurrent programs, Oracle Discoverer workbooks, Business Intelligence Publisher reports, and inbound/outbound interface queries.

Because the view surfaces the event_offer_name column directly from AMS_EVENT_OFFERS_VL, users searching on the term "event_offer_name" are typically attempting to resolve which campaign a given event offer participates in, or to verify the descriptive name attached to an event offer that is referenced by a campaign's related_event_id. The view normalizes the two possible "related event" source codes so that both offer-level and header-level associations are returned without the caller needing to write a UNION manually.

Underlying Base Objects

The view is documented over three reference objects, all of which are themselves views in the APPS schema:

  • AMS_CAMPAIGNS_VL — the campaign master view, supplying campaign_id, campaign_name, description, source_code, and the related_event_id and related_event_from discriminator columns. It is joined in both branches of the UNION ALL.
  • AMS_EVENT_OFFERS_VL — the event offer view, supplying event_offer_id, event_offer_name, description, and event_object_type. It is used in the first branch.
  • AMS_EVENT_HEADERS_VL — the event header view, supplying event_header_id and event_header_name. It is used in the second branch.

The join predicate is c.related_event_id = e.event_offer_id (or h.event_header_id), restricted by c.related_event_from IN ('EVEO','EONE') in the offer branch and c.related_event_from = 'EVEH' in the header branch. This means a campaign row is only returned when its related event type matches the branch being evaluated. Columns that are not applicable to a given branch are materialized with the placeholder conversions to_number('') or to_char('') so that column datatypes align across the UNION ALL.

Key Columns

  • event_offer_id / event_offer_name / event_offer_description — identity and descriptive attributes of the event offer; populated only in the first UNION ALL branch.
  • event_object_type — classifies the offer object; populated only in the first branch.
  • event_header_id / event_header_name — identity of the event header; populated only in the second branch (EVEH).
  • campaign_id / campaign_name / campaign_description — the campaign joined to the event; populated in both branches.
  • campaign_source_code — origin/source indicator of the campaign record.
  • media_name — exposed as a placeholder to_char('') in both branches and therefore always null in the current definition.

The asymmetry of the two branches is significant: a query that filters on event_offer_name will silently exclude header-based (EVEH) campaign associations, and a query that filters on event_header_name will exclude offer-based associations.

Common Use Cases and Queries

Typical uses include reconciling campaign membership for a named event offer, producing campaign rosters for a specific event, and feeding downstream integration extracts. A basic lookup by offer name:

  • SELECT campaign_id, campaign_name, event_offer_name FROM apps.ams_p_event_campaigns_v WHERE event_offer_name = :p_name;

To retrieve all campaigns tied to any event offer or header for a given campaign source:

  • SELECT campaign_name, event_offer_name, event_header_name FROM apps.ams_p_event_campaigns_v WHERE campaign_source_code = :p_source;

To count associations per campaign, grouped by whether the link is offer-based or header-based:

  • SELECT campaign_id, campaign_name, COUNT(event_offer_id) offer_links, COUNT(event_header_id) header_links FROM apps.ams_p_event_campaigns_v GROUP BY campaign_id, campaign_name;

Because media_name is a placeholder, any reporting requirement for media context must be satisfied from another source. Queries should also account for the possibility of a campaign appearing in both branches if its related_event_from values span offer and header codes, though the discriminator column normally prevents this.