Results for “perf_date_from”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_P_CAMPAIGN_OFFERS_V is a public Oracle EBS view owned by the APPS schema within the AMS (Marketing) product family. Its documented purpose is to return the promotional offers associated with campaigns, presenting a consolidated, denormalized record that links each campaign to the offers and qualifying price lists configured against it. Because it is a public view, it is intended for use by reporting tools, custom concurrent programs, and integration interfaces rather than being an internal implementation object. The view is listed as VALID and is available in both Oracle EBS 12.1.1 and 12.2.2, with documented metadata captured under ETRM 12.2.2.

The view is particularly relevant to users searching on the column lumpsum_amount, since LUMPSUM_AMOUNT is one of the monetary attributes it exposes. This makes the view a convenient single source for campaign-and-offer reporting that includes lump-sum payment amounts without requiring joins to multiple AMS and QP tables.

Underlying Base Objects

The view text joins several documented base objects:

• AMS_ACT_OFFERS (synonym) — the activity offer linkage table, aliased as OFR; provides the offer-to-campaign association.
• AMS_OFFERS (synonym) — the offer definition table, aliased as OFFER; supplies offer code, type, amount, performance dates, and lump-sum attributes.
• AMS_CAMPAIGNS_VL (view) — aliased as CAM; supplies campaign name, execution dates, source, status, and active flags.
• QP_LIST_HEADERS_VL (view) — aliased as QPL; supplies the qualifying price list header, currency, and active date ranges.
• AMS_UTILITY_PVT (package) — used via GET_LOOKUP_MEANING to translate the AMS_OFFER_TYPE lookup code into its displayed meaning.

The join is restricted to activity offers whose ARC_ACT_OFFER_USED_BY equals 'CAMP' and whose ACT_OFFER_USED_BY_ID matches the campaign ID, with the campaign active and its status ACTIVE, and where the campaign's SHOW_CAMPAIGN_FLAG is 'Y'. Each offer is also matched to its QP list header, linking the AMS and QP (Advanced Pricing) data models.

Key Columns

• ACTIVITY_OFFER_ID — unique identifier of the activity offer record.
• CAMPAIGN_ID / CAMPAIGN_NAME — the associated campaign and its name.
• CAMPAIGN_START_DATE / CAMPAIGN_END_DATE — actual execution start and end dates of the campaign.
• CAMPAIGN_SOURCE_CODE / CAMPAIGN_STATUS_CODE — source and status of the campaign.
• OFFER_NAME / OFFER_CODE / OFFER_TYPE — the offer's descriptive name, code, and translated type (via AMS_UTILITY_PVT).
• OFFER_CURRENCY_CODE — currency of the qualifying price list.
• START_DATE_EFFECTIVE / END_DATE_EFFECTIVE — active date range of the price list.
• AUTOMATIC_FLAG / PRIMARY_OFFER_FLAG / REUSABLE — behavioral flags for the offer and its price list.
• QP_LIST_HEADER_ID — the qualifying price list header key.
• ORDER_DATE_FROM/TO, SHIP_DATE_FROM/TO, PERF_DATE_FROM/TO — qualifying date ranges.
• OFFER_AMOUNT — the offer amount value.
• LUMPSUM_PAYMENT_TYPE / LUMPSUM_AMOUNT — the lump-sum payment type and lump-sum amount associated with the offer.
• STATUS_CODE / STATUS_DATE — status and status date of the offer.

Common Use Cases and Queries

Typical uses include campaign effectiveness reporting, offer auditing, and extraction of lump-sum payment values for financial or settlement analysis. A basic query listing campaign offers with their lump-sum amounts follows:

• SELECT campaign_name, offer_name, offer_code, offer_amount, lumpsum_amount FROM ams_p_campaign_offers_v WHERE lumpsum_amount IS NOT NULL;
• SELECT campaign_id, offer_name, offer_currency_code, start_date_effective, end_date_effective FROM ams_p_campaign_offers_v WHERE campaign_status_code = 'ACTIVE';
• SELECT offer_code, SUM(lumpsum_amount) FROM ams_p_campaign_offers_v GROUP BY offer_code;

Because the view already restricts to active, displayed campaigns, queries generally require no additional filtering on campaign status unless a specific subset is needed. All access should be granted through APPS synonyms to preserve supportability.