OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Advanced Pricing (QP) Price List Tables Reference

This reference lists the Oracle Advanced Pricing (QP) tables for price lists, modifiers, qualifiers, formulas and the bulk loader in EBS 12.1 and 12.2, with their join keys and code values. The notes below the table hold a working SQL that returns an item's list price on a price list.

Download:

Table/View	Area	Holds	Key columns	Joins	Notes
QP_LIST_HEADERS_B	Header	Price lists, modifier lists and charge lists	LIST_HEADER_ID, LIST_TYPE_CODE, CURRENCY_CODE, ACTIVE_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, GLOBAL_FLAG, ORIG_ORG_ID, AUTOMATIC_FLAG, ROUNDING_FACTOR	LIST_HEADER_ID = QP_LIST_LINES, QP_QUALIFIERS	LIST_TYPE_CODE: PRL price list, AGR agreement price list, DLT discount list, SLT surcharge list, PRO promotion, DEL deal, CHARGES freight and special charges.
QP_LIST_HEADERS_TL	Header	Translated name and description	LIST_HEADER_ID, LANGUAGE, NAME, DESCRIPTION	LIST_HEADER_ID	Filter LANGUAGE = USERENV('LANG') or use the VL view.
QP_LIST_HEADERS_VL	Header	Header plus name in session language	LIST_HEADER_ID, NAME, LIST_TYPE_CODE, CURRENCY_CODE	LIST_HEADER_ID	Usual starting point to find a price list by name.
QP_LIST_LINES	Line	Price list lines and modifier lines	LIST_LINE_ID, LIST_HEADER_ID, LIST_LINE_TYPE_CODE, OPERAND, ARITHMETIC_OPERATOR, START_DATE_ACTIVE, END_DATE_ACTIVE, PRODUCT_PRECEDENCE, PRICE_BREAK_TYPE_CODE, PRICING_PHASE_ID, MODIFIER_LEVEL_CODE, PRICE_BY_FORMULA_ID	LIST_LINE_ID = QP_PRICING_ATTRIBUTES.LIST_LINE_ID	LIST_LINE_TYPE_CODE: PLL price list line, PBH price break header, DIS discount, SUR surcharge, FREIGHT_CHARGE. OPERAND is the price or modifier value.
QP_PRICING_ATTRIBUTES	Attribute	Product (item, category, all items) and pricing attributes of a line	LIST_LINE_ID, LIST_HEADER_ID, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO, COMPARISON_OPERATOR_CODE, EXCLUDER_FLAG	PRODUCT_ATTR_VALUE = TO_CHAR(MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID) when attribute is PRICING_ATTRIBUTE1	Context ITEM: PRICING_ATTRIBUTE1 = item id, PRICING_ATTRIBUTE2 = item category id, PRICING_ATTRIBUTE3 = all items. Values are stored as text.
QP_QUALIFIERS	Qualifier	Who or when a list or line applies (customer, order type, price list)	QUALIFIER_ID, LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE, QUALIFIER_ATTR_VALUE, QUALIFIER_GROUPING_NO, COMPARISON_OPERATOR_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE	LIST_HEADER_ID; LIST_LINE_ID = -1 or null for header qualifiers	Qualifiers in the same QUALIFIER_GROUPING_NO are AND, different groups are OR.
QP_RLTD_MODIFIERS	Line	Links a price break header to its break lines	RLTD_MODIFIER_ID, FROM_RLTD_MODIFIER_ID, TO_RLTD_MODIFIER_ID, RLTD_MODIFIER_GRP_TYPE	FROM_RLTD_MODIFIER_ID = PBH LIST_LINE_ID; TO_RLTD_MODIFIER_ID = break LIST_LINE_ID	RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK'. Break ranges are in QP_PRICING_ATTRIBUTES of the child lines (context VOLUME).
QP_PRICE_FORMULAS_B	Formula	Pricing formulas	PRICE_FORMULA_ID, FORMULA, START_DATE_ACTIVE, END_DATE_ACTIVE	PRICE_FORMULA_ID = QP_LIST_LINES.PRICE_BY_FORMULA_ID	FORMULA is an expression of step numbers, for example 1*2. Names in QP_PRICE_FORMULAS_TL.
QP_PRICE_FORMULA_LINES	Formula	Steps of a formula	PRICE_FORMULA_LINE_ID, PRICE_FORMULA_ID, STEP_NUMBER, PRICE_FORMULA_LINE_TYPE_CODE, NUMERIC_CONSTANT, PRICING_ATTRIBUTE, PRICE_LIST_LINE_ID	PRICE_FORMULA_ID	Line types include numeric constant, pricing attribute, list price and modifier value.
QP_LIST_LINES_V	Line	View joining list lines with product attributes	LIST_LINE_ID, LIST_HEADER_ID, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTR_VALUE, OPERAND	LIST_HEADER_ID	Convenient for ad hoc queries; heavy for large reports, join the base tables instead.
QP_INTERFACE_LIST_HEADERS	Interface	Bulk loader headers	ORIG_SYS_HEADER_REF, LIST_TYPE_CODE, NAME, CURRENCY_CODE, INTERFACE_ACTION_CODE, PROCESS_STATUS_FLAG, PROCESS_FLAG, REQUEST_ID	ORIG_SYS_HEADER_REF ties headers to lines, attributes and qualifiers	Rows are processed when PROCESS_STATUS_FLAG = 'P'. INTERFACE_ACTION_CODE: INSERT, UPDATE, DELETE.
QP_INTERFACE_LIST_LINES	Interface	Bulk loader price list lines	ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, LIST_LINE_TYPE_CODE, OPERAND, ARITHMETIC_OPERATOR, INTERFACE_ACTION_CODE, PROCESS_STATUS_FLAG	ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF	ORIG_SYS_LINE_REF must be unique within the header.
QP_INTERFACE_PRICING_ATTRIBS	Interface	Bulk loader product and pricing attributes	ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE	ORIG_SYS_LINE_REF	Value can be supplied as code or as displayed value columns depending on the loader columns used.
QP_INTERFACE_QUALIFIERS	Interface	Bulk loader header qualifiers	ORIG_SYS_HEADER_REF, ORIG_SYS_QUALIFIER_REF, QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE, QUALIFIER_ATTR_VALUE	ORIG_SYS_HEADER_REF	Optional.
QP_INTERFACE_ERRORS	Interface	Errors written by the bulk loader	REQUEST_ID, ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ERROR_MESSAGE	REQUEST_ID of the import run	Failed rows stay in the interface tables. Reset REQUEST_ID to NULL and PROCESS_STATUS_FLAG to 'P' after fixing to reprocess.
QP: Bulk Import of Price List	Interface	Concurrent program that loads the QP_INTERFACE tables	Parameters include entity type and process ID or request filters	Reads rows with PROCESS_STATUS_FLAG = 'P'	Successful rows are deleted from the interface. Profile QP: Batch Size for Bulk Import controls batch size.
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST	API	Public API to create, update and delete price lists, lines and attributes	p_price_list_rec, p_price_list_line_tbl, p_pricing_attr_tbl, p_qualifiers_tbl plus x_ out records	Uses LIST_HEADER_ID and LIST_LINE_ID for updates	Good for low volume, item by item changes from integrations.
QP_MODIFIERS_PUB.PROCESS_MODIFIERS	API	Public API to maintain modifier lists, lines, qualifiers and attributes	p_modifier_list_rec, p_modifiers_tbl, p_qualifiers_tbl, p_pricing_attr_tbl plus x_ out records	LIST_HEADER_ID for modifier lists	Use for discounts and surcharges, not price list lines.
OE_ORDER_LINES_ALL.PRICE_LIST_ID	Link	Price list used on an order line	LINE_ID, PRICE_LIST_ID, UNIT_LIST_PRICE, UNIT_SELLING_PRICE, PRICING_QUANTITY_UOM	PRICE_LIST_ID = QP_LIST_HEADERS_B.LIST_HEADER_ID	Header price list is OE_ORDER_HEADERS_ALL.PRICE_LIST_ID; lines can differ.
OE_PRICE_ADJUSTMENTS	Link	Modifiers and charges applied to orders	PRICE_ADJUSTMENT_ID, HEADER_ID, LINE_ID, LIST_HEADER_ID, LIST_LINE_ID, LIST_LINE_TYPE_CODE, OPERAND, ADJUSTED_AMOUNT, APPLIED_FLAG	LIST_LINE_ID = QP_LIST_LINES.LIST_LINE_ID	Explains the difference between UNIT_LIST_PRICE and UNIT_SELLING_PRICE. Filter APPLIED_FLAG = 'Y'.

