My Oracle Support Banner

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

Last updated on JULY 07, 2020

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:

  
<span style="font-size: 12px;" data-mce-style="font-size: 12px;"></span>
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

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
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
 Still Have Questions?
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.