Last updated on JULY 25, 2017
Applies to:Oracle Lease and Finance Management - Version 12.2.5 and later
Information in this document applies to any platform.
On : 12.2.5 version, Variable Interest Rate
FInd that there is performance issue with CRVRI process. It runs for days with converted revolving loan contracts
The CRVRI process does not complete with converted revolving loan contracts. The CRVRI process completes in 5 min. for 93,000 contracts with no revolving loans. When we convert another group of 3200 contracts with 108 revolving loans the process runs for days. The sql that is causing this issue is below:
/* Formatted on 3/1/2017 9:17:52 AM (QP5 v5.277) */
SELECT IPH.CHECK_DATE TXN_DATE, SUM (IPH.AMOUNT) TXN_AMOUNT, 'P' TXN_TYPE
FROM AP_INVOICES_ALL AP_INV,
WHERE OKL_INV.ID = OKL_INV_LN.TAP_ID
AND OKL_INV_LN.KHR_ID = :B2
AND AP_INV.APPLICATION_ID = FND_APP.APPLICATION_ID
AND FND_APP.APPLICATION_SHORT_NAME = 'OKL'
AND OKL_INV_LN.CNSLD_AP_INV_ID = CNSLD.CNSLD_AP_INV_ID
AND CNSLD.CNSLD_AP_INV_ID = TO_NUMBER (AP_INV.REFERENCE_KEY1)
AND OKL_INV.FUNDING_TYPE_CODE = 'BORROWER_PAYMENT'
AND AP_INV.INVOICE_ID = IPH.INVOICE_ID
AND IPH.CHECK_DATE <= NVL ( :B1, IPH.CHECK_DATE)
GROUP BY IPH.CHECK_DATE
SELECT CRA.RECEIPT_DATE TXN_DATE,
SUM (RAA.LINE_APPLIED) TXN_AMOUNT,
FROM OKL_BPD_TLD_AR_LINES_V TLD,
WHERE TLD.TRX_STATUS_CODE = 'PROCESSED'
AND TLD.KHR_ID = NVL ( :B2, TLD.KHR_ID)
AND TLD.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RAA.APPLIED_CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND APS.CLASS = 'INV'
AND RAA.APPLICATION_TYPE = 'CASH'
AND RAA.STATUS = 'APP'
AND RAA.DISPLAY = 'Y'
AND CRA.RECEIPT_DATE <= NVL ( :B1, CRA.RECEIPT_DATE)
AND RAA.CASH_RECEIPT_ID = CRA.CASH_RECEIPT_ID
AND TLD.STY_ID = STY.ID
10 min. would be great since with 93,000 contracts without the converted revolving loans the process completes in no time at all.
The issue can be reproduced at will with the following steps:
1. Run CRVRI process.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms