Search Results qp_limit_transactions




Overview
Explore in AI Chat

Overview

QP_LIMIT_TRANSACTIONS is a transactional child table in the Oracle Advanced Pricing (QP) module, owned by the QP schema. It records every pricing event that either consumed or replenished the available balance associated with a pricing limit. A limit, in Advanced Pricing terms, defines a ceiling — by amount, volume, or usage frequency — against which modifier or promotion eligibility is evaluated. Because balances change incrementally as transactions are processed, QP_LIMIT_TRANSACTIONS serves as the detailed movement ledger behind the aggregate position stored in QP_LIMIT_BALANCES.

The object is classified in the ETRM metadata with a heuristic Data Vault designation of link. From a modeling perspective, this suggests the table primarily functions as an associative entity, resolving the many-to-many relationships between pricing limits, price lists, price list lines, and pricing phases. It is not a hub because it carries no independent business key of its own, and it is not a pure satellite because it joins multiple referenced entities rather than merely describing one. The table exists in both Oracle EBS 12.1.1 and 12.2.2, with a documented physical schema of 36 columns in 12.2.2.

Key Information Stored

The table is anchored by the LIMIT_BALANCE_ID foreign key, which links each transaction row to the specific balance record it affects in QP_LIMIT_BALANCES. The business-key candidate documented through unique index QP_LIMIT_TRANSACTIONS_U1 is the composite of PRICE_REQUEST_CODE, LIMIT_BALANCE_ID, LIST_HEADER_ID, and LIST_LINE_ID — this combination enforces that a given price request affects a given limit line only once.

Common Use Cases and Queries

Typical scenarios include auditing limit consumption for a specific promotion, reconciling balance movements to pricing engine output, and reporting on manual versus automatic limit adjustments. A representative query joining the transaction ledger to the current balance follows:

  • Reconcile movements: SELECT t.LIMIT_BALANCE_ID, t.AMOUNT, t.PRICE_REQUEST_DATE FROM QP_LIMIT_TRANSACTIONS t WHERE t.LIMIT_BALANCE_ID = :balance_id.
  • Trace by price list: join to QP_LIST_HEADERS_B on LIST_HEADER_ID and QP_LIST_LINES on LIST_LINE_ID.
  • Isolate manual adjustments: filter on MANUAL_FLAG = 'Y'.
  • Analyze by pricing phase: join QP_PRICING_PHASES on PRICING_PHASE_ID.
  • Aggregate consumption per balance using SUM(AMOUNT) grouped by LIMIT_BALANCE_ID.

Related Objects

QP_LIMIT_TRANSACTIONS participates in four documented foreign key relationships and depends on the following objects:

  • QP_LIMIT_BALANCES — joined via LIMIT_BALANCE_ID; holds the aggregate limit position.
  • QP_LIST_HEADERS_B — joined via LIST_HEADER_ID; parent price list header.
  • QP_LIST_LINES — joined via LIST_LINE_ID; the specific line carrying the limit.
  • QP_PRICING_PHASES — joined via PRICING_PHASE_ID; defines the pricing engine phase.

During pricing engine execution, the Advanced Pricing APIs populate this table as limits are evaluated, making it a critical dependency for limit-related reporting, reconciliation, and troubleshooting of eligibility discrepancies.