In Ebusiness Center Form, Navigating to Collections Tab Exhibits Poor Performance (Doc ID 1472030.1)

Last updated on FEBRUARY 23, 2015

Applies to:

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

Symptoms

Navigating through the Collections Tab in the EBusiness Center form exhibits poor performance.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. In the EBusiness Center form, select a Customer of type Organization. All tabs display fine.
2. Click on the Collection tab.
3. It takes a long time to show the details.

The following sql is observed to consume maximum time:

SELECT ROUND(SUM(GL_CURRENCY_API.CONVERT_AMOUNT(C.CURRENCY,A.CURRENCY_CODE,
 SYSDATE, FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'),
 (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
 DECODE(SUM(GL_CURRENCY_API.CONVERT_AMOUNT(C.CURRENCY,A.CURRENCY_CODE,
 SYSDATE,FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),
 0,1, SUM(GL_CURRENCY_API.CONVERT_AMOUNT(C.CURRENCY,A.CURRENCY_CODE, SYSDATE,
 FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
FROM
GL_SETS_OF_BOOKS A, AR_SYSTEM_PARAMETERS B , AR_TRX_SUMMARY C ,
 HZ_CUST_ACCOUNTS D WHERE A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID AND B.ORG_ID
 = C.ORG_ID AND C.CUST_ACCOUNT_ID = :B1 AND AS_OF_DATE BETWEEN
 TRUNC(ADD_MONTHS(SYSDATE, -12)) AND TRUNC(SYSDATE)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch        1   1308.13    1322.62       1514       6140          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   1308.14    1322.64       1514       6140          0           1

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