Search Results oe_pricing_contracts_pk

Overview

QP.OE_PRICING_CONTRACTS is a table in the Oracle Advanced Pricing (QP) schema that supports order-entry pricing constructs in Oracle E-Business Suite 12.1.1 and 12.2.2. It associates pricing agreements with price lists and discount (modifier) records, effectively acting as the bridge that binds a contractual pricing arrangement to the sell-side pricing objects evaluated during order capture. For technical consultants and DBAs, this table is most frequently encountered when diagnosing how a contract price or contract discount is resolved on a sales order line, or when reproducing pricing behavior outside the application form.

The ETRM metadata does not explicitly assign a Data Vault classification. On the basis of its mined FK structure, the table presents as standalone, though from a heuristic modeling perspective it is best treated as a link entity: it holds a surrogate key and carries references to agreement, price list, and discount entities, with little independent descriptive payload apart from the standard auditing columns.

Key Information Stored

The documented physical schema lists 25 columns. The most significant are:

• PRICING_CONTRACT_ID — the surrogate primary key, enforced by OE_PRICING_CONTRACTS_PK. This is the single documented unique identifier.
• AGREEMENT_ID — links the row to the pricing agreement that governs the contract terms.
• PRICE_LIST_ID — identifies the price list associated with the pricing contract, establishing the base or contract pricing source.
• DISCOUNT_ID — foreign key to SO_DISCOUNTS, tying the contract to a modifier (discount, surcharge, or promotion) definition.
• CREATION_DATE, CREATED_BY — standard audit columns recording row creation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — audit columns for the most recent modification and the session that performed it.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — the DFF (Descriptive Flexfield) column set, enabling customer-specific extensions without schema change.

Because no unique index beyond the primary key is documented, PRICING_CONTRACT_ID is the only documented unique business-key candidate. In practice, the combination of agreement, price list, and discount is normally relied upon as the logical business key, but this is not enforced by a documented unique constraint.

Common Use Cases and Queries

Typical reporting and troubleshooting scenarios include identifying which price lists and modifiers belong to a given agreement, and verifying contract setup before order entry testing.

• Retrieve all contracts for a specific agreement:
  SELECT pricing_contract_id, agreement_id, price_list_id, discount_id FROM qp.oe_pricing_contracts WHERE agreement_id = :agreement_id;
• Resolve contract discounts against modifier definitions:
  SELECT c.pricing_contract_id, c.discount_id, d.name FROM qp.oe_pricing_contracts c JOIN so_discounts d ON d.discount_id = c.discount_id;
• Audit recently created contract rows:
  SELECT pricing_contract_id, created_by, creation_date FROM qp.oe_pricing_contracts WHERE creation_date > SYSDATE - 30;

Use cases include pricing setup validation, contract price discrepancy investigation, migration reconciliation between environments, and extracting contract-to-pricelist mappings for downstream reporting.

Related Objects

The following objects are the most significant relative to this table, based on the documented FK relationships and the Advanced Pricing schema:

• SO_DISCOUNTS — referenced via OE_PRICING_CONTRACTS.DISCOUNT_ID; holds the modifier definition applied by the contract.
• QP_PRICING_AGREEMENTS — the agreement header referenced through AGREEMENT_ID, holding contract-level terms.
• QP_PRICE_LISTS (and related price list line tables) — referenced through PRICE_LIST_ID.
• OE_PRICING_CONTRACTS_PK — the primary key constraint on PRICING_CONTRACT_ID.
• QP_PRICING_ATTRIBUTES — the qualifying attribute volume often joined to price list and modifier records for resolution logic.
• OE_ORDER_LINES_ALL — sales order lines where contract pricing is ultimately consumed during order capture.

Consultants should note that the table is classified as standalone in the mined relationship model, meaning no downstream tables reference it through a documented foreign key; joins are therefore performed from the pricing side outward using AGREEMENT_ID, PRICE_LIST_ID, and DISCOUNT_ID.