Performance issue with General Ledger Reconciliation Report (CEXRECRE) (Doc ID 1354039.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version: 11.5.10.2 and later   [Release: 11.5 and later ]
Information in this document applies to any platform.
Performance issue with General Ledger Reconciliation Report (CEXRECRE)

$Header CEXRECRE.rdf 115.47 2004/12/17 07:30 lkwan ship

Symptoms

The General Ledger Reconciliation Report takes well over an hour to complete

SELECT 'RECEIPT' C_AR_TYPE,
DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE,
DECODE(crh.status, 'REVERSED', - crh.acctd_amount, crh.acctd_amount),
DECODE(crh.status, 'REVERSED', - crh.amount, crh.amount)) C_AR_AMOUNT,
rc.customer_name C_AR_CUSTOMER_NAME,
crh.gl_date C_AR_GL_DATE,
cr.receipt_date C_AR_REMIT_DATE,
arm.name C_AR_PAYMENT_METHOD,
cr.receipt_number C_AR_RECEIPT_NUMBER,
cr.currency_code C_AR_CURRENCY,
cr.amount C_AR_TRANS_AMOUNT,
crh.status C_AR_STATUS
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
ra_customers rc,
ar_receipt_methods arm
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND cr.remittance_bank_account_id = :P_BANK_ACCOUNT_ID
AND crh.account_code_combination_id = to_number(:C_ASSET_CC_ID)
AND crh.status in ('REMITTED', 'CLEARED', 'REVERSED')
AND crh.gl_date <= :C_AS_OF_DATE
AND crh.gl_date >= :C_CASHBOOK_BEGIN_DATE
AND crh.gl_posted_date is not null
AND crh.current_record_flag = 'Y'
AND rc.customer_id(+) = cr.pay_from_customer
AND arm.receipt_method_id = cr.receipt_method_id
AND nvl(cr.status, 'X') = decode(nvl(cr.status,'X'),'REV','U',nvl(cr.status,'X'))
AND NOT EXISTS
(select null
from ce_statement_reconciliations sr,
ce_statement_lines sl,
ce_statement_headers sh
where sr.reference_id = crh.cash_receipt_history_id
and sr.reference_type = 'RECEIPT'
and sr.status_flag = 'M'
and sr.current_record_flag = 'Y'
and sl.statement_line_id = sr.statement_line_id
and sl.statement_header_id = sh.statement_header_id
and sh.bank_account_id = :P_BANK_ACCOUNT_ID
and sh.statement_date <= :C_AS_OF_DATE
and sh.statement_date >= :C_CASHBOOK_BEGIN_DATE
UNION
select null
from ce_statement_reconciliations sr,
ce_statement_lines sl,
ce_statement_headers sh,
ar_cash_receipt_history crh_rc
where sr.reference_id = crh_rc.cash_receipt_history_id
and sr.reference_type = 'RECEIPT'
and sr.status_flag = 'M'
and sr.current_record_flag = 'Y'
and sl.statement_line_id = sr.statement_line_id
and sl.statement_header_id = sh.statement_header_id
and sh.bank_account_id = :P_BANK_ACCOUNT_ID
and sh.statement_date <= :C_AS_OF_DATE
and sh.statement_date >= :C_CASHBOOK_BEGIN_DATE
and crh_rc.cash_receipt_id = cr.cash_receipt_id
and crh.status <> 'REVERSED' --bug 5671653 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=5671653]
)
UNION ALL
SELECT 'RECEIPT' C_AR_TYPE,
DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE, crh.acctd_amount, crh.amount) C_AR_AMOUNT,
rc.customer_name C_AR_CUSTOMER_NAME,
crh.gl_date C_AR_GL_DATE,
cr.receipt_date C_AR_REMIT_DATE,
arm.name C_AR_PAYMENT_METHOD,
cr.receipt_number C_AR_RECEIPT_NUMBER,
cr.currency_code C_AR_CURRENCY,
cr.amount C_AR_TRANS_AMOUNT,
crh.status C_AR_STATUS
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh2,
ar_cash_receipt_history crh,
ra_customers rc,
ar_receipt_methods arm
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND cr.remittance_bank_account_id = :P_BANK_ACCOUNT_ID
AND crh.account_code_combination_id = to_number(:C_ASSET_CC_ID)
AND crh.status in ('REMITTED', 'CLEARED')
AND crh.gl_date <= :C_AS_OF_DATE
AND crh.gl_date >= :C_CASHBOOK_BEGIN_DATE
AND crh.gl_posted_date is not null
AND crh2.cash_receipt_id = crh.cash_receipt_id
AND crh2.cash_receipt_history_id = crh.reversal_cash_receipt_hist_id
AND crh2.status = 'REVERSED'
AND rc.customer_id(+) = cr.pay_from_customer
AND arm.receipt_method_id = cr.receipt_method_id
AND nvl(cr.status, 'X') = decode(nvl(cr.status,'X'),'REV','U',nvl(cr.status,'X'))
AND NOT EXISTS
(select null
from ce_statement_reconciliations sr,
ce_statement_lines sl,
ce_statement_headers sh
where sr.reference_id = crh.cash_receipt_history_id -- bug 5671653 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=5671653] crh2.cash_receipt_history_id
and sr.reference_type = 'RECEIPT'
and sr.status_flag = 'M'
and sr.current_record_flag = 'Y'
and sl.statement_line_id = sr.statement_line_id
and sl.statement_header_id = sh.statement_header_id
and sh.bank_account_id = :P_BANK_ACCOUNT_ID
and sh.statement_date <= :C_AS_OF_DATE
and sh.statement_date >= :C_CASHBOOK_BEGIN_DATE)
ORDER BY C_AR_GL_DATE, C_AR_CUSTOMER_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 1 91.28 911.21 2103171 2136741 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 91.33 911.26 2103171 2136741 0 0

Changes

NONE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms