Search Results qp_adv_mod_products




Overview
Explore in AI Chat

Overview

QP_ADV_MOD_PRODUCTS is a table in the QP (Advanced Pricing) module of Oracle E-Business Suite, residing in the QP schema. It stores product information and the associated pricing phase for all products referenced by modifiers of type Other Item Discount, Promotional Goods or Related, and modifiers defined at the Group of Lines level. In effect, it acts as the intersection between a pricing phase and the specific product attribute/value combinations that qualify a modifier, allowing a single modifier to target one or many items without duplicating the modifier definition itself.

From a Data Vault modeling perspective, the documented physical structure is limited to three columns and a single foreign key to QP_PRICING_PHASES. The heuristic classification derived from the FK structure is standalone, meaning the table neither functions as a hub (no independent business key domain) nor as a conventional link joining two hubs. This classification should be treated as a modeling suggestion only; functionally the table behaves closer to a dependent child or reference list, qualifying parent modifier rows by pricing phase and product attribute.

Key Information Stored

Although the documented physical schema lists only three columns, each carries significant semantic weight and, combined, forms the complete business key of the table:

  • PRICING_PHASE_ID — Foreign key to QP_PRICING_PHASES. Identifies the pricing phase under which the product qualification applies. This is the column that ties the product entry back to the modifier execution context.
  • PRODUCT_ATTRIBUTE — The attribute by which a product is qualified (for example, an item identifier, item category, or other product-defining attribute). It names the dimension being constrained.
  • PRODUCT_ATTR_VALUE — The specific value the attribute must take for the modifier to apply to that product.

No surrogate primary key column is documented in the ETRM metadata. The business-key candidate is defined by the unique index QP_ADV_MOD_PRODUCTS_U1 covering (PRICING_PHASE_ID, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE). This composite uniqueness enforces that a given attribute/value pairing is recorded only once per pricing phase, preventing duplicate product qualification rows for the same modifier context.

Common Use Cases and Queries

Typical scenarios involve diagnosing why a promotional or other-item modifier does not fire, auditing which products are in scope for a modifier, and reporting the product footprint of a pricing phase.

Listing products for a given pricing phase:

  • SELECT PRICING_PHASE_ID, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE FROM QP.QP_ADV_MOD_PRODUCTS WHERE PRICING_PHASE_ID = :phase_id;

Joining to the phase definition to obtain the phase context:

  • SELECT a.PRICING_PHASE_ID, a.PRODUCT_ATTRIBUTE, a.PRODUCT_ATTR_VALUE, p.PRICING_PHASE_CODE FROM QP.QP_ADV_MOD_PRODUCTS a, QP.QP_PRICING_PHASES p WHERE a.PRICING_PHASE_ID = p.PRICING_PHASE_ID;

Detecting duplicate qualification entries (which the U1 index should prevent, unless indexes have been altered):

  • SELECT PRICING_PHASE_ID, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE, COUNT(*) FROM QP.QP_ADV_MOD_PRODUCTS GROUP BY PRICING_PHASE_ID, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE HAVING COUNT(*) > 1;

For modifier troubleshooting, the table is best joined back to the modifier and modifier-list entities through QP_PRICING_PHASES, since the table does not directly carry a modifier identifier.

Related Objects

The following objects are most significant when working with QP_ADV_MOD_PRODUCTS:

  • QP_PRICING_PHASES — Parent table referenced via QP_ADV_MOD_PRODUCTS.PRICING_PHASE_ID. Provides the phase context in which the product qualification is evaluated.
  • QP_MODIFIERS — Modifier definitions; their effective product scope is bounded by the phase and product rows recorded here.
  • QP_MODIFIER_LISTS — Modifier list header/line data; qualifiers of the Group of Lines type ultimately resolve through phase and product information in this table.
  • QP_PRICING_ATTRIBUTES — Defines the product attributes that PRODUCT_ATTRIBUTE values reference, useful when resolving attribute codes to descriptions.
  • QP_PRICE_BOOKS / QP_PRICE_LISTS — Price book and list entities whose modifier applications are visually configured using this product scope.

Together these objects form the modifier qualification chain: modifier list, modifier, pricing phase, and finally product qualification rows in QP_ADV_MOD_PRODUCTS.