E-Business Tax Issue: Updating Tax Rate Is Taking More Than 30 Minutes To Complete
(Doc ID 1540024.1)
Last updated on FEBRUARY 21, 2019
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 THEN X_TAX_RATE_CODE ELSE NULL" doesn't make sense logically.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!