Results for “order_value_discount_type”

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

Overview

AMS_OFFERS is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that serves as a surrogate extension to QP_LIST_HEADERS_B for offer-related information. In Oracle Advanced Marketing and Trade Management, an "offer" is a promotional construct delivered to customers, and while the core list/modifier definition is stored in the Oracle Advanced Pricing (QP) schema as a list header (a modifier list), AMS_OFFERS captures the marketing-specific attributes that QP_LIST_HEADERS_B does not natively hold — most notably the offer status, performance date ranges, lumpsum offer types, and budget-related fields used for accrual and settlement of trade promotions. The relationship between the two tables is enforced through the QP_LIST_HEADER_ID foreign key, meaning every AMS_OFFERS row corresponds to exactly one QP list header and one offer code.

From a data-modelling perspective, the ETRM metadata classifies this object as satellite-leaning. In Data Vault terms, the hub would be the underlying list header (QP_LIST_HEADERS_B) and AMS_OFFERS behaves as a satellite that carries descriptive, time-aware attributes — status transitions, performance windows, and financial classification — attached to that business key. Practitioners modelling the marketing domain should treat OFFER_ID as a pure surrogate and QP_LIST_HEADER_ID or OFFER_CODE as the durable business key linking back to the hub.

Key Information Stored

The table is defined with 48 columns in the 12.1.1 physical schema, all owned by the AMS schema. The most operationally significant columns are:

• OFFER_ID — the surrogate primary key (AMS_OFFERS_PK), populated from a sequence; contains no business meaning.
• QP_LIST_HEADER_ID — foreign key to QP_LIST_HEADERS_B and the subject of the unique index AMS_OFFERS_UK2; the definitive linkage between the marketing offer and its pricing modifier.
• OFFER_CODE — the human-readable business identifier, protected by unique index AMS_OFFERS_UK1.
• OFFER_TYPE — classifies the offer (for example lumpsum versus order-value discount), which drives downstream settlement logic.
• STATUS_CODE and STATUS_DATE — the lifecycle state of the offer and the timestamp of the last transition.
• PERF_DATE_FROM / PERF_DATE_TO — the performance window during which the offer accrues.
• LUMPSUM_AMOUNT and LUMPSUM_PAYMENT_TYPE — capture fixed-fee (lumpsum) offers and how the payment is to be made, distinguishing them from per-unit discounts.
• BUDGET_AMOUNT_FC, BUDGET_AMOUNT_TC, TRANSACTION_CURRENCY_CODE, and FUNCTIONAL_CURRENCY_CODE — fund budgeting for the promotion in both transaction and functional currencies.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org / data-security access control.
• OWNER_ID, CUSTOM_SETUP_ID, USER_STATUS_ID, and ACTIVITY_MEDIA_ID — the owner resource, custom setup profile, user-defined status, and associated media, all resolved through foreign keys to AMS and JTF tables.
• OBJECT_VERSION_NUMBER, CREATION_DATE, and LAST_UPDATE_DATE — standard concurrency control and audit columns critical to any incremental extract.

Common Use Cases and Queries

Typical reporting focuses on offer status reporting, fund-consumption analysis, and reconciliation to the pricing engine. A standard listing query joins the surrogate to its pricing parent:

SELECT o.offer_code, o.offer_type, o.status_code,
       o.perf_date_from, o.perf_date_to, h.name
FROM   ams_offers o,
       qp_list_headers_b h
WHERE  o.qp_list_header_id = h.list_header_id
AND    o.status_code = 'ACTIVE';

Budget and accrual reporting compares budgeted versus consumed funds by currency:

SELECT offer_code, budget_amount_tc, transaction_currency_code, budget_offer_yn
FROM   ams_offers
WHERE  perf_date_from >= :p_from AND perf_date_to <= :p_to;

Integration scenarios use AMS_OFFERS to drive lumpsum settlement, to populate the trade-management dashboard, and to establish the offer-to-media and offer-to-custom-setup associations required for planning. Because status transitions are dated, the table also supports point-in-time analysis of which offers were active during a given accrual period.

Related Objects

The most significant objects linked to AMS_OFFERS are:

• QP_LIST_HEADERS_B — the master pricing modifier list; joined on QP_LIST_HEADER_ID and the true hub of the offer business key.
• AMS_USER_STATUSES_B — the user-defined status vocabulary, joined on USER_STATUS_ID.
• AMS_CUSTOM_SETUPS_B — custom setup profiles, joined on CUSTOM_SETUP_ID.
• AMS_MEDIA_B — the media and activity channels through which an offer is delivered, joined on ACTIVITY_MEDIA_ID.
• JTF_RS_RESOURCE_EXTNS — the resource/owner responsible for the offer, joined on OWNER_ID.
• HZ_PARTIES — the buying-group contact, joined on BUYING_GROUP_CONTACT_ID.
• PV_PG_INVITE_HEADERS_B — the channel-program invitation header, which references the offer through QP_LIST_HEADER_ID.

Collectively these relationships confirm AMS_OFFERS as the marketing-facing satellite of the pricing hub, mediating status, performance, budget, and channel context for every promotion managed in Oracle Marketing.