Results for “offer_product_id”

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

Overview

The AMS_OFFER_PRODUCTS_TEMP table resides in the AMS (Oracle Marketing) schema and serves as a transient staging structure within the Oracle E-Business Suite Marketing module. Its documented purpose is to store all products defined for a request covering only active offers created through Oracle Marketing Online, populated temporarily when a full refresh is executed through a concurrent program. Unlike the permanent offer product tables, this object holds intermediate working data that supports the refresh process before validated records settle into the operational marketing tables.

From a data-modeling perspective, the ETRM metadata classifies this object heuristically as a standalone Data Vault construct. This is a modeling suggestion rather than a strict physical attribute: the table does not participate as a hub, link, or satellite in a formal Data Vault design, and its foreign-key dependencies are limited to lookup references rather than to a dense transactional network. The standalone classification reflects its role as an isolated staging landing area with a single-column surrogate primary key.

Key Information Stored

The table contains nine documented columns at the 12.2.2 schema level. The most significant are:

• OFFER_PRODUCT_ID — the surrogate primary key, enforced by AMS_OFFER_PRODUCTS_TEMP_PK. It uniquely identifies each staged offer-product association and is the column most frequently referenced by the user's search term.
• PRODUCT_ID — the foreign key to FND_DM_PRODUCTS, identifying the product linked to the offer.
• QP_LIST_HEADER_ID — references the Oracle Advanced Pricing list header, tying the staged record to the pricing structure governing the offer.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, controlling multi-tenant and security-group visibility of the staged data.
• CREATION_DATE, CREATED_BY — audit columns capturing when and by whom the temporary record was created during the refresh run.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS "Who" audit columns recording the last modification and the login session responsible for it.

No additional unique business-key indexes beyond the primary key are documented, so OFFER_PRODUCT_ID is the sole unique identifier, while PRODUCT_ID and QP_LIST_HEADER_ID function as the principal business-key candidates in practical query patterns.

Common Use Cases and Queries

Because the table is populated only during a full refresh concurrent program, its primary use cases are diagnostic and reconciliation-oriented. A frequent scenario involves validating that products staged for active offers match the expected pricing list membership before the refresh commits. A representative query joins the temporary data to the product list header:

SELECT t.offer_product_id, t.product_id, t.qp_list_header_id
FROM   ams.ams_offer_products_temp t
WHERE  t.security_group_id = :p_security_group_id
AND    t.creation_date >= :p_run_date;

Operations teams also use the table to audit concurrent request output, comparing staged rows against the permanent offer-product tables to detect orphaned or dropped products. Reporting on creation and update timestamps supports confirmation that a given refresh executed within its scheduled window. Because rows are temporary in nature, queries should always filter by CREATED_BY or CREATION_DATE to isolate a specific refresh invocation rather than scanning the entire staging set.

Related Objects

The following objects are the most significant dependencies and related structures:

• FND_DM_PRODUCTS — referenced via PRODUCT_ID; supplies product master descriptions for staged offer products.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; governs data access scope for the staged rows.
• AMS_OFFER_PRODUCTS_TEMP_PK — the primary key constraint over OFFER_PRODUCT_ID.
• QP_LIST_HEADERS — the pricing list header implied by QP_LIST_HEADER_ID, linking staged records to pricing definitions.
• AMS_OFFER_PRODUCTS — the permanent offer-product association table that ultimately receives validated data from the refresh.
• AMS_OFFERS — the parent offer definition whose active status drives what is staged into this temporary table.

These relationships confirm that AMS_OFFER_PRODUCTS_TEMP operates as a bounded staging layer within the Oracle Marketing and Advanced Pricing integration, with the OFFER_PRODUCT_ID serving as the anchor key for all downstream reconciliation.