Periodic Actual Cost Worker CMCPAW Performance Issue in 'UPDATE CST_PAC_PERIOD_BALANCES' (Doc ID 1300579.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

When running CMCPAW getting a  performance issue.

From the TRACE FILE the time is being spent in the following statement:

UPDATE CST_PAC_PERIOD_BALANCES CPPB SET (LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, PERIOD_BALANCE, PERIOD_QUANTITY, PERIODIC_COST,
VARIANCE_AMOUNT) = (SELECT :B8 , SYSDATE, :B7 , :B6 , :B5 , :B4 , SYSDATE,

CPICD.ITEM_BALANCE, CPIC.TOTAL_LAYER_QUANTITY, CPICD.ITEM_COST, (SELECT
SUM
(NVL (MPACD.VARIANCE_AMOUNT, 0)) FROM MTL_PAC_ACTUAL_COST_DETAILS MPACD
WHERE
MPACD.TXN_CATEGORY = :B1 AND MPACD.INVENTORY_ITEM_ID =
CPPB.INVENTORY_ITEM_ID AND MPACD.PAC_PERIOD_ID = :B3 AND
MPACD.COST_GROUP_ID = :B2 AND MPACD.COST_LAYER_ID = CPPB.COST_LAYER_ID AND
MPACD.COST_ELEMENT_ID = CPPB.COST_ELEMENT_ID AND MPACD.LEVEL_TYPE =
CPPB.LEVEL_TYPE) FROM CST_PAC_ITEM_COST_DETAILS CPICD, CST_PAC_ITEM_COSTS
CPIC WHERE CPIC.COST_LAYER_ID = CPICD.COST_LAYER_ID AND CPPB.COST_LAYER_ID
=
CPICD.COST_LAYER_ID AND CPPB.COST_ELEMENT_ID = CPICD.COST_ELEMENT_ID AND
CPPB.LEVEL_TYPE = CPICD.LEVEL_TYPE) WHERE CPPB.PAC_PERIOD_ID = :B3 AND
CPPB.COST_GROUP_ID = :B2 AND CPPB.TXN_CATEGORY = :B1 AND :B1 = (SELECT MAX
(TXN_CATEGORY) FROM MTL_PAC_ACTUAL_COST_DETAILS WHERE PAC_PERIOD_ID = :B3
AND COST_GROUP_ID = :B2 AND INVENTORY_ITEM_ID = CPPB.INVENTORY_ITEM_ID)
AND
EXISTS (SELECT 1 FROM CST_PAC_ITEM_COST_DETAILS CPICD1 WHERE
CPPB.COST_LAYER_ID = CPICD1.COST_LAYER_ID AND CPPB.COST_ELEMENT_ID =
CPICD1.COST_ELEMENT_ID AND CPPB.LEVEL_TYPE = CPICD1.LEVEL_TYPE) AND EXISTS
(SELECT 1 FROM CST_PAC_LOW_LEVEL_CODES CPLLC WHERE CPLLC.INVENTORY_ITEM_ID
=
CPPB.INVENTORY_ITEM_ID AND CPLLC.LOW_LEVEL_CODE = :B9 AND
CPLLC.PAC_PERIOD_ID = :B3 AND CPLLC.COST_GROUP_ID = :B2 )

Cause

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