Autoinvoice Performance Issue: AutoInvoice Appears to Hang After Printing the Message AR_IID_INV_WITH_NEG_APP in the Log File (Doc ID 1266030.1)

Last updated on JUNE 08, 2016

Applies to:

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

Symptoms

Running AutoInvoice appears to hang while inserting into RA_INTERFACE_ERRORS_ALL, the last message shown in the log file is:

AR_IID_INV_WITH_NEG_APP

A review of the trace file shows:

insert into RA_INTERFACE_ERRORS(INTERFACE_LINE_ID,MESSAGE_TEXT,INVALID_VALUE)
  select interface_line_id ,:b0 ,trx_number  from RA_INTERFACE_LINES where
  ((trx_number in (select ct.trx_number  from RA_CUSTOMER_TRX CT ,
  RA_INTERFACE_LINES_GT IL ,RA_CUST_TRX_TYPES T where
  ((((((CT.customer_trx_id<>nvl(IL.CUSTOMER_TRX_ID,(-999)) and CT.TRX_NUMBER=
  IL.TRX_NUMBER) and IL.request_id=ct.request_id) and IL.request_id=:b1) and
  CT.request_id=:b1) and IL.CUST_TRX_TYPE_ID=T.CUST_TRX_TYPE_ID) and T.TYPE=
  'CM')) and NVL(INTERFACE_STATUS,'~')<>'P') and request_id=:b1)

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.05          0          0          0           0
Execute      1  19092.01   18861.32        908 1541846712          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2  19092.06   18861.38        908 1541846712          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183  (APPS)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE
      0   HASH JOIN (SEMI)
      0    TABLE ACCESS   GOAL: ANALYZED (FULL) OF
               'RA_INTERFACE_LINES_ALL'
      0    VIEW OF 'VW_NSO_1'
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'RA_INTERFACE_LINES_ALL'
      0      NESTED LOOPS
      0       MERGE JOIN (CARTESIAN)
      0        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'RA_CUSTOMER_TRX_ALL'
      0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'RA_CUSTOMER_TRX_N15' (NON-UNIQUE)
      0        BUFFER (SORT)
      0         TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                    'RA_CUST_TRX_TYPES_ALL'
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                  'RA_INTERFACE_LINES_N1' (NON-UNIQUE)

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