My Oracle Support Banner

PERFORMACE ISSUE CREATING A QUOTE FROM AN OPPORTUNITY. (Doc ID 967965.1)

Last updated on FEBRUARY 11, 2021

Applies to:

Oracle Quoting - Version 12.0.1 to 12.2.8 [Release 12.0 to 12.2]
Information in this document applies to any platform.

Goal

User experienced poor performance when creating a quote from an opportunity.

The tkprof report shows poor performance on the following SQL

...
  
FROM ASO_OA_QUOTE_LINES_ALL_V QuoteLineEO,
ASO_QUOTE_LINE_DETAILS qte_line_det,
MTL_SYSTEM_ITEMS_VL ITEMS,
ASO_SHIPMENTS SHIP,
MTL_UNITS_OF_MEASURE_VL mc,
MTL_UNITS_OF_MEASURE_VL MUV,
oe_transaction_types_tl ottt,
fnd_lookup_values cat_lok
WHERE QuoteLineEO.QUOTE_LINE_ID = qte_line_det.QUOTE_LINE_ID (+) AND
QuoteLineEO.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND
QuoteLineEO.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND
QuoteLineEO.QUOTE_HEADER_ID = SHIP.QUOTE_HEADER_ID AND
QuoteLineEO.QUOTE_LINE_ID = SHIP.QUOTE_LINE_ID AND
QuoteLineEO.charge_periodicity_code = mc.uom_code (+) AND
mc.uom_class(+) = fnd_profile.value('ONT_UOM_CLASS_CHARGE_PERIODICITY') AND
QuoteLineEO.line_category_code = cat_lok.lookup_code (+) AND
cat_lok.lookup_type(+) = 'LINE_CATEGORY' AND
cat_lok.language(+) = userenv('LANG') AND
QuoteLineEO.uom_code = muv.uom_code and
QuoteLineEO.ORDER_LINE_TYPE_ID = ottt.transaction_type_id(+) AND ottt.LANGUAGE(+) =
USERENV('LANG')) QRSLT WHERE REF_LINE_ID = :1 ORDER BY LINE_NUMBER,
DECODE(SERVICE_REF_TYPE_CODE,'QUOTE', (SELECT BOM_SORT_ORDER FROM ASO_QUOTE_LINE_DETAILS WHERE
QUOTE_LINE_ID= SERVICE_REF_LINE_ID), BOM_SORT_ORDER), DECODE(SERVICE_REF_TYPE_CODE,'QUOTE',
SERVICE_REF_LINE_ID, QUOTE_LINE_ID), DECODE(SERVICEABLE_PRODUCT_FLAG,'Y',0, QUOTE_LINE_ID)
  
This is taking close to 100 seconds and is querying over 10's of million blocks



Solution

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
Goal
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.