Performance For Custom Price Book Using API QP_PREQ_PUB.PRICE_REQUEST (Doc ID 2267602.1)

Last updated on MAY 18, 2017

Applies to:

Oracle Advanced Pricing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Pricing Engine

ACTUAL BEHAVIOR
---------------
Performance for Custom Price Book Using API QP_PREQ_PUB.PRICE_REQUEST

When using this QP_PREQ_PUB.PRICE_REQUEST API in one of the custom program which usually takes nearly 10 mins to run and now after this patch it is taking more then an hour.

Top sql is:

SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq QP_QUALIFIERS_N7)
index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2)
l_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID,
QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG,
QPQ.SEARCH_IND, :B9 VALIDATED_FLAG, :B9 APPLIED_FLAG, :B10 ATTRIBUTE_LEVEL,
:B2 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM ,
QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE ,
QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE ,
QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE,
:B9 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE,
QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ' , NULL LINE_INDEX, NULL
PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST,
QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B8
PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' ||
QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE
START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H,
QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND,
QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND,
QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE,
QPH.LIMIT_EXISTS_FLAG, QPLATQ.DERIVED_QUALIFIER_FLAG......


 
EXPECTED BEHAVIOR
-----------------------
Time expectation is lesser then 10 mins. It is now taking more then an hour.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. via custom program , launch the API QP_PREQ_PUB.PRICE_REQUEST

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot get the custom program to perform in a timely manner

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms