Performance Problem When Deleting Items (BMCDEL) (Doc ID 1063320.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Item Master - Version: 11.5.10.0 to 11.5.10.0 - Release: 11.5 to 11.5
Information in this document applies to any platform.

Symptoms

Find a performance problem with program BMCDEL when  deleting  item from Applications.
 
The bottleneck query was identified as :


SELECT 1
FROM DUAL
WHERE &ORGANIZATION_ID =
(select to_number(profile_option_value)
from fnd_profile_options fpo,
fnd_profile_option_values fpov
where fpo.profile_option_name ='SP_ORGANIZATION_ID'
and fpo.profile_option_id = fpov.profile_option_id
and fpo.application_id = fpov.application_id
and fpov. level_id = 10001)
AND EXISTS
(select NULL
from qp_pricing_attributes
where product_attribute_context = 'ITEM'
and product_attribute = 'PRICING_ATTRIBUTE1'
and product_attr_value IS NOT NULL
and product_attr_value <> 'ALL'
and product_attr_value = &ITEM_ID
UNION
select NULL
from qp_pricing_attributes
where product_attribute_context = 'ITEM'
and product_attribute = 'PRICING_ATTRIBUTE1'
and product_attr_value IS NOT NULL
and comparison_operator_code = '='
and pricing_attr_value_from = &ITEM_ID)

The issue can be reproduced at will with the following steps:
1. Inventory responsibility
2. Items
3. Delete items.


.

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