Performance Issue with AR Process Lockbox Integration with OLFM (Doc ID 3016753.1)

Last updated on APRIL 16, 2024

Applies to:

Symptoms

Find that there is performance issue with lockbox integration with OLFM.

Here is the expensive sql:

SQL ID: 9bk35aw2zx58n Plan Hash: 1058583488

SELECT AR_INVOICE_ID, AR_INVOICE_NUMBER, INVOICE_LINE_ID, LINE_NUMBER,
  NVL(LTB.AMOUNT_DUE_REMAINING,A.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING,
  A.STY_ID
FROM
 OKL_RCPT_CUST_CONT_BALANCES_UV A, OKL_LOCKBOX_TXL_BALANCE LTB,
  OKL_STRM_TYP_ALLOCS B, OKL_BILLING_REFERENCE_DTLS C, OKC_K_HEADERS_B CHR
  WHERE 1 = 1 AND A.ORG_ID = :B4 AND STATUS = 'OP' AND STREAM_ALLC_TYPE = :B3
  AND CAT_ID = :B2 AND A.STY_ID = B.STY_ID AND A.INVOICE_LINE_ID =
  LTB.CUSTOMER_TRX_LINE_ID(+) AND A.AR_INVOICE_ID = C.CUSTOMER_TRX_ID AND
  C.BILLING_REFERENCE_ID = :B1 AND A.KHR_ID = CHR.ID ORDER BY PAYMENT_GROUP,
  INVOICE_DUE_DATE,SEQUENCE_NUMBER,CHR.START_DATE,AR_INVOICE_NUMBER,
  LINE_NUMBER ASC

The issue can be reproduced at will with the following steps:
1. Populate the OLFM contract number or invoice number in the Lockbox
interface table: ar_payments_interface_all
2. Complete setups in AR and OLFM per the note:
Improved Cash Throughput (Doc ID 2840619.1)
3. Call the OKL Hook and observe performance.

Changes

Cause

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