R12: CE/PAY: Slow Performance when Accessing Available Payroll EFT Type Transactions While Trying to Reconcile (Doc ID 2617836.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

When trying to reconcile Payroll EFT payments and clicking on the Available button, the system does not respond in a timely manner.

The AWR report shows the following query against CE_801_EFT_TRANSACTIONS_V to be the cause of the slowdown.

SELECT MULTI_SELECT, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, CURRENCY_CODE,
AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, COUNT_CLEARED,
ACTUAL_AMOUNT_CLEARED, CLEARED_DATE, VALUE_DATE, GL_DATE, STATUS_DSP, STATUS,
TRX_DATE, MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS,
BATCH_NAME, PSON, AGENT_NAME, REMITTANCE_NUMBER, REFERENCE_TYPE_DSP,
REFERENCE_TYPE, REFERENCE_ID, APPLICATION_ID, CASH_RECEIPT_ID,
CODE_COMBINATION_ID, BANK_ACCOUNT_ID, ROW_ID, TRX_ID, TRX_CURRENCY_TYPE,
TRX_TYPE, CUSTOMER_NAME, VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, PERIOD_NAME,
JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER,
JOURNAL_CATEGORY, BASE_AMOUNT, CLEARING_TRX_TYPE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID, DEPOSIT_DATE, REVERSED_RECEIPT_FLAG, ORG_ID,
LEGAL_ENTITY_ID FROM CE_801_EFT_TRANSACTIONS_V WHERE bank_account_id between
:1 and :2 AND (trx_type in ('PAYMENT' )) order by TYPE_MEANING ASC,
RegExp_Replace (RegExp_Replace (TRX_NUMBER, '(\d+)', lpad('\1=', 243, '0')),
rpad('0+(', 243 , lpad(')\=', 240 , '.')), '\1') ASC

Cause

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