Search Results qp_limit_balances




Overview
Explore in AI Chat

Overview

QP_LIMIT_BALANCES is a transactional table in the QP (Advanced Pricing) schema of Oracle E-Business Suite, holding the running balance records for pricing limits defined in the Oracle Trade Management and Advanced Pricing limit-checking framework. Each row captures the monetary state of a specific limit as constrained by a set of qualifier attributes, including organization-driven and multivalued attribute combinations. Limits in EBS are used to enforce spending, discount, or accrual thresholds against customers, price lists, or other pricing dimensions, and QP_LIMIT_BALANCES preserves the aggregated figures that determine whether a transaction may proceed.

From a Data Vault modeling perspective, the heuristic classification for this table is satellite-leaning. It extends the parent limit definition with descriptive and measured attributes (available, reserved, and consumed amounts) while carrying a foreign key to QP_LIMITS. Because the balance is qualified further by organization and multivalued attributes, QP_LIMIT_BALANCES behaves as an attribute-bearing satellite rather than a pure hub or link.

Key Information Stored

The table contains 44 documented columns, of which the following are the most significant:

The composite unique index QP_LIMIT_BALANCES_U2 (LIMIT_ID plus the organization, multivalued attribute, and PRICE_REQUEST_CODE columns) represents the business-key candidate that governs uniqueness beyond the surrogate identifier.

Common Use Cases and Queries

Typical reporting scenarios include checking remaining limit availability per customer or organization, auditing reserved versus consumed amounts, and reconciling limit balances against the transactions that produced them. A representative query joining the balance to its parent limit definition is:

  • SELECT b.LIMIT_BALANCE_ID, b.LIMIT_ID, b.ORGANIZATION_ATTR_VALUE, b.AVAILABLE_AMOUNT, b.RESERVED_AMOUNT, b.CONSUMED_AMOUNT FROM QP.QP_LIMIT_BALANCES b, QP.QP_LIMITS l WHERE b.LIMIT_ID = l.LIMIT_ID AND b.ORGANIZATION_ATTR_VALUE = :org_value;
  • To trace consumption, join QP_LIMIT_TRANSACTIONS t ON t.LIMIT_BALANCE_ID = b.LIMIT_BALANCE_ID to reconstruct how each transaction reduced or reserved the available balance.
  • Aggregate reporting by multivalued attribute: group by MULTIVAL_ATTRIBUTE1, MULTIVAL_ATTR1_VALUE, MULTIVAL_ATTRIBUTE2, and MULTIVAL_ATTR2_VALUE to summarize utilization across pricing qualifiers.
  • Audit queries using LAST_UPDATE_DATE and CREATED_BY to identify balances modified by a specific user or concurrent program.

Related Objects

  • QP_LIMITS — parent table referenced by QP_LIMIT_BALANCES.LIMIT_ID; stores the limit definition itself.
  • QP_LIMIT_TRANSACTIONS — child table referencing QP_LIMIT_BALANCES.LIMIT_BALANCE_ID; captures the individual transactions that adjust a balance.
  • QP_LIMIT_BALANCES_PK and QP_LIMIT_BALANCES_U1 / U2 — the primary and unique indexes enforcing identity and business-key uniqueness.
  • QP_PRICING_ATTRIBUTES and related QP qualifier tables — supply the organization and multivalued attribute context values stored in the balance rows.
  • QP_PRICE_LISTS / QP_LIST_HEADERS — pricing structures whose limits ultimately derive from the balances recorded here.