Item price query: SELECT h.name price_list, msi.segment1 item, l.operand list_price, a.product_uom_code uom, l.start_date_active, l.end_date_active FROM qp_list_headers_vl h, qp_list_lines l, qp_pricing_attributes a, mtl_system_items_b msi WHERE h.list_header_id = l.list_header_id AND l.list_line_id = a.list_line_id AND h.list_type_code = 'PRL' AND l.list_line_type_code = 'PLL' AND a.product_attribute_context = 'ITEM' AND a.product_attribute = 'PRICING_ATTRIBUTE1' AND a.product_attr_value = TO_CHAR(msi.inventory_item_id) AND msi.organization_id = :master_org_id AND msi.segment1 = :item AND h.name = :price_list AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(l.start_date_active), TRUNC(SYSDATE)) AND NVL(TRUNC(l.end_date_active), TRUNC(SYSDATE));

Price list health check

Send us a price list extract and we will flag overlapping lines, expired prices still in use and items priced only through categories.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

Item price lookup path

A price list is a QP_LIST_HEADERS_B row with LIST_TYPE_CODE 'PRL'. Each price is a QP_LIST_LINES row with LIST_LINE_TYPE_CODE 'PLL' and the price in OPERAND (with ARITHMETIC_OPERATOR UNIT_PRICE for a plain unit price). The item is not on the line: it is a QP_PRICING_ATTRIBUTES row with PRODUCT_ATTRIBUTE_CONTEXT 'ITEM', PRODUCT_ATTRIBUTE 'PRICING_ATTRIBUTE1' and PRODUCT_ATTR_VALUE holding the inventory item id as text, plus PRODUCT_UOM_CODE. So the path is header to line on LIST_HEADER_ID, line to attribute on LIST_LINE_ID, and attribute to item through TO_CHAR(INVENTORY_ITEM_ID). Apply the line date range, and remember a price can also come from a category line or an 'all items' line, or from a price break header with child lines.

