Search Results campaign_status_code




Overview
Explore in AI Chat

Overview

AMS_P_CAMPAIGN_PRODUCTS_V is a public, VALID view owned by the APPS schema in the Oracle E-Business Suite Advanced Marketing (AMS) module. It exposes the products and product categories that marketing campaigns target, joining campaign header attributes to the product/category associations held against each campaign. The view is a reporting-oriented interface: it is designed to be queried directly by custom reports, concurrent programs, Oracle Discoverer worksheets, and third-party integration extracts without requiring knowledge of the underlying AMS transaction tables.

A defining characteristic of this view is that it is pre-filtered. The view text applies four mandatory predicates, so it never returns the full universe of campaign-product associations. It returns only rows where the parent campaign has STATUS_CODE = 'ACTIVE', ACTIVE_FLAG = 'Y', and SHOW_CAMPAIGN_FLAG = 'Y', and where the association row carries MASTER_OBJECT_TYPE = 'CAMP'. Consumers therefore receive only currently live campaigns that are flagged for display.

Underlying Base Objects

The view is defined over two documented AMS views rather than base tables directly:

The two objects are joined on CAM.CAMPAIGN_ID = AAP.MASTER_OBJECT_ID, constrained by AAP.MASTER_OBJECT_TYPE = 'CAMP'. Because both sides are views, this object functions as an abstraction layer: as long as the public view signatures remain stable, the underlying AMS schema can change without breaking dependent reports.

Key Columns

  • ACTIVITY_PRODUCT_ID — primary identifier of the campaign-to-product association row.
  • OBJECT_VERSION_NUMBER — optimistic locking version stamp inherited from the association record.
  • CAMPAIGN_ID / CAMPAIGN_NAME — the parent campaign. CAMPAIGN_ID is the value joined from MASTER_OBJECT_ID.
  • CAMPAIGN_STATUS_CODE — the column most often searched for under the term "campaign_status_code." It is aliased from AMS_CAMPAIGNS_VL.STATUS_CODE and, by virtue of the view predicate, is always 'ACTIVE' in result sets. The corresponding CAMPAIGN_STATUS_DATE records when the campaign entered that status.
  • CAMPAIGN_SOURCE_CODE / CAMPAIGN_TYPE / ACTUAL_START_DATE / ACTUAL_END_DATE — campaign classification and realized execution window.
  • LEVEL_TYPE_CODE / LEVEL_TYPE — indicates whether the row targets a whole category or a specific inventory item.
  • CATEGORY_ID, CATEGORY_NAME, CATEGORY_SET_ID, CATEGORY_SET_NAME — product category hierarchy context.
  • ORGANIZATION_ID, INVENTORY_ITEM_ID, INVENTORY_ITEM_NUMBER, INVENTORY_ITEM_DESCRIPTION — the specific item targeted, where applicable.
  • EXCLUDED_FLAG — marks products explicitly excluded from the campaign's targeting.

Common Use Cases and Queries

Typical uses include campaign product eligibility extracts, promotional catalog reporting, and integration feeds that must publish only active, displayable campaigns and their targeted items.

SELECT campaign_id, campaign_name, campaign_status_code,
       level_type, inventory_item_number, category_name, excluded_flag
FROM   apps.ams_p_campaign_products_v
WHERE  campaign_status_code = 'ACTIVE'
AND    excluded_flag = 'N';

Because the view already enforces the ACTIVE and displayable-campaign predicates, filtering explicitly on CAMPAIGN_STATUS_CODE is functionally redundant but harmless, and it makes the intent of a report self-documenting. To retrieve the targeting scope for one campaign:

SELECT campaign_name, level_type, inventory_item_number,
       inventory_item_description, excluded_flag
FROM   apps.ams_p_campaign_products_v
WHERE  campaign_id = :p_campaign_id;

Reports requiring historical or inactive campaigns must bypass this view and query AMS_CAMPAIGNS_VL and AMS_ACT_PRODUCTS_V directly, since the status filter cannot be relaxed through the view.