Last updated on AUGUST 28, 2017
Applies to:
Oracle Receivables - Version 12.1.1 and laterInformation 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 )
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 )
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