Advanced Collections Performance In Searching Customer By Transaction In R12 After Patch 9861798 Is Applied (Doc ID 1428077.1)

Last updated on JULY 18, 2016

Applies to:

Oracle Advanced Collections - Version 12.1.3 and later
Information in this document applies to any platform.
Form:IEXRCALL.FMB - IEX Startup Form

***Checked for relevance on 27-SEP-2013***

Symptoms

There is a performance issue in IEXRCALL when searching on transaction number.  The problem sql is the following:

SELECT ACCOUNT_NUMBER COL1, ROLE_TYPE COL2, FIRST_NAME COL3, MEANING COL4,LAST_NAME COL5, ADDRESS COL6, CITY COL7, COUNTRY COL8, COUNTRY_NAME COL9,CUST_ACCOUNT_ID COL10, EMAIL_ADDRESS COL11, PARTY_ID COL12, PARTY_NAME COL13, POSTAL_CODE COL14, PROVINCE COL15, STATE COL16, STATUS_MEAN COL17, URL COL18, ORIG_SYSTEM_REFERENCE COL19
FROM IEX_LS_ACC_TRX_QUICK_V WHERE 'JTFBIND' = :vJTFBIND and cust_account_id in (
select unique a.Bill_to_customer_id from ra_customer_trx a where
a.trx_number like :INVOICE) AND STATUS = 'A'

This problem sql is fixed with Patch 9861798, but after the patch, there is still a performance issue.

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