Results for “offer_code”

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

Overview

The AMS_ASO_CAMPAIGN_OFFERS_V view is a reporting and integration object in the Oracle E-Business Suite Marketing (AMS) product family. It exposes campaign-level offer records by joining Oracle Advanced Pricing offer definitions with their associated price list headers. The view is owned by the APPS schema and is marked VALID in both Oracle EBS 12.1.1 and 12.2.2 reference environments.

Its principal role is to present a denormalized, business-friendly projection of offers that are used specifically in the context of marketing campaigns. Because the view filters the underlying offer table on ARC_ACT_OFFER_USED_BY = 'CAMP', only offers tagged as campaign offers are returned, excluding offers used by other activity types. This makes it a convenient source for campaign offer reports, integration extracts, and custom concurrent programs that need offer metadata alongside pricing list attributes such as description, currency, and validity dates.

The view also surfaces the LUMPSUM_PAYMENT_TYPE column, which relates directly to the search term "lumpsum_payment_type." This column is populated from the underlying AMS_ACT_OFFERS table and indicates the payment type associated with lump-sum offers, a configuration used in campaign promotion and rebate scenarios.

Underlying Base Objects

The view is defined over two documented base objects:

• AMS_ACT_OFFERS (referenced via the synonym AMS_ACT_OFFERS, aliased OFR) — the activity offer table that stores campaign and activity offer records.
• QP_LIST_HEADERS_VL (aliased QPL) — the Oracle Advanced Pricing price list headers view, providing list-level attributes such as description, currency, and active dates.

The join condition is OFR.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID, linking each offer to its parent price list. The filter OFR.ARC_ACT_OFFER_USED_BY = 'CAMP' restricts output to offers flagged for campaign use. Because QP_LIST_HEADERS_VL is itself a view, the joined result translates many list-level fields (for example, DESCRIPTION, CURRENCY_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE, AUTOMATIC_FLAG) into the offer context.

Key Columns

• ACTIVITY_OFFER_ID — Primary identifier of the offer record in AMS_ACT_OFFERS.
• OFFER_NAME / OFFER_CODE — Descriptive name and code of the offer.
• QP_LIST_HEADER_ID — Foreign key to the associated Advanced Pricing list header.
• OFFER_CURRENCY_CODE — Currency of the linked price list.
• START_DATE_EFFECTIVE / END_DATE_EFFECTIVE — Active date range of the price list.
• AUTOMATIC_FLAG — Indicates whether the offer is applied automatically.
• PRIMARY_OFFER_FLAG — Marks the offer as primary for the campaign or activity.
• ORDER_DATE_FROM / ORDER_DATE_TO, SHIP_DATE_FROM / SHIP_DATE_TO, PERF_DATE_FROM / PERF_DATE_TO — Qualifying date ranges for order, shipment, and performance.
• STATUS_CODE / STATUS_DATE — Offer status and the date it was set.
• OFFER_AMOUNT — Monetary or qualifying amount associated with the offer.
• LUMPSUM_PAYMENT_TYPE — Payment type for lump-sum offers, relevant to campaign payout and rebate configurations.

Common Use Cases and Queries

Typical uses include listing active campaign offers, reconciling offers against price list currencies, and extracting lump-sum payment configurations. The following query returns campaign offers with their pricing list and lump-sum payment type:

SELECT ACTIVITY_OFFER_ID,
       OFFER_NAME,
       OFFER_CODE,
       OFFER_CURRENCY_CODE,
       START_DATE_EFFECTIVE,
       END_DATE_EFFECTIVE,
       OFFER_AMOUNT,
       LUMPSUM_PAYMENT_TYPE
  FROM APPS.AMS_ASO_CAMPAIGN_OFFERS_V
 WHERE STATUS_CODE = 'ACTIVE';

To isolate offers that carry a lump-sum payment definition:

SELECT ACTIVITY_OFFER_ID,
       OFFER_CODE,
       LUMPSUM_PAYMENT_TYPE,
       OFFER_AMOUNT
  FROM APPS.AMS_ASO_CAMPAIGN_OFFERS_V
 WHERE LUMPSUM_PAYMENT_TYPE IS NOT NULL;

These queries are commonly embedded in custom reports, BI Publisher data templates, and inbound/outbound integration interfaces that synchronize campaign offer data with external promotion or settlement systems.