My Oracle Support Banner

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

Applies to:

Oracle E-Business Tax - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

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.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.