Search Results market_segment_name




Overview
Explore in AI Chat

Overview

The AMS_P_CAMPAIGN_MKT_SEGMENTS_V view is a public, APPS-owned database view in the Oracle E-Business Suite Advanced Marketing (AMS) module. It exposes the market segments associated with active marketing campaigns, joining campaign header information to its assigned cells and segment eligibility attributes. The view is defined as a "public" view, meaning it is intended for consumption by external reporting tools, custom concurrent programs, and integration interfaces rather than being restricted to internal Marketing forms logic.

The ELIGIBILITY_NAME column, which the user searched for, is one of the more consequential outputs of this view. It is not a physical stored column but is derived at runtime via a call to AMS_UTILITY_PVT.GET_LOOKUP_MEANING against the AMS_MARKET_ELIG_TYPE lookup type, translating the stored ELIGIBILITY_TYPE code into a user-facing description. This design pattern is common in AMS views, where coded flexfield or lookup values are decoded for reporting convenience.

Underlying Base Objects

The view is constructed over four documented referenced objects:

  • AMS_ACT_MARKET_SEGMENTS (synonym) — the central transactional table holding segment-to-campaign associations, including SEGMENT_TYPE, EXCLUDE_FLAG, and ELIGIBILITY_TYPE.
  • AMS_CAMPAIGNS_VL (view) — the multi-language campaign header view supplying campaign identifiers, names, source codes, execution dates, and status.
  • AMS_CELLS_VL (view) — the multi-language cell view supplying the market segment identifier and name (aliased as MARKET_SEGMENT_ID and MARKET_SEGMENT_NAME).
  • AMS_UTILITY_PVT (package) — the utility package whose GET_LOOKUP_MEANING function decodes lookup codes into meanings.

The active SELECT joins AMS_ACT_MARKET_SEGMENTS to AMS_CAMPAIGNS_VL on ACT_MARKET_SEGMENT_USED_BY_ID = CAMPAIGN_ID and to AMS_CELLS_VL on MARKET_SEGMENT_ID = CELL_ID, filtered to rows where ARC_ACT_MARKET_SEGMENT_USED_BY = 'CAMP'. The commented-out alternative text shows a territory-based variant using AMS_TERR_V and JTF_TERR_ALL, replaced in the active definition by null placeholders for TERR_HIERARCHY_ID and HIERARCHY_NAME.

Key Columns

  • CAMPAIGN_ID, CAMPAIGN_NAME, SOURCE_CODE — campaign identity and origin.
  • ACTUAL_EXEC_START_DATE, ACTUAL_EXEC_END_DATE, STATUS_CODE — execution window and lifecycle status.
  • MARKET_SEGMENT_ID, MARKET_SEGMENT_NAME — the assigned cell identifier and its descriptive name.
  • SEGMENT_TYPE and its decoded SEGMENT_NAME — classification of the segment (decoded against AMS_MKT_SEGMENT_TYPE).
  • EXCLUDE_FLAG — indicates whether the segment is inclusion or exclusion based.
  • ELIGIBILITY_TYPE and decoded ELIGIBILITY_NAME — the raw eligibility code and its human-readable meaning from the AMS_MARKET_ELIG_TYPE lookup.
  • TERR_HIERARCHY_ID, HIERARCHY_NAME — null in the current definition, retained for backward compatibility.

Common Use Cases and Queries

Typical uses include building marketing effectiveness reports, validating campaign segmentation before execution, and feeding downstream analytics or CRM extracts. Because the view filters on ACTIVE_FLAG = 'Y', STATUS_CODE = 'ACTIVE', and SHOW_CAMPAIGN_FLAG = 'Y', only currently visible active campaigns appear.

To list active campaigns with their decoded eligibility names:

SELECT campaign_id,
       campaign_name,
       market_segment_name,
       segment_name,
       eligibility_name,
       exclude_flag
FROM   apps.ams_p_campaign_mkt_segments_v
WHERE  actual_exec_start_date >= SYSDATE
ORDER  BY campaign_name, market_segment_name;

To count segments per eligibility type:

SELECT eligibility_name,
       COUNT(*) seg_count
FROM   apps.ams_p_campaign_mkt_segments_v
GROUP  BY eligibility_name;

Because ELIGIBILITY_NAME is resolved through a PL/SQL lookup function, queries returning large result sets may incur additional overhead; filtering on the underlying ELIGIBILITY_TYPE code where possible is advisable for performance-sensitive reports.