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

Last updated on DECEMBER 03, 2016

Applies to:

Oracle Cost Management - Version 12.1.3 and later
Information in this document applies to any platform.

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

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