R12: AR/CLL: Performance Degraded While Trying to Reconcile Receivables (AR) Cash Receipts. (Doc ID 3071768.1)

Last updated on FEBRUARY 18, 2025

Applies to:

Symptoms

Performance issues when associate charges on the Bank Statement and Reconciliation screen.

Performance degraded when trying to reconcile Receivables (AR) Cash Receipts.

A SQSL*Trace / TKPROF of the issue reproduced shows the following outstanding SQL

SQL ID: 89m4jkt6d55pm Plan Hash: 3965952327           

SELECT CT.TRX_ID , CT.TRX_NUMBER , CT.TRX_TYPE , CT.TYPE_MEANING , 
  CT.TRX_DATE , ABS(CT.AMOUNT )  , CT.CUSTOMER_NAME , CT.BATCH_NAME , 
  CT.BATCH_ID ,  NULL  PAYMENT_FORM , CT.STATUS , CT.STATUS_DSP , CT.ORG_ID , 
  CT.CASH_RECEIPT_ID , 'AR' TRX_SOURCE , CT.AMOUNT ORIGINAL_AMOUNT 
FROM
 CE_222_TRANSACTIONS_V CT   WHERE CT.TRX_DATE >=  NVL(TO_DATE (:b1 , 
  'DD-MM-RRRR' )  , TO_DATE (CT.TRX_DATE , 'DD-MM-RRRR' )  )   AND 
  CT.TRX_DATE <=  NVL(TO_DATE (:b2 , 'DD-MM-RRRR' )  , TO_DATE (CT.TRX_DATE , 
  'DD-MM-RRRR' )  )   AND NVL(CT.ORG_ID , -1 )  =  NVL(:b3 , NVL(CT.ORG_ID , 
  -1 )  )   AND ( NVL(CT.AMOUNT , 0 )  >=  NVL(:b4 , CT.AMOUNT )   AND 
  NVL(CT.AMOUNT , 0 )  <=  NVL(:b5 , CT.AMOUNT )  )  AND ( ( NVL(:b6 , 'N' )  
  =  'Y'  AND CT.BATCH_ID IS NOT NULL  )  OR ( NVL(:b6 , 'N' )  =  'N'  AND 
  CT.BATCH_ID IS NULL  ) )  AND NVL(CT.STATUS ,  NULL  )  =  NVL(:b7 , 
  CT.STATUS )   AND CT.BANK_ACCOUNT_ID =  :b8  AND 0 =  (SELECT COUNT (1) 
  FROM CLL_F035_AR_CASH_REC_HIST_EXT   WHERE CASH_RECEIPT_HISTORY_ID =  
  CT.TRX_ID  AND ( ATTRIBUTE1 IS NOT NULL   OR ATTRIBUTE1 =  'RESERVED' )  
  AND ROWNUM  <  2   )   ORDER BY CT.TRX_DATE DESC 

  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        0      0.00       0.00          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1     86.36      86.36          0      70398          0           0
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        2     86.36      86.36          0      70398          0           0

Steps to reproduce:

1. Log into Oracle Applications using the AR LAD Add-On Localizations responsibility.
2. Open the Bank Statements and Reconciliation screen.
3. Try to reconcile AR Cash Receipts.

Cause

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