OEXOEORD: Performance: System Is taking Long Time to Save Sales Order Line , When a Price List with Huge Number of Qualifiers Is Involved (Doc ID 1947636.1)

Last updated on MARCH 01, 2017

Applies to:

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

Symptoms


System takes long time to save Order Lines , and the most expensive sql during this process is

SELECT COUNT(*) NO_OF_QUALIFIERS_IN_GRP
FROM
 QP_QUALIFIERS A , QP_LIST_HEADERS_B B WHERE A.LIST_HEADER_ID = :B1 AND
  A.LIST_LINE_ID = -1 AND A.QUALIFIER_CONTEXT = 'MODLIST' AND
  A.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4' AND A.LIST_HEADER_ID =
  B.LIST_HEADER_ID AND B.LIST_TYPE_CODE IN ('AGR', 'PRL')


Performance issue is seen only when price for the Items on the Lines is derived from one specific price list

For example in this case , problematic price list has around 350 qualifiers.


STEPS

The issue can be reproduced at will with the following steps:

  1.  Order Management responsibility
  2.  Orders, Returns > Sales Order Form
  3.  Create Order Header and Go to Line Items
  4.  Enter Ordered Item, Quantity and tab out (this step is taking long time).
  5.  Save the line (System is taking long time to save the line).




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