Bad Performance on Inventory Valuation Report. It Takes Long Time for completing. (Doc ID 1638714.1)

Last updated on APRIL 04, 2025

Applies to:

Goal

Run standard reports: "Inventory Value Report - by Cost Group" and "All Inventories Value Report" take long time, more than 20 hours and the status keep running-normal.

CR_hang_sql_text.sql output show that process is still on the same SQL_TEXT :

INSERT INTO CST_ITEM_LIST_TEMP( INVENTORY_ITEM_ID, CATEGORY_ID,
COST_TYPE_ID ) SELECT MSI.INVENTORY_ITEM_ID, MIC.CATEGORY_ID, CI
C.COST_TYPE_ID FROM MTL_ITEM_CATEGORIES MIC, MTL_SYSTEM_ITEMS_KF
V MSI, CST_ITEM_COSTS CIC, CST_ITEM_COSTS CIC1, CST_ITEM_COSTS C
IC2 WHERE MIC.CATEGORY_SET_ID = :B10 AND MIC.ORGANIZATION_ID = :
B9 AND MSI.ORGANIZATION_ID = :B9 AND MSI.INVENTORY_ITEM_ID = MIC
.INVENTORY_ITEM_ID AND MSI.CONCATENATED_SEGMENTS BETWEEN NVL(:B8
,MSI.CONCATENATED_SEGMENTS) AND NVL(:B7 ,MSI.CONCATENATED_SEGME
NTS) AND CIC1.ORGANIZATION_ID (+) = :B6 AND CIC2.ORGANIZATION_ID
(+) = :B6 AND CIC1.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_I
D AND CIC2.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID AND CIC
1.COST_TYPE_ID (+) = NVL(:B5 ,:B4 ) AND CIC2.COST_TYPE_ID (+) =
:B3 AND CIC.ROWID = NVL(CIC1.ROWID,CIC2.ROWID) AND CIC.INVENTORY
_ASSET_FLAG = DECODE(:B2 ,1,CIC.INVENTORY_ASSET_FLAG,1) AND NVL(
CIC.ITEM_COST,0) = DECODE(:B1 , 1,0, NVL(CIC.ITEM_COST,0) )
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.