Autoinvoice Performance Issue: Poor Performance While Inserting Into RA_INTERFACE_ERRORS (Doc ID 1351867.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms


RAXTRX module: Autoinvoice Import Program runs very slow after <Patch 10359523> and <Patch 6027573> were applied.

The following insert is showing as the top query in the tkprof, and is the one causing the problem:

insert into RA_INTERFACE_ERRORS(INTERFACE_LINE_ID,MESSAGE_TEXT,INVALID_VALUE,
ORG_ID)select il.interface_line_id ,:b0 ,((inv.trx_number||' : lin num ')
||inv_l.line_number) ,il.org_id from RA_INTERFACE_LINES_GT il ,
AR_NET_REVENUE_AMOUNT nra ,RA_CUSTOMER_TRX_LINES inv_l ,RA_CUSTOMER_TRX inv
,RA_CUST_TRX_TYPES inv_tt ,RA_CUSTOMER_TRX cm ,RA_CUSTOMER_TRX_LINES cm_l
where (((((((((il.request_id=:b1 and il.reference_line_id=
nra.customer_trx_line_id) and il.reference_line_id=
inv_l.customer_trx_line_id) and il.previous_customer_trx_id=
inv.customer_trx_id) and il.customer_trx_id=cm.customer_trx_id) and
cm.customer_trx_id=cm_l.customer_trx_id) and inv.cust_trx_type_id=
inv_tt.cust_trx_type_id) and inv_tt.allow_overapplication_flag='N') and
cm_l.customer_trx_line_id=il.interface_line_id) and
sign((decode(sign(inv_l.extended_amount),1,1,(-1))*
(decode(nvl(il.amount_includes_tax_flag,'X'),'Y',
inv_l.gross_extended_amount,nra.net_amount)+il.amount)))=(-1))




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