Performance Issue in the 'Create Investor Invoice Disbursements' Program
(Doc ID 2981464.1)
Last updated on OCTOBER 17, 2023
Applies to:
Oracle Lease and Finance Management - Version 12.2.10 and laterInformation in this document applies to any platform.
Symptoms
After applying patch 35500660:R12.OKL.C "1OFF:35500636:UNEXPECT RESULTS AFTER APPLYING PATCH 35378117", the performance of the "Create Investor Invoice Disbursements" program has become an issue. The program is running for more than 20 hours where is used to complete in 6 hours.
This is the long running SQL:
SELECT ARAPP.RECEIVABLE_APPLICATION_ID,
ARAPP.CASH_RECEIPT_ID,
SUM (AD.AMOUNT_CR) AMOUNT_APPLIED,
ARAPP.APPLY_DATE,
ARAPP.CUSTOMER_TRX_ID APPLIED_CUSTOMER_TRX_ID,
ARAPP.INVOICE_NUMBER TRX_NUMBER,
ARAPP.INVOICE_DATE TRX_DATE,
TLD.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL,
TLD.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
TLD.TLD_ID TLD_ID,
TLD.LINE_AMOUNT AMOUNT
FROM OKL_BPD_TLD_AR_LINES_V TLD,
OKL_RECEIPT_APPLICATIONS_UV ARAPP,
AR_DISTRIBUTIONS_ALL AD,
RA_CUSTOMER_TRX_LINES_ALL AC
WHERE TLD.TLD_ID = :B2
AND ARAPP.LINE_APPLIED > 0
AND NVL (ARAPP.CUSTOMER_TRX_LINE_ID, TLD.CUSTOMER_TRX_LINE_ID) =
TLD.CUSTOMER_TRX_LINE_ID
AND ARAPP.CUSTOMER_TRX_ID = TLD.CUSTOMER_TRX_ID
AND AC.INTERFACE_LINE_ATTRIBUTE14 = TO_CHAR (TLD.TLD_ID)
AND AC.LINE_TYPE = 'LINE'
AND AC.LINE_TYPE = ARAPP.LINE_TYPE
AND AC.CUSTOMER_TRX_LINE_ID = TLD.CUSTOMER_TRX_LINE_ID
AND AD.REF_CUSTOMER_TRX_LINE_ID = AC.CUSTOMER_TRX_LINE_ID
AND AC.CUSTOMER_TRX_ID = ARAPP.CUSTOMER_TRX_ID
AND ARAPP.RECEIVABLE_APPLICATION_ID = AD.SOURCE_ID
AND AD.SOURCE_TABLE = 'RA'
AND AC.CUSTOMER_TRX_ID = :B1
AND ARAPP.LINE_NUMBER = AC.LINE_NUMBER
AND NOT EXISTS
(SELECT '1'
FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
WHERE PAY.RECEIVABLE_APPLICATION_ID =
ARAPP.RECEIVABLE_APPLICATION_ID
AND PAY.INVESTOR_AGREEMENT_ID = :B4
AND PAY.INVESTOR_LINE_ID = :B3
AND PAY.TLD_ID = TLD.TLD_ID)
GROUP BY ARAPP.RECEIVABLE_APPLICATION_ID,
ARAPP.CASH_RECEIPT_ID,
ARAPP.APPLY_DATE,
ARAPP.CUSTOMER_TRX_ID,
ARAPP.INVOICE_NUMBER,
ARAPP.INVOICE_DATE,
TLD.AMOUNT_DUE_ORIGINAL,
TLD.AMOUNT_DUE_REMAINING,
TLD.TLD_ID,
TLD.LINE_AMOUNT;
STEPS
1. Using Lease Super User responsibility, author and book a contract.
2. Run billing and generate Invoice(s).
3. Create and activate an Investor Pool and Agreement.
4. Using a Receivables Manager responsibility, create and apply receipts.
5. Using the Lease Administrator, run the "Create Investor Invoice Disbursements" program.
6. Note this is taking an extended time.
Changes
Patch 35500660:R12.OKL.C "1OFF:35500636:UNEXPECT RESULTS AFTER APPLYING PATCH 35378117" was applied.
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 |
Changes |
Cause |
Solution |
References |