Autoinvoice Performance Issue - Stuck On Insert Into AR_LINE_CONTS

(Doc ID 1279669.1)

Last updated on APRIL 12, 2017

Applies to:

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

Symptoms


RAXTRX- Autoinvoice takes over an hour to complete.

Following is the query causing the problem:

INSERT INTO AR_LINE_CONTS ( CUSTOMER_TRX_LINE_ID, CONTINGENCY_CODE,
EXPIRATION_DATE, EXPIRATION_DAYS, COMPLETED_FLAG, REQUEST_ID, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN )
SELECT MAX(CTL.CUSTOMER_TRX_LINE_ID), 'AR_PAYMENT_TERM', NULL, NULL, 'N',
MAX(CTL.REQUEST_ID), :B3 , SYSDATE, :B3 , SYSDATE, :B3 FROM
RA_CUSTOMER_TRX_LINES CTL, RA_CUSTOMER_TRX CT, RA_TERMS_LINES TL,
RA_CUST_TRX_TYPES CTT WHERE CTL.REQUEST_ID = :B2 AND CTL.CUSTOMER_TRX_ID =
CT.CUSTOMER_TRX_ID AND CT.BATCH_SOURCE_ID NOT IN (20, 21) AND
CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CTT.TYPE = 'INV' AND
CTL.LINE_TYPE = 'LINE' AND CT.TERM_ID = TL.TERM_ID AND NOT EXISTS (SELECT
'errors' FROM RA_INTERFACE_ERRORS IE WHERE IE.INTERFACE_LINE_ID =
CTL.CUSTOMER_TRX_LINE_ID) GROUP BY CTL.CUSTOMER_TRX_LINE_ID, TL.TERM_ID
HAVING MAX(DUE_DAYS) > :B1

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