Search Results ams_mkt_segment_type




Overview
Explore in AI Chat

Overview

The APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V view is a denormalized reporting and integration view within the Oracle Marketing (AMS) module of Oracle E-Business Suite. It exposes the association between active marketing campaigns and the market segments (cells) targeted by those campaigns. The view is designed to present a flattened, read-only projection of campaign-to-segment relationships so that concurrent programs, OA Framework pages, Discoverer workbooks, and third-party integrations can query this information without navigating the normalized underlying model.

Because it is defined with a _V suffix and owned by APPS, the view is intended for consumption by custom reports, BI Publisher data templates, and Oracle Marketing's own internal processing logic rather than for direct DML. The presence of a MARKET_SEGMENT_NAME column makes it the canonical source for discovering which named market segments are attached to which campaigns — exactly the term searched by the user (market_segment_name).

Underlying Base Objects

The ETRM metadata documents four referenced base objects:

The view's source text shows an inline block that originally joined AMS_TERR_V, JTF_TERR_ALL, and territory-based segmentation, but this path is commented out. The active definition joins only the cell-based branch, so the view surfaces cell-driven segments rather than territory-hierarchy segments. Filtering restricts output to campaigns where ACTIVE_FLAG = 'Y', STATUS_CODE = 'ACTIVE', and SHOW_CAMPAIGN_FLAG = 'Y', and where the segment is used by a campaign (ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP').

Key Columns

  • CAMPAIGN_ID / CAMPAIGN_NAME — identity and description of the parent campaign.
  • SOURCE_CODE, STATUS_CODE — campaign origin and current status.
  • ACTUAL_EXEC_START_DATE / ACTUAL_EXEC_END_DATE — actual execution window for the campaign.
  • MARKET_SEGMENT_ID / MARKET_SEGMENT_NAME — the cell identifier and its translatable name; the term the user searched for maps directly to MARKET_SEGMENT_NAME.
  • SEGMENT_TYPE / SEGMENT_NAME — the stored lookup code and its decoded meaning via AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_MKT_SEGMENT_TYPE', …).
  • ELIGIBILITY_TYPE / ELIGIBILITY_NAME — the eligibility rule code and decoded description from AMS_MARKET_ELIG_TYPE.
  • EXCLUDE_FLAG — indicates whether the segment is included or excluded from the campaign audience.
  • TERR_HIERARCHY_ID / HIERARCHY_NAME — present as TO_NUMBER(NULL) and NULL, retained for structural compatibility with the territory-based variant but not populated by the active cell path.

Common Use Cases and Queries

Typical usage retrieves all active campaigns and their associated named segments:

  • Segment discovery: SELECT CAMPAIGN_NAME, MARKET_SEGMENT_NAME, SEGMENT_NAME, ELIGIBILITY_NAME FROM APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V;
  • Campaign-specific lookup: SELECT MARKET_SEGMENT_NAME, EXCLUDE_FLAG FROM APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V WHERE CAMPAIGN_ID = :p_campaign_id;
  • Search by name: SELECT CAMPAIGN_NAME FROM APPS.AMS_P_CAMPAIGN_MKT_SEGMENTS_V WHERE MARKET_SEGMENT_NAME LIKE '%' || :p_name || '%';
  • Audience composition reporting for campaigns executed within a date range, filtered on ACTUAL_EXEC_START_DATE and ACTUAL_EXEC_END_DATE.

Because the view already restricts to active, visible campaigns and campaign-driven segments, it is well suited for operational dashboards and integration extracts that require current targeting data without additional status filtering.