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
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.