My Oracle Support Banner

AutoReconciliation Program Is Having Performance Issues (Doc ID 1613572.1)

Last updated on FEBRUARY 08, 2018

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Reconciliation

ACTUAL BEHAVIOR
---------------
ARPLABRC module: AutoReconciliation takes several hours to process successfully.

EXPECTED BEHAVIOR
-----------------------
The process should complete in a few minutes.

SELECT DISTINCT(CRH.CASH_RECEIPT_HISTORY_ID), CRH.CASH_RECEIPT_ID, CRH.ROWID,
  DECODE(CR.CURRENCY_CODE, :B7 , CRH.AMOUNT, :B6 , CRH.AMOUNT,
  NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT)), CRH.ACCTD_AMOUNT, CRH.STATUS, DECODE(
  CRH.STATUS, 'CLEARED', CRH.AMOUNT, 'RISK_ELIMINATED', CRH.AMOUNT, 0),
  CR.TYPE, CR.CURRENCY_CODE, DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE,
  'FUNCTIONAL', BA.CURRENCY_CODE, 'BANK', 'FOREIGN'), CRH.AMOUNT, CR.TYPE,
  CRH.EXCHANGE_RATE, CRH.EXCHANGE_DATE, CRH.EXCHANGE_RATE_TYPE, CRH.ORG_ID,
  CR.REMIT_BANK_ACCT_USE_ID
FROM
 GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SP, CE_STATEMENT_RECONCILS_ALL
  REC, CE_BANK_ACCOUNTS BA, CE_BANK_ACCT_USES_OU_V ABA,
  AR_CASH_RECEIPT_HISTORY_ALL CRH, AR_CASH_RECEIPTS_ALL CR,
  AR_RECEIVABLE_APPLICATIONS_ALL RA, HZ_CUST_ACCOUNTS CU, HZ_PARTIES HP,
  AR_PAYMENT_SCHEDULES_ALL PS WHERE SOB.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID
  AND NVL(REC.STATUS_FLAG, 'U') = 'U' AND NVL(REC.CURRENT_RECORD_FLAG,'Y') =
  'Y' AND NVL(REC.REFERENCE_TYPE, 'RECEIPT') IN ('RECEIPT', 'DM REVERSAL')
  AND REC.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID AND CRH.STATUS IN
  ('REMITTED', DECODE(SP.SHOW_CLEARED_FLAG, 'N','REMITTED', 'CLEARED'),
  DECODE(:B4 , 'NSF', 'REVERSED', 'REJECTED', 'REVERSED', 'REMITTED'),
  'RISK_ELIMINATED') AND CRH.CURRENT_RECORD_FLAG = 'Y' AND
  CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND CRH.ORG_ID = CR.ORG_ID AND
  CRH.ORG_ID = REC.ORG_ID (+) AND ABA.BANK_ACCT_USE_ID =
  CR.REMIT_BANK_ACCT_USE_ID AND ABA.ORG_ID = NVL(:B5 , ABA.ORG_ID) AND
  ABA.ORG_ID = CR.ORG_ID AND ABA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID AND
  BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID AND CRH.STATUS = DECODE(:B4 ,
  'NSF', DECODE(:B3 , 'REVERSE',CRH.STATUS, 'REVERSED'), 'REJECTED',
  DECODE('REVERSE', 'REVERSE', CRH.STATUS, 'REVERSED'), CRH.STATUS) AND
  CRH.TRX_DATE >= SP.CASHBOOK_BEGIN_DATE AND CR.CASH_RECEIPT_ID =
  RA.CASH_RECEIPT_ID AND CR.ORG_ID = RA.ORG_ID AND RA.STATUS = 'APP' AND
  RA.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND RA.ORG_ID =
  PS.ORG_ID AND CU.CUST_ACCOUNT_ID = CR.PAY_FROM_CUSTOMER AND HP.PARTY_ID =
  CU.PARTY_ID AND HP.PARTY_NAME = :B2 AND CU.CUST_ACCOUNT_ID =
  NVL(PS.CUSTOMER_ID,CU.CUST_ACCOUNT_ID) AND PS.TRX_NUMBER = :B1

 

SELECT AB.BATCH_ID, AB.ROW_ID, 1, AB.TRX_CURRENCY_TYPE, AB.CURRENCY_CODE,
  'RBATCH', AB.TRX_DATE, AB.GL_DATE, AB.EXCHANGE_RATE, AB.EXCHANGE_RATE_DATE,
  AB.EXCHANGE_RATE_TYPE, AB.ORG_ID, AB.CE_BANK_ACCT_USE_ID, 'CASH'
FROM
 CE_222_BATCHES_V AB WHERE ( AB.BATCH_NUMBER = :B3 OR AB.REMITTANCE_NUMBER =
  :B3 ) AND AB.ORG_ID = NVL(:B2 , AB.ORG_ID) AND AB.BANK_ACCOUNT_ID = :B1

 

Changes

 

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
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
References


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