AutoInvoice Performance Issue: Improper Hint in Package ARP_ROUNDING (Doc ID 1954245.1)

Last updated on AUGUST 28, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, Transactions Related

Find that autoinvoice is running long.

See the following as the long running statement:

  

UPDATE RA_CUST_TRX_LINE_GL_DIST LGD 
SET (AMOUNT, ACCTD_AMOUNT) = (SELECT /* + index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered */ NVL(LGD.AMOUNT, 0)
     - (SUM(LGD2.AMOUNT) - ( DECODE(LGD.GL_DATE, REC1.GL_DATE, 1, 0) * 
    CTL.EXTENDED_AMOUNT ) ), NVL(LGD.ACCTD_AMOUNT, 0) - (SUM(LGD2.ACCTD_AMOUNT)
     - ( DECODE(LGD.GL_DATE, REC1.GL_DATE, 1, 0) * DECODE(:B2 , NULL, ROUND( 
    CTL.EXTENDED_AMOUNT * NVL(CT.EXCHANGE_RATE,1), :B3 ), ROUND( ( 
    CTL.EXTENDED_AMOUNT * NVL(CT.EXCHANGE_RATE,1) ) / :B2 ) * :B2 ) ) ) FROM 
    RA_CUSTOMER_TRX_LINES CTL, RA_CUSTOMER_TRX CT, RA_CUST_TRX_LINE_GL_DIST 
    LGD2, RA_CUST_TRX_LINE_GL_DIST REC1 
    
UNION
   SELECT
    /*+ index(rec5 RA_CUST_TRX_LINE_GL_DIST_N6) INDEX
    (lgd5 ra_cust_trx_line_gl_dist_n7) */
  
FROM RA_CUST_TRX_LINE_GL_DIST LGD5,
    RA_CUST_TRX_LINE_GL_DIST REC5      ,
    RA_CUSTOMER_TRX_LINES CTL2         ,
    RA_CUSTOMER_TRX T
    WHERE T.REQUEST_ID          = :B5
  AND T.CUSTOMER_TRX_ID         = REC5.CUSTOMER_TRX_ID
  AND CTL2.CUSTOMER_TRX_LINE_ID = LGD5.CUSTOMER_TRX_LINE_ID
  AND REC5.CUSTOMER_TRX_ID      = LGD5.CUSTOMER_TRX_ID
  AND REC5.ACCOUNT_CLASS        = 'REC'
  AND REC5.LATEST_REC_FLAG      = 'Y'
  AND (CTL2.LINE_TYPE          IN ( 'TAX','FREIGHT','CHARGES','SUSPENSE')
  OR (CTL2.LINE_TYPE            = 'LINE'
  AND (CTL2.ACCOUNTING_RULE_ID IS NULL
  OR LGD5.ACCOUNT_SET_FLAG      = 'Y' )))
    
  call     count       cpu    elapsed       disk      query    current      rows
  ------- ------  -------- ---------- ---------- ---------- ----------   ----------
  Parse        1      0.00       0.00          0          0          0        0
  Execute      1     69.26     104.04     635394     666491          0        0
  Fetch        0      0.00       0.00          0          0          0        0
  ------- ------  -------- ---------- ---------- ---------- ----------   ----------
  total        2     69.26     104.04     635394     666491          0        0
 

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