R12:CE: Bank statement import and reconciliation takes a long time to complete.
(Doc ID 1573285.1)
Last updated on APRIL 12, 2019
Applies to:
Oracle Cash Management - Version 12.1.1 to 12.1.2 [Release 12.1]Information in this document applies to any platform.
CE Bank statement import and reconciliation takes a long time to complete.
Symptoms
For an issue we had in CE, we applied some patch and mainly 13697994.
Since then, when we import a bank statement, the system is slow
SQL ID: 3tmdfspg18k1v
Plan Hash: 1385477331
INSERT INTO CE_AVAILABLE_TRANSACTIONS_TMP (SEQ_ID, ROW_ID, MULTI_SELECT,
BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_NAME,
BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER,
CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, GL_DATE,
STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE, MATURITY_DATE,
EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS, BATCH_NAME, BATCH_ID, AGENT_NAME,
CUSTOMER_NAME, PAYMENT_METHOD, VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID,
REFERENCE_TYPE_DSP, REFERENCE_TYPE, REFERENCE_ID, ACTUAL_AMOUNT_CLEARED,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
REMITTANCE_NUMBER, CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED,
BANK_CURRENCY_CODE, TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME,
JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE,
REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID, CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG) SELECT CE_AVAILABLE_TRANSACTIONS_S.NEXTVAL, ROW_ID,
MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM,
BANK_NAME, BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER,
CHECK_NUMBER, CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED,
GL_DATE, STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE,
MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS,
BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, PAYMENT_METHOD,
VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE_DSP, REFERENCE_TYPE,
REFERENCE_ID, ACTUAL_AMOUNT_CLEARED, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, CASH_RECEIPT_ID,
APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID, PERIOD_NAME, JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER, CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT,
RECEIPT_CLASS_ID, RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE,
VALUE_DATE, REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID,
CE_BANK_ACCT_USE_ID, 'N' FROM CE_101_TRANSACTIONS_V WHERE BANK_ACCOUNT_ID =
:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 32.26 46063.90 232842 9990192 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- --------------------
total 2 32.26 46063.90 232842 9990192 0 0
Changes
Patch 13697994 is applied
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |