Unable to Query Customers in Advanced Collections After RUP5 or February 2016 RPC for IEX (Doc ID 2127620.1)

Last updated on APRIL 21, 2017

Applies to:

Oracle Advanced Collections - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

After applying the February 2016 RPC patch (22899060) or RUP5 (which includes 2016 IEX RPC), searching on an Organization in the Advanced Collections search window takes 30 seconds for every customer.  When the customer is returned it shows millions of records returned for 'Delinquencies in Past Year' in the header, for every customer.  When the customer only has a few invoices.

It some cases the form will just spin and not return a customer.

The trace file shows the following sql is causing a performance problem:

SELECT COUNT(DECODE(PS.CLASS, 'INV', PS.PAYMENT_SCHEDULE_ID, 'DM',
PS.PAYMENT_SCHEDULE_ID, 'CB', PS.PAYMENT_SCHEDULE_ID, NULL)) CNT_INV,
COUNT(DECODE(PS.CLASS, 'INV', DECODE(DEL.STATUS, 'DELINQUENT',
DEL.DELINQUENCY_ID, 'PREDELINQUENT', DEL.DELINQUENCY_ID, NULL), NULL))
CNT_DEL
FROM
AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS CA, IEX_DELINQUENCIES DEL WHERE
CA.PARTY_ID = :B1

Steps to Reproduce:

Responsibility: Collections Agent
Navigation
: Collections

  1. Click on the flashlight to open the Search
  2. Change Customer By = Organization
  3. Enter actual customer name in the first field (Organization) and click Search
  4. Customer is correctly returned in the results.
  5. Click Ok. It takes 30+ seconds to return the record in IEXRCALL.
  6. Most of the data is correct, except the counts on the header.

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