Last updated on AUGUST 24, 2016
Applies to:Oracle E-Business Tax - Version 12.1.3 and later
Information in this document applies to any platform.
Attempting to update Tax Rate and it is taking more than 30 minutes to complete.
The PL/SQL package ZX_RATES_PKG as zxcratesb.pls 120.20.12010000.9 has the the following SQL statements with performance problem as well as logic problem:
1) UPDATE fnd_lookup_values
SET description = P_TAX_RATE_CODE,....
the condition "AND lookup_code = NVL(TAG,P_TAX_RATE_CODE)" makes the SQL take long time to complete. You can see the buffer_gets and CPU time in the attached top10_sql.xls. Also, lookup_code=NVL(TAG,...) doesn't make sense, it means if TAG is not null, the sql update would update all records that having lookup_type=P_LOOKUP_TYPE.
2) MERGE INTO FND_LOOKUP_VALUES
USING (SELECT INSTALLED_FLAG,LANGUAGE_CODE FROM FND_LANGUAGES where INSTALLED_FLAG in ('I', 'B')) L ....
again, above condition " LOOKUP_CODE = NVL(TAG,X_TAX_RATE_CODE)" makes the sql takes long time to complete, the CPU time and buffer_gets of the SQL is in the top10_sql.xls. "lookup_code=nvl(tag,...)" doesn't make sense logically.
3) WHEN NOT MATCHED THEN INSERT
( LOOKUP_TYPE, ....
... (SELECT CASE WHEN LENGTHB(X_TAX_RATE_CODE) > 30 ...
the select "CASE WHEN LENGTHB(X_TAX_RATE_CODE) > 30
ELSE NULL" doesn't make sense logically.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms