Results for “ams_act_offers”

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

Overview

AMS_ACT_OFFERS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that holds the high-level details of promotional offers associated with a campaign. It serves as the master repository for offer definition, capturing the identity, classification, validity window, qualifying date ranges, and monetary amount of each promotional offer used within campaign activities. The table is owned by the AMS schema and carries a documented status of VALID, with 29 physical columns in the ETRM 12.1.1 schema.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is hub-leaning. AMS_ACT_OFFERS functions principally as a hub because its primary key (ACTIVITY_OFFER_ID) is a system-generated surrogate identifier that other transactional and rule tables reference, rather than an assembly of natural composite keys. Modelers treating this object in a Data Vault or similar architecture may therefore position it as a hub, with the offer's descriptive attributes (status, dates, amounts) flowing into a linked satellite. The table also exhibits several outgoing foreign keys to other hub-style entities, which reinforces its role as a central reference point for campaign promotions.

Key Information Stored

The most significant columns documented for AMS_ACT_OFFERS are:

• ACTIVITY_OFFER_ID — the surrogate primary key (AMS_ACT_OFFERS_PK) and the single unique-index candidate (AMS_ACT_OFFERS_U1). All downstream relationships to campaign schedules, rules, triggers, and leads join on this value.
• ACT_OFFER_USED_BY_ID — foreign key to AMS_CAMPAIGNS_ALL_B, identifying the campaign that owns or uses the offer.
• QP_LIST_HEADER_ID — foreign key to QP_LIST_HEADERS_B, linking the promotional offer to the Advanced Pricing price list header used to qualify or value the offer.
• ARC_ACT_OFFER_USED_BY and PRIMARY_OFFER_FLAG — flags identifying the owning activity and whether the offer is the primary offer for that activity.
• OFFER_TYPE and OFFER_CODE — the classification and user-facing code for the offer.
• ACTIVE_PERIOD_SET, ACTIVE_PERIOD, START_DATE, and END_DATE — control the offer's active validity window.
• ORDER_DATE_FROM/TO, SHIP_DATE_FROM/TO, PERF_DATE_FROM/TO — qualifying order, shipment, and performance date ranges that determine eligibility.
• STATUS_CODE and STATUS_DATE — the current lifecycle state of the offer and the timestamp that state was set.
• OFFER_AMOUNT and LUMPSUM_PAYMENT_TYPE — the monetary value and payment disposition of the offer.
• SECURITY_GROUP_ID and CUSTOM_SETUP_ID — foreign keys to FND_SECURITY_GROUPS and AMS_CUSTOM_SETUPS_B, supporting multi-org security and custom setup inheritance.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — the standard WHO audit columns.

Common Use Cases and Queries

The table is most commonly queried when reporting on campaign promotional activity, validating which offers were active during a given period, or reconciling offers against pricing and lead data. A typical reporting join retrieves the offer with its parent campaign and price list:

SELECT o.ACTIVITY_OFFER_ID, o.OFFER_CODE, o.OFFER_TYPE, o.STATUS_CODE, o.OFFER_AMOUNT, c.CAMPAIGN_NAME, l.NAME AS PRICE_LIST
FROM AMS.AMS_ACT_OFFERS o
LEFT JOIN AMS.AMS_CAMPAIGNS_ALL_B c ON c.CAMPAIGN_ID = o.ACT_OFFER_USED_BY_ID
LEFT JOIN QP.QP_LIST_HEADERS_B l ON l.LIST_HEADER_ID = o.QP_LIST_HEADER_ID
WHERE o.STATUS_CODE = 'ACTIVE';

Date-range filtering against START_DATE and END_DATE is standard when determining offer eligibility, while ORDER_DATE_FROM/TO, SHIP_DATE_FROM/TO, and PERF_DATE_FROM/TO drive qualification logic. Analysts tracing offer performance frequently join to AMS_CAMPAIGN_SCHEDULES and AMS_TRIGGER_ACTIONS on ACTIVITY_OFFER_ID to establish where and when an offer was presented, and to AS_SALES_LEADS or AS_LEAD_LINES_ALL on OFFER_ID to evaluate lead generation attributable to the offer.

Related Objects

The following are the most significant related objects, each joined on the documented key columns:

• AMS_CAMPAIGNS_ALL_B — referenced by AMS_ACT_OFFERS.ACT_OFFER_USED_BY_ID; supplies the parent campaign.
• QP_LIST_HEADERS_B — referenced by AMS_ACT_OFFERS.QP_LIST_HEADER_ID; provides the pricing list definition.
• FND_SECURITY_GROUPS — referenced by AMS_ACT_OFFERS.SECURITY_GROUP_ID; enforces security grouping.
• AMS_CUSTOM_SETUPS_B — referenced by AMS_ACT_OFFERS.CUSTOM_SETUP_ID; supplies custom setup metadata.
• AMS_CAMPAIGN_SCHEDULES — references AMS_ACT_OFFERS.ACTIVITY_OFFER_ID; ties offers to scheduled campaign activity.
• AMS_IBA_CAMPAIGN_RULES — references AMS_ACT_OFFERS.ACTIVITY_OFFER_ID; governs rule-driven offer behavior.
• AMS_TRIGGER_ACTIONS — references AMS_ACT_OFFERS.ACTIVITY_OFFER_ID; links offers to triggered actions.
• AS_SALES_LEADS, AS_LEAD_LINES_ALL, and AS_SALES_LEAD_LINES — reference AMS_ACT_OFFERS.OFFER_ID; connect sales leads and lead lines back to the originating offer.