PERIOD CLOSE RECONCILIATION REPORT(CSTRPCRE) DOES NOT END Performance (Doc ID 2027403.1)

Last updated on JUNE 14, 2023

Applies to:

Symptoms

On 12.1.3,
When running Period Close Reconciliation Report(CSTRPCRE), it does not end.

AWR Report shows following SQL taking long time.
-----------------------
INSERT INTO CST_INV_QTY_TEMP( ORGANIZATION_ID, COST_GROUP_ID,
SUBINVENTORY_CODE, INVENTORY_ITEM_ID, ROLLBACK_QTY, QTY_SOURCE, REVISION,
TXN_SOURCE_TYPE_ID, CATEGORY_ID, COST_TYPE_ID ) SELECT /*+ LEADING (MMT)*/
:B3 , MMT.COST_GROUP_ID, MMT.SUBINVENTORY_CODE, MMT.INVENTORY_ITEM_ID,
-1*SUM(MMT.PRIMARY_QUANTITY), 4, DECODE(:B1 , 1, MMT.REVISION, NULL),
MMT.TRANSACTION_SOURCE_TYPE_ID, CILT.CATEGORY_ID, CILT.COST_TYPE_ID FROM
MTL_MATERIAL_TRANSACTIONS MMT, CST_ITEM_LIST_TEMP CILT, CST_CG_LIST_TEMP
CCLT, CST_SUB_LIST_TEMP CSLT WHERE MMT.ORGANIZATION_ID = :B3 AND
CILT.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND CCLT.COST_GROUP_ID =
MMT.COST_GROUP_ID AND CSLT.SUBINVENTORY_CODE = MMT.SUBINVENTORY_CODE AND
MMT.COSTED_FLAG IN ('N', 'E') AND MMT.ORGANIZATION_ID =
NVL(MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID) AND
NVL(MMT.OWNING_TP_TYPE, 2) = 2 AND NVL(MMT.LOGICAL_TRANSACTION, -1) <> 1 AND
MMT.TRANSACTION_ACTION_ID NOT IN (24, 40, 41, 50, 51, 52) AND MMT.CREAT
ION_DATE <= NVL(:B2 , MMT.CREATION_DATE) GROUP BY MMT.COST_GROUP_ID,
MMT.SUBINVENTORY_CODE, MMT.INVENTORY_ITEM_ID, DECODE(:B1 , 1, MMT.REVISION,
NULL), MMT.TRANSACTION_SOURCE_TYPE_ID, CILT.CATEGORY_ID, CILT.COST_TYPE_ID

STEPS
--------------------------------------------------------------------------------------------
The issue can be reproduced at will with the following steps:
1. Run Period Close Reconciliation Report(CSTRPCRE)

Cause

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