CRVRI Process Runs for Days with Converted Revolving Loan Contracts
(Doc ID 2268152.1)
Last updated on FEBRUARY 25, 2019
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.
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