Search Results multival_attribute1

Overview

QP_LIMIT_BALANCES_V is an APPS-owned database view in the Oracle Advanced Pricing (QP) module, valid in both Oracle EBS 12.1.1 and 12.2.2. It is documented as holding the balances for the limit — that is, the consumed, reserved, and available amounts tracked against pricing limits defined in Oracle Advanced Pricing. Pricing limits (also called budgetary or accrual limits) allow organizations to cap the total value of a promotion, discount, or modifier that can be applied within a defined scope. This view exposes the balance records that record how much of each limit has been used at any point in time.

Because the view is defined over a single base table, it does not perform joins or aggregations. It is effectively a synonym-backed read interface that presents limit balance rows, along with their descriptive and DBA audit attributes, for use in reports, concurrent programs, and integration extracts. Report authors and technical consultants query it to determine remaining budget on a limit without navigating the pricing engine, making it the standard access point for limit-balance reporting.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over one referenced base object: QP_LIMIT_BALANCES, accessed through its synonym. The view text is a direct projection:

• SELECT of all operational columns from QP_LIMIT_BALANCES, aliased QPLMB.
• ROWID exposed as ROW_ID, enabling row-level identification of balance records.
• Because it is a simple select over a single table, the view is inherently updatable in the same manner as the base table, subject to the base table's constraints.
• No DISTINCT, GROUP BY, or join logic exists; therefore rows in the view are exactly the rows in QP_LIMIT_BALANCES.

The base table stores one row per balance context for a given limit, keyed by LIMIT_BALANCE_ID and related to the limit definition through LIMIT_ID. The view adds no filtering, so all balance rows are visible.

Key Columns

• LIMIT_BALANCE_ID — primary identifier for the balance record.
• LIMIT_ID — foreign key linking the balance to its parent limit definition.
• AVAILABLE_AMOUNT, RESERVED_AMOUNT, CONSUMED_AMOUNT — the core financial balances. Available is the capacity remaining, reserved is the amount tentatively held by unconfirmed transactions, and consumed is the amount already applied.
• ORGANIZATION_ATTR_CONTEXT, ORGANIZATION_ATTRIBUTE, ORGANIZATION_ATTR_VALUE — describe the organizational scope against which the balance is tracked (for example, an operating unit or organization flexfield).
• MULTIVAL_ATTR1_* and MULTIVAL_ATTR2_* — two sets of multivalue attribute columns. Each set includes TYPE, CONTEXT, ATTRIBUTE (name), VALUE, and DATATYPE. The column MULTIVAL_ATTR2_VALUE (search term: "multival_attr2_value") stores the value of the second multivalue attribute used to qualify the balance, such as a customer, product, or qualifier dimension.
• CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — standard Oracle descriptive flexfield (DFF) columns supporting user-defined extension data.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID.

Common Use Cases and Queries

Typical uses include auditing promotion budgets, reconciling consumed versus available amounts, and feeding limit balances into custom order or pricing reports. A common query retrieves balances for a limit and its multivalue qualifiers:

• SELECT LIMIT_ID, LIMIT_BALANCE_ID, AVAILABLE_AMOUNT, RESERVED_AMOUNT, CONSUMED_AMOUNT FROM QP_LIMIT_BALANCES_V WHERE LIMIT_ID = :p_limit_id;
• SELECT LIMIT_BALANCE_ID, MULTIVAL_ATTRIBUTE2, MULTIVAL_ATTR2_VALUE, AVAILABLE_AMOUNT FROM QP_LIMIT_BALANCES_V WHERE MULTIVAL_ATTR2_VALUE IS NOT NULL;
• SELECT ORGANIZATION_ATTRIBUTE, ORGANIZATION_ATTR_VALUE, SUM(CONSUMED_AMOUNT) FROM QP_LIMIT_BALANCES_V GROUP BY ORGANIZATION_ATTRIBUTE, ORGANIZATION_ATTR_VALUE;

Because the view is unfiltered, always constrain queries by LIMIT_ID or organization attributes to avoid full scans. Note that balance values are maintained by the pricing engine at transaction time, so reports should account for uncommitted reservations via RESERVED_AMOUNT.