Period Close Reconciliation Report Running Long (Doc ID 2960595.1)

Last updated on JULY 17, 2023

Applies to:

Symptoms

 
Period Close Reconciliation Report Running Long

The following INSERT speed affects performance:

SQL ID: c9w9upw2r769p Plan Hash: 4075715159

INSERT INTO CST_INV_QTY_TEMP( QTY_SOURCE, ORGANIZATION_ID, COST_GROUP_ID,
SUBINVENTORY_CODE, INVENTORY_ITEM_ID, ACCOUNTED_VALUE) SELECT 11, :B3
ORGANIZATION_ID, DECODE(MTA.TRANSACTION_SOURCE_TYPE_ID, 5, DECODE( :B2 , 2,
NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.COST_GROUP_ID), MMT.COST_GROUP_ID),
MMT.COST_GROUP_ID), DECODE(MTA.TRANSACTION_SOURCE_TYPE_ID, 5, DECODE( :B2 ,
2, DECODE(MMT.TRANSFER_COST_GROUP_ID, NULL, MMT.SUBINVENTORY_CODE,
MMT.COST_GROUP_ID, MMT.SUBINVENTORY_CODE, NULL), MMT.SUBINVENTORY_CODE),
MMT.SUBINVENTORY_CODE), MMT.INVENTORY_ITEM_ID,
SUM(MTA.BASE_TRANSACTION_VALUE) FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_ACCOUNTS MTA WHERE MTA.ACCOUNTING_LINE_TYPE = 1 AND
MTA.TRANSACTION_DATE >= :B5 AND MTA.TRANSACTION_DATE <= :B4 +1-(1/(24*3600))
AND MTA.ORGANIZATION_ID = :B3 AND (SIGN(MMT.PRIMARY_QUANTITY) =
SIGN(MTA.PRIMARY_QUANTITY) OR MMT.TRANSACTION_ACTION_ID = 24) AND
MMT.TRANSACTION_ID = MTA.TRANSACTION_ID AND MMT.TRANSACTION_TYPE_ID <> 25
GROUP BY DECODE(MTA.TRANSACTION_SOURCE_TYPE_ID, 5, DECODE( :B2 , 2,
NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.COST_GROUP_ID), MMT.COST_GROUP_ID),
MMT.COST_GROUP_ID), DECODE(MTA.TRANSACTION_SOURCE_TYPE_ID, 5, DECODE( :B2 ,
2, DECODE(MMT.TRANSFER_COST_GROUP_ID, NULL, MMT.SUBINVENTORY_CODE,
MMT.COST_GROUP_ID, MMT.SUBINVENTORY_CODE, NULL), MMT.SUBINVENTORY_CODE),
MMT.SUBINVENTORY_CODE), MMT.INVENTORY_ITEM_ID HAVING
(SUM(BASE_TRANSACTION_VALUE) <> 0 OR :B1 = 0)

EXPECTED BEHAVIOR
-----------------------
Expect the report to run in a short amount of time

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. (R) Cost Management -> Requests -> Period Close Reconciliation Report

Cause

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