E-Business Tax Issue: Updating Tax Rate Is Taking More Than 30 Minutes To Complete (Doc ID 1540024.1)

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.

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

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