# Oracle EBS Advanced Pricing (QP) Price List Tables Reference > Source: https://netray.co/tools/oracle-ebs-advanced-pricing-qp-tables-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer The QP tables behind price lists, modifiers, qualifiers and formulas in EBS 12.1 and 12.2, with join keys, code values, the bulk loader interface and a ready SQL to find an item's list price. ## Category oracle ## Erp Oracle E-Business Suite ## Audience - Oracle EBS developer - Order Management consultant - Pricing analyst - Report developer ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table/View **Key:** object **Type:** text #### Area **Key:** area **Type:** text #### Holds **Key:** holds **Type:** text #### Key columns **Key:** keys **Type:** text #### Joins **Key:** joins **Type:** text #### Notes **Key:** notes **Type:** text **Rows:** **Object:** QP_LIST_HEADERS_B **Area:** Header **Holds:** Price lists, modifier lists and charge lists **Keys:** 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 **Joins:** LIST_HEADER_ID = QP_LIST_LINES, QP_QUALIFIERS **Notes:** 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. **Object:** QP_LIST_HEADERS_TL **Area:** Header **Holds:** Translated name and description **Keys:** LIST_HEADER_ID, LANGUAGE, NAME, DESCRIPTION **Joins:** LIST_HEADER_ID **Notes:** Filter LANGUAGE = USERENV('LANG') or use the VL view. **Object:** QP_LIST_HEADERS_VL **Area:** Header **Holds:** Header plus name in session language **Keys:** LIST_HEADER_ID, NAME, LIST_TYPE_CODE, CURRENCY_CODE **Joins:** LIST_HEADER_ID **Notes:** Usual starting point to find a price list by name. **Object:** QP_LIST_LINES **Area:** Line **Holds:** Price list lines and modifier lines **Keys:** 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 **Joins:** LIST_LINE_ID = QP_PRICING_ATTRIBUTES.LIST_LINE_ID **Notes:** 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. **Object:** QP_PRICING_ATTRIBUTES **Area:** Attribute **Holds:** Product (item, category, all items) and pricing attributes of a line **Keys:** 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 **Joins:** PRODUCT_ATTR_VALUE = TO_CHAR(MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID) when attribute is PRICING_ATTRIBUTE1 **Notes:** Context ITEM: PRICING_ATTRIBUTE1 = item id, PRICING_ATTRIBUTE2 = item category id, PRICING_ATTRIBUTE3 = all items. Values are stored as text. **Object:** QP_QUALIFIERS **Area:** Qualifier **Holds:** Who or when a list or line applies (customer, order type, price list) **Keys:** 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 **Joins:** LIST_HEADER_ID; LIST_LINE_ID = -1 or null for header qualifiers **Notes:** Qualifiers in the same QUALIFIER_GROUPING_NO are AND, different groups are OR. **Object:** QP_RLTD_MODIFIERS **Area:** Line **Holds:** Links a price break header to its break lines **Keys:** RLTD_MODIFIER_ID, FROM_RLTD_MODIFIER_ID, TO_RLTD_MODIFIER_ID, RLTD_MODIFIER_GRP_TYPE **Joins:** FROM_RLTD_MODIFIER_ID = PBH LIST_LINE_ID; TO_RLTD_MODIFIER_ID = break LIST_LINE_ID **Notes:** RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK'. Break ranges are in QP_PRICING_ATTRIBUTES of the child lines (context VOLUME). **Object:** QP_PRICE_FORMULAS_B **Area:** Formula **Holds:** Pricing formulas **Keys:** PRICE_FORMULA_ID, FORMULA, START_DATE_ACTIVE, END_DATE_ACTIVE **Joins:** PRICE_FORMULA_ID = QP_LIST_LINES.PRICE_BY_FORMULA_ID **Notes:** FORMULA is an expression of step numbers, for example 1*2. Names in QP_PRICE_FORMULAS_TL. **Object:** QP_PRICE_FORMULA_LINES **Area:** Formula **Holds:** Steps of a formula **Keys:** PRICE_FORMULA_LINE_ID, PRICE_FORMULA_ID, STEP_NUMBER, PRICE_FORMULA_LINE_TYPE_CODE, NUMERIC_CONSTANT, PRICING_ATTRIBUTE, PRICE_LIST_LINE_ID **Joins:** PRICE_FORMULA_ID **Notes:** Line types include numeric constant, pricing attribute, list price and modifier value. **Object:** QP_LIST_LINES_V **Area:** Line **Holds:** View joining list lines with product attributes **Keys:** LIST_LINE_ID, LIST_HEADER_ID, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTR_VALUE, OPERAND **Joins:** LIST_HEADER_ID **Notes:** Convenient for ad hoc queries; heavy for large reports, join the base tables instead. **Object:** QP_INTERFACE_LIST_HEADERS **Area:** Interface **Holds:** Bulk loader headers **Keys:** ORIG_SYS_HEADER_REF, LIST_TYPE_CODE, NAME, CURRENCY_CODE, INTERFACE_ACTION_CODE, PROCESS_STATUS_FLAG, PROCESS_FLAG, REQUEST_ID **Joins:** ORIG_SYS_HEADER_REF ties headers to lines, attributes and qualifiers **Notes:** Rows are processed when PROCESS_STATUS_FLAG = 'P'. INTERFACE_ACTION_CODE: INSERT, UPDATE, DELETE. **Object:** QP_INTERFACE_LIST_LINES **Area:** Interface **Holds:** Bulk loader price list lines **Keys:** ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, LIST_LINE_TYPE_CODE, OPERAND, ARITHMETIC_OPERATOR, INTERFACE_ACTION_CODE, PROCESS_STATUS_FLAG **Joins:** ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF **Notes:** ORIG_SYS_LINE_REF must be unique within the header. **Object:** QP_INTERFACE_PRICING_ATTRIBS **Area:** Interface **Holds:** Bulk loader product and pricing attributes **Keys:** ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF, PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE, PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE **Joins:** ORIG_SYS_LINE_REF **Notes:** Value can be supplied as code or as displayed value columns depending on the loader columns used. **Object:** QP_INTERFACE_QUALIFIERS **Area:** Interface **Holds:** Bulk loader header qualifiers **Keys:** ORIG_SYS_HEADER_REF, ORIG_SYS_QUALIFIER_REF, QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE, QUALIFIER_ATTR_VALUE **Joins:** ORIG_SYS_HEADER_REF **Notes:** Optional. **Object:** QP_INTERFACE_ERRORS **Area:** Interface **Holds:** Errors written by the bulk loader **Keys:** REQUEST_ID, ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ERROR_MESSAGE **Joins:** REQUEST_ID of the import run **Notes:** Failed rows stay in the interface tables. Reset REQUEST_ID to NULL and PROCESS_STATUS_FLAG to 'P' after fixing to reprocess. **Object:** QP: Bulk Import of Price List **Area:** Interface **Holds:** Concurrent program that loads the QP_INTERFACE tables **Keys:** Parameters include entity type and process ID or request filters **Joins:** Reads rows with PROCESS_STATUS_FLAG = 'P' **Notes:** Successful rows are deleted from the interface. Profile QP: Batch Size for Bulk Import controls batch size. **Object:** QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST **Area:** API **Holds:** Public API to create, update and delete price lists, lines and attributes **Keys:** p_price_list_rec, p_price_list_line_tbl, p_pricing_attr_tbl, p_qualifiers_tbl plus x_ out records **Joins:** Uses LIST_HEADER_ID and LIST_LINE_ID for updates **Notes:** Good for low volume, item by item changes from integrations. **Object:** QP_MODIFIERS_PUB.PROCESS_MODIFIERS **Area:** API **Holds:** Public API to maintain modifier lists, lines, qualifiers and attributes **Keys:** p_modifier_list_rec, p_modifiers_tbl, p_qualifiers_tbl, p_pricing_attr_tbl plus x_ out records **Joins:** LIST_HEADER_ID for modifier lists **Notes:** Use for discounts and surcharges, not price list lines. **Object:** OE_ORDER_LINES_ALL.PRICE_LIST_ID **Area:** Link **Holds:** Price list used on an order line **Keys:** LINE_ID, PRICE_LIST_ID, UNIT_LIST_PRICE, UNIT_SELLING_PRICE, PRICING_QUANTITY_UOM **Joins:** PRICE_LIST_ID = QP_LIST_HEADERS_B.LIST_HEADER_ID **Notes:** Header price list is OE_ORDER_HEADERS_ALL.PRICE_LIST_ID; lines can differ. **Object:** OE_PRICE_ADJUSTMENTS **Area:** Link **Holds:** Modifiers and charges applied to orders **Keys:** PRICE_ADJUSTMENT_ID, HEADER_ID, LINE_ID, LIST_HEADER_ID, LIST_LINE_ID, LIST_LINE_TYPE_CODE, OPERAND, ADJUSTED_AMOUNT, APPLIED_FLAG **Joins:** LIST_LINE_ID = QP_LIST_LINES.LIST_LINE_ID **Notes:** Explains the difference between UNIT_LIST_PRICE and UNIT_SELLING_PRICE. Filter APPLIED_FLAG = 'Y'. **Notes:** 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)); ## Content **Intro:** 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. **Sections:** #### Item price lookup path **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### Which tables store price lists in Oracle EBS R12? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms oracle ebs price list, qp tables in oracle ebs r12, oracle ebs global price list, oracle ebs advanced pricing, oracle applications price list, qp_list_lines pricing_attribute1, qp bulk import of price list --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json