Query an Item in Organization Item Form is Taking Very Long Time. (Doc ID 850047.1)

Last updated on AUGUST 21, 2016

Applies to:

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

Symptoms

On 11.5.10.2 in Production:
Querying the item is taking very long time from organization item screen. This is taking more time when customer is trying to query the third item.

EXPECTED BEHAVIOR
Expect that the query take less time

STEPS
The issue can be reproduced at will with the following steps:
1) Just go to INV -> Items -> Organization items.
2) Just try to query one item for an organization
3) Try to query for second item(it takes some time to retrieve the item)
4) Try the same step for 3rd item
From the third item it takes very long time to get the result and it is almost hanging.

 

The Most costing scripts were:

SELECT COUNT(1)
FROM
OE_ORDER_LINES_ALL WHERE INVENTORY_ITEM_ID = :B2 AND NVL(OPEN_FLAG,'Y') =
'Y' AND CANCELLED_FLAG = 'N' AND SHIP_FROM_ORG_ID = :B1 AND ROWNUM = 1

SELECT COUNT(1)
FROM
WSH_DELIVERY_DETAILS WHERE INVENTORY_ITEM_ID = :B2 AND PICKABLE_FLAG = 'Y'
AND NVL(INV_INTERFACED_FLAG,'N') NOT IN ('Y','X') AND RELEASED_STATUS <>
'D' AND ORGANIZATION_ID = :B1 AND ROWNUM = 1

SELECT 1
FROM
WSH_DELIVERY_DETAILS WDD WHERE WDD.INVENTORY_ITEM_ID = :B2 AND
WDD.INV_INTERFACED_FLAG IN ('N','P') AND WDD.RELEASED_STATUS <> 'D' AND
WDD.SOURCE_CODE = 'OE' AND WDD.ORGANIZATION_ID = :B1 AND ROWNUM = 1


SELECT COUNT(1)
FROM
OE_ORDER_LINES_ALL L WHERE L.INVENTORY_ITEM_ID = :B2 AND L.OPEN_FLAG || '' =
'Y' AND (L.SHIP_FROM_ORG_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS
WHERE MASTER_ORGANIZATION_ID= :B3 AND 1=(SELECT CONTROL_LEVEL FROM
MTL_ITEM_ATTRIBUTES WHERE ATTRIBUTE_NAME=:B4 ) ) OR L.SHIP_FROM_ORG_ID= :B1
) AND ROWNUM = 1

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