Performance Issue in the 'Create Investor Invoice Disbursements' Program (Doc ID 2981464.1)

Last updated on OCTOBER 17, 2023

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.