CRVRI Process Runs for Days with Converted Revolving Loan Contracts (Doc ID 2268152.1)

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.

Symptoms

On : 12.2.5 version, Variable Interest Rate

ACTUAL BEHAVIOR
---------------
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,
  OKL_TRX_AP_INVOICES_V OKL_INV,
  AP_INVOICE_PAYMENT_HISTORY_V IPH,
  OKL_CNSLD_AP_INVS_ALL CNSLD,
  OKL_TXL_AP_INV_LNS_ALL_B OKL_INV_LN,
  FND_APPLICATION FND_APP
  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
UNION ALL
 SELECT CRA.RECEIPT_DATE TXN_DATE,
  SUM (RAA.LINE_APPLIED) TXN_AMOUNT,
  'R' TXN_TYPE
  FROM OKL_BPD_TLD_AR_LINES_V TLD,
  AR_PAYMENT_SCHEDULES_ALL APS,
  AR_RECEIVABLE_APPLICATIONS_ALL RAA,
  AR_CASH_RECEIPTS_ALL CRA,
  OKL_STRM_TYPE_V STY
  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
  AND STY.ST


EXPECTED BEHAVIOR
-----------------------
10 min. would be great since with 93,000 contracts without the converted revolving loans the process completes in no time at all.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run CRVRI process.

Changes

 

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