Unable to Query Customers in Advanced Collections After RUP5 or February 2016 RPC for IEX
(Doc ID 2127620.1)
Last updated on JULY 24, 2019
Oracle Advanced Collections - Version 12.1.3 and later Information in this document applies to any platform.
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