My Oracle Support Banner

General Ledger Transfer Program Performance: Performance Problem Running General Ledger Transfer Program (ARGLTP) (Doc ID 1265033.1)

Last updated on FEBRUARY 12, 2019

Applies to:

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

Symptoms


You are running 'ARGLTP module: General Ledger Transfer Program', and it is taking too long.

Tkprof might show one or all of the following as the top queries:


insert into gl_interface(created_by,date_created,status,actual_flag,group_id,
set_of_books_id,user_je_source_name,user_je_category_name,accounting_date,
subledger_doc_sequence_id,subledger_doc_sequence_value,
ussgl_transaction_code,currency_code,code_combination_id,entered_dr,
entered_cr,accounted_dr,accounted_cr,reference1,reference10,reference21,
reference22,reference23,reference24,reference25,reference26,reference27,
reference28,reference29,reference30,request_id)select /*+ ORDERED USE_NL
(l ctlgd ct ctt cust) INDEX (ctlgd,
RA_CUST_TRX_LINE_GL_DIST_N5) +*/ :b0 created_by ,trunc(sysdate )
date_created ,:b1 status ,:b2 actual_flag ,:b3 group_id ,:b4 sob_id ,:b5
source_name ,decode(ctt.type,'CM',:b6,'DM',:b7,'CB',:b8,:b9) category ,
ctlgd.gl_date gl_date ,ct.doc_sequence_id doc_seqid ,ct.doc_sequence_value
doc_num ,ctlgd.ussgl_transaction_code ussgl ,ct.invoice_currency_code
currency ,ctlgd.code_combination_id ccid ,decode(ctlgd.account_class,'REC',
decode(sign(nvl(ctlgd.amount,0)),(-1),null ,nvl(ctlgd.amount,0)),'ROUND',
decode(sign(nvl(ctlgd.acctd_amount,0)),(-1),(-nvl(ctlgd.amount,0)),null ),
decode(sign(nvl(ctlgd.amount,0)),(-1),(-nvl(ctlgd.amount,0)),null ))
entered_dr ,decode(ctlgd.account_class,'REC',decode(sign(nvl(ctlgd.amount,0)
),(-1),(-nvl(ctlgd.amount,0)),null ),'ROUND',
decode(sign(nvl(ctlgd.acctd_amount,0)),(-1),null ,nvl(ctlgd.amount,0)),
decode(sign(nvl(ctlgd.amount,0)),(-1),null ,nvl(ctlgd.amount,0)))
entered_cr ,decode(ctlgd.account_class,'REC',decode(sign(nvl(ctlgd.amount,0)
),(-1),null ,nvl(ctlgd.acctd_amount,0)),'ROUND',
decode(sign(nvl(ctlgd.acctd_amount,0)),(-1),(-nvl(ctlgd.acctd_amount,0)),
null ),decode(sign(nvl(ctlgd.amount,0)),(-1),(-nvl(ctlgd.acctd_amount,0)),
null )) acctd_dr ,decode(ctlgd.account_class,'REC',
decode(sign(nvl(ctlgd.amount,0)),(-1),(-nvl(ctlgd.acctd_amount,0)),null ),
'ROUND',decode(sign(nvl(ctlgd.acctd_amount,0)),(-1),null ,
nvl(ctlgd.acctd_amount,0)),decode(sign(nvl(ctlgd.amount,0)),(-1),null ,
nvl(ctlgd.acctd_amount,0))) acctd_cr ,(:b10||to_char(:b3)) ref1 ,
decode(:b12,'Y',null ,((((((l.meaning||' ')||:b13)||decode(ctt.type,'CB',
:b14,'CM',:b15,'DEP',:b16,'DM',:b17,'GUAR',:b18,'INV',:b19,null ))||' ')
||ct.trx_number)||:b20)) ref10 ,to_char(:b3) ref21 ,
to_char(ct.customer_trx_id) ref22 ,to_char(ctlgd.cust_trx_line_gl_dist_id)
ref23 ,ct.trx_number ref24 ,cust.account_number ref25 ,'CUSTOMER' ref26 ,
to_char(ct.bill_to_customer_id) ref27 ,decode(ctt.type,'CM','CM','DM','DM',
'CB','CB','INV') ref28 ,(decode(ctt.type,'CM','CM_','DM','DM_','CB','CB_',
'INV_')||ctlgd.account_class) ref29 ,'RA_CUST_TRX_LINE_GL_DIST' ref30 ,:b22
from ra_cust_trx_line_gl_dist ctlgd ,ra_cust_trx_line_gl_dist rec ,
ar_lookups l ,ra_customer_trx ct ,ra_cust_trx_types ctt ,hz_cust_accounts
cust where ((((((((((((((((ctlgd.cust_trx_line_gl_dist_id+0)<:b23 and
ctlgd.gl_date between to_date((:b24||' 00:00:00'),'DD-MON-RR HH24:MI:SS')
and to_date((:b25||' 23:59:59'),'DD-MON-RR HH24:MI:SS')) and
ctlgd.posting_control_id=(-3)) and ctlgd.set_of_books_id=:b4) and
ctlgd.account_set_flag='N') and ctlgd.customer_trx_id=ct.customer_trx_id)
and l.lookup_type='AUTOGL_TYPE') and l.lookup_code=nvl(ctlgd.account_class,
'REV')) and ct.complete_flag='Y') and ct.bill_to_customer_id=
cust.cust_account_id) and ct.cust_trx_type_id=ctt.cust_trx_type_id) and
ctlgd.customer_trx_id=rec.customer_trx_id) and rec.account_class='REC') and
rec.latest_rec_flag='Y') and rec.account_set_flag='N') and 'NOT_MFAR_TYPE'=
AR_MFAR_PKG.ar_mfar_trx_post(ctlgd.customer_trx_id,:b0,:b1,:b2,:b3,:b4,:b5,
:b6,:b7,:b8,:b9,:b10,:b12,:b13,:b20,:b14,:b15,:b17,:b16,:b18,:b19,:b24,:b25,
:b23))



update ra_cust_trx_line_gl_dist set gl_posted_date=:b0,posting_control_id=
:b1,last_update_date=sysdate ,last_updated_by=:b2
where
(cust_trx_line_gl_dist_id in (select to_number(reference23) from
gl_interface where ((((user_je_source_name=:b3 and set_of_books_id=:b4) and
group_id=:b1) and (reference30||'')='RA_CUST_TRX_LINE_GL_DIST') and
accounting_date between to_date((:b6||' 00:00:00'),'DD-MON-RR HH24:MI:SS')
and to_date((:b7||' 23:59:59'),'DD-MON-RR HH24:MI:SS'))) and
set_of_books_id=:b4)

 

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
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.