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

Last updated on MARCH 27, 2017

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

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