My Oracle Support Banner

AutoInvoice Performance Issue: Poor Performance When Inserting into RA_INTERFACE_ERRORS table (Doc ID 1329285.1)

Last updated on JANUARY 04, 2021

Applies to:

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

Symptoms

Poor performance of Import process in AutoInvoice. The issue was traced to many self-joins to RA_INTERFACE_ERRORS and joins to RA_CUST_TRX_TYPES table.

Review of TKPROF shows performance in the following query:

  insert into RA_INTERFACE_ERRORS
  (INTERFACE_LINE_ID,MESSAGE_TEXT,INVALID_VALUE)
   select L.INTERFACE_LINE_ID ,:b0 ,L.MANDATE_LAST_TRX_FLAG  from
   RA_INTERFACE_LINES_GT L where (((L.REQUEST_ID=:b1 and L.CUSTOMER_TRX_ID is
 
   not null ) and NVL(L.INTERFACE_STATUS,'~')<>'P') and L.CUSTOMER_TRX_ID in
   (select L.CUSTOMER_TRX_ID  from RA_INTERFACE_LINES_GT L ,RA_CUST_TRX_TYPES
   TT where ((((L.REQUEST_ID=:b1 and NVL(L.INTERFACE_STATUS,'~')<>'P') and
   TT.CUST_TRX_TYPE_ID=TT.CUST_TRX_TYPE_ID) and TT.TYPE in ('INV','DM')) and
   L.LINE_TYPE in ('LINE','CHARGES')) group by L.CUSTOMER_TRX_ID having
   count(distinct nvl(UPPER(L.MANDATE_LAST_TRX_FLAG),'N'))>1))
 
 
 
 

 

Changes

 

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