12.2 CSTACRLR: Accrual Reconciliation Load Run Performance (Doc ID 2929016.1)

Last updated on JUNE 19, 2023

Applies to:

Symptoms

Responsibility : Cost Management - SLA

Navigation path:  Tools > Requests >  Submit Request 'Accrual Reconciliation Load Run'

Customer was facing the performance issue while running the Accrual Reconciliation Load Run as observed in TKPROF.  

INSERT INTO CST_RECONCILIATION_GTT ( TRANSACTION_DATE, AMOUNT, ENTERED_AMOUNT,
QUANTITY, CURRENCY_CODE, CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_DATE, PO_DISTRIBUTION_ID,
RCV_TRANSACTION_ID, INVOICE_DISTRIBUTION_ID, INVENTORY_TRANSACTION_ID,
ACCRUAL_ACCOUNT_ID, TRANSACTION_TYPE_CODE, INVENTORY_ITEM_ID, VENDOR_ID,
INVENTORY_ORGANIZATION_ID, WRITE_OFF_ID, DESTINATION_TYPE_CODE,
OPERATING_UNIT_ID, BUILD_ID, REQUEST_ID, AE_HEADER_ID, AE_LINE_NUM ) SELECT
/*+ Leading(POD,POH,MCT,MMT,FSP,XTE) INDEX(XTE XLA_TRANSACTION_ENTITIES_N1)
*/ MMT.TRANSACTION_DATE, ROUND((NVL(XAL.ACCOUNTED_DR,0) -
NVL(XAL.ACCOUNTED_CR,0)) / :B7 ) * :B7 , ROUND((NVL(XAL.ENTERED_DR,0) -
NVL(XAL.ENTERED_CR,0)) / :B7 ) * :B7 , ROUND(NVL(MMT.PRIMARY_QUANTITY,0),20)
, XAL.CURRENCY_CODE, XAL.CURRENCY_CONVERSION_TYPE,
XAL.CURRENCY_CONVERSION_RATE, XAL.CURRENCY_CONVERSION_DATE,
POD.PO_DISTRIBUTION_ID, NULL, NULL, MMT.TRANSACTION_ID,
XAL.CODE_COMBINATION_ID, 'CONSIGNMENT', MMT.INVENTORY_ITEM_ID,
POH.VENDOR_ID, MMT.ORGANIZATION_ID, NULL, POD.DESTINATION_TYPE_CODE, :B4 ,
:B6 , :B5 , XAL.AE_HEADER_ID, XAL.AE_LINE_NUM FROM XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL, XLA_TRANSACTION_ENTITIES_UPG XTE,
MTL_MATERIAL_TRANSACTIONS MMT, CST_ACCRUAL_ACCOUNTS CAA,
FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_DISTRIBUTIONS_ALL POD, PO_HEADERS_ALL
POH, (SELECT DISTINCT TRANSACTION_ID FROM MTL_CONSUMPTION_TRANSACTIONS
START WITH PO_DISTRIBUTION_ID = :B1 CONNECT BY PRIOR TRANSACTION_ID =
PARENT_TRANSACTION_ID) MCT WHERE XAL.CODE_COMBINATION_ID =
CAA.ACCRUAL_ACCOUNT_ID AND CAA.OPERATING_UNIT_ID = :B4 AND FSP.ORG_ID = :B4
AND FSP.SET_OF_BOOKS_ID = XAH.LEDGER_ID AND XAH.AE_HEADER_ID =
XAL.AE_HEADER_ID AND XAH.APPLICATION_ID = 707 AND XAL.APPLICATION_ID = 707
AND XAL.LEDGER_ID = FSP.SET_OF_BOOKS_ID AND XTE.LEDGER_ID =
FSP.SET_OF_BOOKS_ID AND XTE.APPLICATION_ID = 707 AND XTE.ENTITY_ID =
XAH.ENTITY_ID AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS' AND
XAH.GL_TRANSFER_STATUS_CODE = 'Y' AND MMT.TRANSACTION_ID =
NVL(XTE.SOURCE_ID_INT_1,(-99)) AND MMT.TRANSACTION_ACTION_ID IN (6,25) AND
(MCT.TRANSACTION_ID = MMT.TRANSACTION_ID OR MCT.TRANSACTION_ID =
MMT.TRANSFER_TRANSACTION_ID) AND POD.ORG_ID = :B4 AND
POD.PO_DISTRIBUTION_ID = :B2 AND POD.PO_HEADER_ID = POH.PO_HEADER_ID AND
XAL.ACCOUNTING_DATE <= :B3

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5389.34 5620.57 148633491 148852344 105 175
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5389.34 5620.57 148633491 148852344 105 175

Cause

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