Search Results qp_limit_basis

Overview

QP.QP_LIMITS is a core Advanced Pricing table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the definition of pricing limits and the promotional, deal, or modifier context for which each limit is established. Limits allow organizations to cap the exposure of a promotion, agreement, or modifier — for example by revenue, cost, quantity, accrual units, or usage — and to define the system behavior when a configured threshold is crossed.

The table resides in the QP schema, is registered under FND Design Data as QP.QP_LIMITS, and holds transaction data in the APPS_TS_TX_DATA tablespace with indexes in APPS_TS_TX_IDX. With 46 documented columns, it is a wide, attribute-rich structure.

From a Data Vault modeling perspective, the mined heuristic classification is satellite-leaning. This suggests QP_LIMITS is best understood as a descriptive satellite attached to a parent pricing construct (the list header or list line), rather than as an independent hub or a pure relationship link. The primary key QP_LIMITS_PK (LIMIT_ID) acts as the surrogate identifier, while LIST_HEADER_ID and LIST_LINE_ID provide the contextual grain of each limit record.

Key Information Stored

The most significant columns in QP_LIMITS fall into identifier, definition, and descriptive categories:

• LIMIT_ID — System-generated surrogate primary key that uniquely identifies each limit. It is also the sole unique business-key candidate, enforced by the unique index QP_LIMITS_U1.
• LIST_HEADER_ID — Identifies the list header to which the limit belongs; a foreign key to QP_LIST_HEADERS_B.
• LIST_LINE_ID — Identifies the associated list line; a foreign key to QP_LIST_LINES.
• LIMIT_NUMBER — Sequence number of the limit within its list header or list line.
• BASIS — The basis against which the limit is measured: Revenue, Cost, Accrual Units, Quantity, or Usage. Values derive from lookup type QP_LIMIT_BASIS.
• LIMIT_LEVEL_CODE — The level at which the limit applies, based on lookup type LIMIT_LEVEL.
• LIMIT_EXCEED_ACTION_CODE — The action the pricing engine takes when a limit is exceeded, based on lookup type LIMIT_EXCEED_ACTION.
• LIMIT_HOLD_FLAG — Indicates whether the pricing engine should return a hold status when the limit is breached.
• ORGANIZATION_FLAG — Controls whether the limit applies across all sales organizations or is tracked per sales organization with which the customer does business.
• AMOUNT — The monetary or numeric value governing the limit threshold.
• MULTIVAL_ATTR1_CONTEXT / MULTIVAL_ATTRIBUTE1 and the parallel attribute 2 columns — Multivalue attribute context and value pairs, indexed for query efficiency by QP_LIMITS_U2 and QP_LIMITS_U3.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — The flexible descriptive flexfield segments and their context.
• EACH_ATTR_EXISTS, NON_EACH_ATTR_COUNT, TOTAL_ATTR_COUNT — Denormalized tracker columns describing attribute population, useful for optionality modeling.
• Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, PROGRAM_ID, REQUEST_ID, and related fields) — Audit and concurrent program metadata.

Common Use Cases and Queries

A frequent reporting requirement is enumerating limits attached to a specific agreement or promotion. The following pattern joins the limit definition to its parent header:

SELECT l.LIMIT_ID, l.LIMIT_NUMBER, l.BASIS, l.AMOUNT, l.LIMIT_LEVEL_CODE
FROM QP.QP_LIMITS l
WHERE l.LIST_HEADER_ID = :list_header_id
ORDER BY l.LIMIT_NUMBER;

Because LIMIT_ID is the single unique key, lookups by LIMIT_ID (or via the QP_LIMITS_U1 index) are the fastest access path. Queries that filter by list header and line benefit from the non-unique composite index QP_LIMITS_N1 (LIST_HEADER_ID, LIST_LINE_ID). Reporting on multivalue attribute combinations can leverage QP_LIMITS_U2 and QP_LIMITS_U3. A common dashboard measures promotion utilization by aggregating AMOUNT against consumption recorded in QP_LIMIT_BALANCES. Audit queries typically trace who created or modified a limit using the Who columns and REQUEST_ID.

Related Objects

• QP.QP_LIMIT_ATTRIBUTES — Child table referencing QP_LIMITS.LIMIT_ID, storing additional limit attributes.
• QP.QP_LIMIT_BALANCES — Tracks consumed and remaining balances per limit; joins on LIMIT_ID and is essential for utilization reporting.
• QP.QP_LIST_HEADERS_B — Parent list header; joined via LIST_HEADER_ID.
• QP.QP_LIST_LINES — Parent list line; joined via LIST_LINE_ID.
• Associated pricing engine APIs and lookup types (QP_LIMIT_BASIS, LIMIT_LEVEL, LIMIT_EXCEED_ACTION) drive valid values and runtime evaluation of limit definitions.