Search Results qp_limits_pk

Overview

QP_LIMITS is a core table within the Oracle Advanced Pricing (QP) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of pricing limits and the associated Promotion, Deal, or modifier for which each limit is set. Within Advanced Pricing, limits are used to impose thresholds on the quantity or amount of a benefit that a customer, order, or promotion may consume. For example, a promotion offering a fixed discount may be capped at a defined number of units or a monetary ceiling; the governing parameters of that cap are persisted in QP_LIMITS.

The table resides in the QP schema and is classified as VALID. Applying heuristic Data Vault modeling, QP_LIMITS exhibits satellite-leaning characteristics: it is keyed by a single surrogate primary key (LIMIT_ID) and carries descriptive and foreign-key attributes that qualify pricing entities rather than establishing independent business identities. The table is referenced by dependent child tables through the LIMIT_ID column, and it references pricing list structures via LIST_HEADER_ID and LIST_LINE_ID.

Key Information Stored

The table contains 46 documented columns in the 12.2.2 schema. The most significant are:

• LIMIT_ID — Surrogate primary key defined by the QP_LIMITS_PK constraint and reinforced by the unique index QP_LIMITS_U1. This is the definitive business-key candidate for uniquely identifying a limit record.
• LIST_HEADER_ID — Foreign key to QP_LIST_HEADERS_B, identifying the pricing list header (promotion, deal, or modifier) to which the limit belongs.
• LIST_LINE_ID — Foreign key to QP_LIST_LINES, tying the limit to a specific pricing list line.
• LIMIT_NUMBER — Sequential identifier for the limit within its owning entity.
• BASIS — The basis on which the limit is applied (for example, quantity or monetary value).
• LIMIT_LEVEL_CODE — Indicates the level at which the limit operates (line, order, or another defined hierarchy).
• LIMIT_EXCEED_ACTION_CODE — Defines the action taken when the limit is exceeded (for example, hold, reject, or warn).
• LIMIT_HOLD_FLAG — Flag indicating whether the transaction is held upon exceeding the limit.
• AMOUNT — The threshold value of the limit itself.
• ORGANIZATION_FLAG — Indicates whether the limit is organization-specific.
• MULTIVAL_ATTRIBUTE1 / MULTIVAL_ATTRIBUTE2 and related type/context/datatype columns — Support flexible, multi-valued attribute definitions.
• ATTRIBUTE1 through ATTRIBUTE15, CONTEXT — Standard descriptive flexfield (DFF) columns for customer-defined extensions.
• EACH_ATTR_EXISTS, NON_EACH_ATTR_COUNT, TOTAL_ATTR_COUNT — Summary counters describing attribute presence.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_ID, REQUEST_ID.

Common Use Cases and Queries

Typical uses include auditing promotions to determine which limits are active, verifying the threshold and exceed-action behavior during order pricing, and reconciling consumed limits against QP_LIMIT_BALANCES. For example, to retrieve all limits defined for a given pricing list header:

• SELECT l.limit_id, l.limit_number, l.basis, l.amount, l.limit_exceed_action_code FROM qp.qp_limits l WHERE l.list_header_id = :p_list_header_id;

To join limits to their owning list header descriptions for reporting:

• SELECT l.limit_id, h.name, l.amount, l.basis FROM qp.qp_limits l, qp.qp_list_headers_b h WHERE l.list_header_id = h.list_header_id;

To identify limits with holds enabled and their exceed actions:

• SELECT limit_id, limit_number, limit_level_code, limit_exceed_action_code FROM qp.qp_limits WHERE limit_hold_flag = 'Y';

Reporting use cases include promotion governance reporting, ensuring no limit is misconfigured with contradictory hold and exceed-action settings, and reconciling multi-valued attribute usage across active promotions.

Related Objects

The following objects are most relevant and are directly documented through the foreign-key structure:

• QP_LIMIT_ATTRIBUTES — Child table referenced by QP_LIMIT_ATTRIBUTES.LIMIT_ID → QP_LIMITS.LIMIT_ID. Stores additional qualifying attributes for each limit.
• QP_LIMIT_BALANCES — Child table referenced by QP_LIMIT_BALANCES.LIMIT_ID → QP_LIMITS.LIMIT_ID. Holds consumed and remaining balance information for active limits.
• QP_LIST_HEADERS_B — Parent table of QP_LIMITS via LIST_HEADER_ID. Represents the promotion, deal, or modifier header.
• QP_LIST_LINES — Parent table of QP_LIMITS via LIST_LINE_ID. Represents the individual pricing list line the limit qualifies.

These relationships confirm QP_LIMITS as the central defining entity flanked by its balance and attribute satellites, integrated with the QP list header and line hierarchy.