My Oracle Support Banner

Poor Performance Querying Price List Header Information (Doc ID 956235.1)

Last updated on JUNE 08, 2017

Applies to:

Oracle Advanced Pricing - Version 12.0.6 and later
Information in this document applies to any platform.
FORM:QPXPRDPL.FMB - Define Price Lists
Price List Setup

Symptoms

-- Problem Statement:
On 12.0.6 in Production:
Find extremely poor performance querying price list header information.
It takes 1-4 minutes.

EXPECTED BEHAVIOR
Expect price list header information to be returned in 10 seconds or less.


Additional Notes
upgraded to 12.0.6 from 11.5.9

Most expensive SQL statement shown:

SELECT CUSTOMER_ITEM_ID,product_attr_val_disp,PRODUCT_UOM_CODE,
PRIMARY_UOM_FLAG,BREAK_UOM_CODE,BREAK_UOM_CONTEXT,OPERAND,START_DATE_ACTIVE,
END_DATE_ACTIVE,MODIFIER_LEVEL_CODE,PRODUCT_PRECEDENCE,COMMENTS,REVISION,
REVISION_DATE,REVISION_REASON_CODE,ROW_ID,PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTRIBUTE,BREAK_UOM_ATTRIBUTE,PRODUCT_ATTR_VALUE,
INCOMPATIBILITY_GRP_CODE,LIST_LINE_NO,PRICING_GROUP_SEQUENCE,ACCRUAL_FLAG,
PRICING_PHASE_ID,AUTOMATIC_FLAG,PRICE_BY_FORMULA_ID,INVENTORY_ITEM_ID,
ORGANIZATION_ID,RELATED_ITEM_ID,RELATIONSHIP_TYPE_ID,SUBSTITUTION_CONTEXT,
SUBSTITUTION_ATTRIBUTE,SUBSTITUTION_VALUE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,PROGRAM_APPLICATION_ID,
PROGRAM_ID,PROGRAM_UPDATE_DATE,REQUEST_ID,LIST_HEADER_ID,LIST_LINE_ID,
PRICE_BREAK_TYPE_CODE,NUMBER_EFFECTIVE_PERIODS,EFFECTIVE_PERIOD_UOM,
OVERRIDE_FLAG,PRINT_ON_INVOICE_FLAG,REBATE_TRANSACTION_TYPE_CODE,BASE_QTY,
BASE_UOM_CODE,ACCRUAL_QTY,ACCRUAL_UOM_CODE,ESTIM_ACCRUAL_RATE,
GENERATE_USING_FORMULA_ID,REPRICE_FLAG,CONTEXT,ATTRIBUTE1,ATTRIBUTE2,
ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,
ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,
ATTRIBUTE15,PRICING_ATTRIBUTE_ID,PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTRIBUTE,PRICING_ATTR_VALUE_FROM,PRICING_ATTR_VALUE_TO,PRODUCT_ID,
LIST_PRICE,PERCENT_PRICE,ARITHMETIC_OPERATOR,LIST_LINE_TYPE_CODE,
product_attribute_datatype,pricing_attribute_datatype,
comparison_operator_code,ATTR_CONTEXT,ATTR_ATTRIBUTE1,ATTR_ATTRIBUTE2,
ATTR_ATTRIBUTE3,ATTR_ATTRIBUTE4,ATTR_ATTRIBUTE5,ATTR_ATTRIBUTE6,
ATTR_ATTRIBUTE7,ATTR_ATTRIBUTE8,ATTR_ATTRIBUTE9,ATTR_ATTRIBUTE10,
ATTR_ATTRIBUTE11,ATTR_ATTRIBUTE12,ATTR_ATTRIBUTE13,ATTR_ATTRIBUTE14,
ATTR_ATTRIBUTE15,ATTR_CREATION_DATE,ATTR_CREATED_BY,ATTR_LAST_UPDATE_DATE,
ATTR_LAST_UPDATED_BY,ATTR_LAST_UPDATE_LOGIN,ATTR_PROGRAM_APPLICATION_ID,
ATTR_PROGRAM_ID,ATTR_PROGRAM_UPDATE_DATE,ATTR_REQUEST_ID,PA_LIST_HEADER_ID,
CONTINUOUS_PRICE_BREAK_FLAG
FROM
qp_list_lines_v WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND EXISTS (SELECT
'1' FROM MTL_SYSTEM_ITEMS mtl WHERE product_attr_value =
mtl.inventory_item_id AND mtl.organization_id = (SELECT
qp_util.get_item_validation_org FROM Dual) AND product_attribute=
'PRICING_ATTRIBUTE1' UNION SELECT '1' FROM dual WHERE product_attribute !=
'PRICING_ATTRIBUTE1') and (LIST_HEADER_ID=:1) and (PA_LIST_HEADER_ID=:2)

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
Responsibility = Order Management Super User (or equivalent)
Navigate to: Price List > Price List Setup
Query price list

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.