Aging Reports Performance Issue: Poor Performance Running Aging - 4 Buckets Report (ARXAGE) (Doc ID 1307559.1)

Last updated on FEBRUARY 12, 2018

Applies to:

Oracle Receivables - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms

Poor performance of ARXAGE module: Aging - 4 Buckets Report. In this particular instance it has been running for over 24+ hours.

The following costly SQL is causing the issue.

SELECT SITE_USES.SITE_USE_ID , RTRIM(RPAD(LOC.STATE , 2 ) ) ,
RTRIM(RPAD(LOC.CITY , 25 ) ) , RTRIM(RPAD(LOC.CITY , 25 ) ) ||
DECODE(LOC.CITY , NULL , NULL , DECODE(LOC.STATE , NULL , NULL , ',
' ) ) || RTRIM(RPAD(LOC.STATE , 2 ) )
FROM
HZ_CUST_ACCT_SITES_ALL ACCT_SITE , HZ_PARTY_SITES PARTY_SITE , HZ_LOCATIONS
LOC , HZ_CUST_SITE_USES_ALL SITE_USES , HZ_CUST_ACCOUNTS CUST_ACCT WHERE
SITE_USES.SITE_USE_CODE = 'BILL_TO' AND ACCT_SITE.CUST_ACCOUNT_ID = :b1
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND
LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND SITE_USES.CUST_ACCT_SITE_ID =
ACCT_SITE.CUST_ACCT_SITE_ID AND NVL(SITE_USES.STATUS , 'A' ) = 'A'
AND CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID AND
NVL(ACCT_SITE.ORG_ID , -1 ) = DECODE(:b2 , '1000' , NVL(ACCT_SITE.ORG_ID ,
-1 ) , :b3 )