GLXRCAUT: Automatic Reconciliation Cannot Complete With Large Number Of Unreconciled Lines (Doc ID 1941648.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle General Ledger - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
GLXRCAUT Automatic Reconciliation

Symptoms

ACTUAL BEHAVIOR
Performance issue with Automatic Reconciliation (GLXRCAUT) not running with
large number of unreconciled lines

Additional info:
Applied patch 13019169:R12.GL.B but issue still remains

Trace shows following expensive statement causing the issue

SELECT SEGMENT1||'
'||SEGMENT2||'
'||SEGMENT3||'
'||SEGMENT4||'
'||SEGMENT5||'
'||SEGMENT6||'
'||SEGMENT7||'
'||SEGMENT8||'
'||SEGMENT9 ACC_FLEX , c . code_combination_id CODE_COMBINATION_ID , c .
chart_of_accounts_id CHART_OF_ACCOUNTS_ID , b . name BATCH , h . name JOURNAL
, h . period_name PERIOD , l . effective_date EFFECTIVE_DATE , h .
currency_code CURRENCY_CODE , l . entered_dr ENTERED_DR , l . entered_cr
ENTERED_CR , l . accounted_dr ACCOUNTED_DR , l . accounted_cr ACCOUNTED_CR ,
l . je_header_id JE_HEADER_ID , r . je_line_num JE_LINE_NUM , nvl ( r .
jgzz_recon_ref , '-9' ) REFERENCE , C.SEGMENT1 COMP_SEG , C.SEGMENT2
ACCT_SEG , decode ( : P_RECON_RULE , '4' , NULL , C.SEGMENT1 ) G_COMP_SEG ,
decode ( : P_RECON_RULE , '3' , NULL , '4' , NULL , C.SEGMENT2 ) G_COMP_ACCT
, decode ( : P_RECON_RULE , '2' , NULL , r . jgzz_recon_ref ) G_REFERENCE ,
decode ( : P_RECON_RULE , '4' , SEGMENT1||'
'||SEGMENT2||'
'||SEGMENT3||'
'||SEGMENT4||'
'||SEGMENT5||'
'||SEGMENT6||'
'||SEGMENT7||'
'||SEGMENT8||'
'||SEGMENT9 , NULL ) G_ACCOUNT FROM gl_je_lines_recon r , gl_je_lines l ,
gl_je_headers h , gl_je_batches b , gl_period_statuses ps ,
gl_code_combinations c WHERE c.code_combination_id = l.code_combination_id
AND r.je_header_id = l.je_header_id AND r.je_line_num = l.je_line_num AND
r.ledger_id = : P_LEDGER_ID AND l.ledger_id = : P_LEDGER_ID AND
l.je_header_id = h.je_header_id AND h.je_batch_id = b.je_batch_id AND
h.actual_flag = 'A' AND l.period_name = ps.period_name AND ps.application_id
= 101 AND ps.ledger_id = : P_LEDGER_ID AND c.chart_of_accounts_id = :
C_STRUCT_NUM AND r.jgzz_recon_status = 'U' AND l.status = 'P' AND h.status =
'P' AND : P_LEDGER_CURRENCY IS NOT NULL AND ( l.LEDGER_ID , C.SEGMENT1 ) IN (
SELECT acc.ledger_id , acc.segment_value FROM gl_access_set_assignments acc
WHERE acc.access_set_id = 1021 AND acc.access_privilege_code IN ( 'B' , 'F' )
) AND C.SEGMENT1 = '001' AND C.SEGMENT2 = '20100' AND C.SEGMENT3 = '000' AND
C.SEGMENT4 = '0000' AND C.SEGMENT5 = '00000' AND C.SEGMENT6 = '0000' AND
C.SEGMENT7 = '00' AND C.SEGMENT8 = '00000' AND C.SEGMENT9 = '00000' AND
h.currency_code = : P_LEDGER_CURRENCY AND r.JGZZ_RECON_REF is not null AND
c.jgzz_recon_flag = 'Y' AND l.effective_date BETWEEN greatest ( :
CP_PERIOD_START , NVL ( : P_START_DATE , SYSDATE - 9999 ) ) AND least ( :
CP_PERIOD_END , NVL ( : P_END_DATE , SYSDATE ) ) ORDER BY 18 ASC,20 ASC,19
ASC,21 ASC , 1 , h.currency_code , l.effective_date , h.name , l.je_line_num

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