PRL header, PLL line, ITEM / PRICING_ATTRIBUTE1 attribute
Compare text to text: TO_CHAR(inventory_item_id)
Check PRODUCT_UOM_CODE, a list can price the same item in several UOMs

Global flag and Pricing Security

GLOBAL_FLAG = 'Y' makes a price list or modifier usable by all operating units. When it is 'N', the list is restricted to the operating unit in ORIG_ORG_ID. With Pricing Security switched on (profile QP: Security Control), who may view and maintain each list is further controlled by entity usage and privileges set up in Oracle Pricing, so a user can see a list in Order Management but not open it in the Price List form. When a list does not appear in the order LOV, check the global flag and operating unit, the list currency against the order currency (or multi-currency conversion setup), active flag and dates, and any qualifiers on the list.

Bulk loading with QP: Bulk Import of Price List

For large loads, insert into QP_INTERFACE_LIST_HEADERS, QP_INTERFACE_LIST_LINES, QP_INTERFACE_PRICING_ATTRIBS and optionally QP_INTERFACE_QUALIFIERS, tying rows together with ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF and ORIG_SYS_PRICING_ATTR_REF. Set INTERFACE_ACTION_CODE to INSERT, UPDATE or DELETE and PROCESS_STATUS_FLAG to 'P', then run QP: Bulk Import of Price List. Successful rows are removed from the interface tables; failed rows stay and the reasons go to QP_INTERFACE_ERRORS by REQUEST_ID. To retry, fix the data and reset REQUEST_ID to NULL. For a few changes from an integration, the QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST API is simpler.

Gotchas: categories, breaks and UOM

Lines priced by item category use PRICING_ATTRIBUTE2 with the category id, and the category must belong to the category set used by pricing, so an item-level query misses them. Price break lines have their own PRICE BREAK relationship in QP_RLTD_MODIFIERS, and the quantity ranges are pricing attributes of the child lines, not of the PBH. Product precedence decides which line wins when both item and category lines qualify. Finally, OE_ORDER_LINES_ALL.UNIT_LIST_PRICE is the price the engine found, and OE_PRICE_ADJUSTMENTS explains every change from list to selling price.

Frequently Asked Questions

Which tables store price lists in Oracle EBS R12?

QP_LIST_HEADERS_B (with QP_LIST_HEADERS_TL for the name) holds price list headers with LIST_TYPE_CODE 'PRL', QP_LIST_LINES holds price lines with LIST_LINE_TYPE_CODE 'PLL' and the price in OPERAND, and QP_PRICING_ATTRIBUTES holds the item, category and UOM each line prices.

What is a global price list in Oracle EBS?

A price list with GLOBAL_FLAG = 'Y' on QP_LIST_HEADERS_B, available to every operating unit. A non-global list is restricted to the operating unit stored in ORIG_ORG_ID. Pricing Security, when enabled, adds further view and maintain controls on top of this.

How do I find the item on a QP price list line?

Join QP_PRICING_ATTRIBUTES on LIST_LINE_ID and filter PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' and PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'. PRODUCT_ATTR_VALUE is the inventory item id stored as text, so compare it with TO_CHAR(MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID).

How do I load thousands of price list lines?

Use the Pricing bulk loader: populate the QP_INTERFACE_LIST_HEADERS, QP_INTERFACE_LIST_LINES and QP_INTERFACE_PRICING_ATTRIBS tables with PROCESS_STATUS_FLAG 'P' and run QP: Bulk Import of Price List. Errors are in QP_INTERFACE_ERRORS for the request.

If you want sales and pricing teams to ask what an item costs a customer and why, answered from your QP tables on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools