Errors ORA-20999 ORA-01403 In The Argentine Turnover Tax Data Upload Verification Report

(Doc ID 1998715.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Financials for the Americas - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Argentina Payables

When attempting to run the Argentine Turnover Tax Data Upload Verification Report,
the following error occurs.


Insert in JL_ZZ_AP_SUPP_AWT_TYPES not done
ORA-00001: unique constraint (JL.JL_ZZ_AP_SUPP_AWT_TYPES_U2) violated

Failed to INSERT into JL_ZZ_AP_SUP_AWT_CD Insert not done
ORA-00001: unique constraint (JL.JL_ZZ_AP_SUP_AWT_CD_U2) violated

Wht Rate and Wht group for this record from Government File doesnt match with AP Wht Tax Setup.
49 .TAXPAYER ID FAILED IN FINAL VALIDATION, FETCHING NEXT RECORD - -20999 -ERROR-
ORA-20999: AN ERROR WAS ENCOUNTERED IN WHT_GROUP_NUM AND WHT_RATE VALIDATION 100 -ERROR-
ORA-01403: no data found


The issue can be reproduced at will with the following steps:

1. Responsibility: Oracle Payables - Argentine localization
2. Path: Other -> Requests -> Run
3. Run the Argentine Turnover Tax Data Upload Verification Report

a) Output file

Error "Record failed during Final Check Validation" for many many taxpayers.
This error is misleading to the users who will be using this report to check that the process is working.

b) Log file (Argentine_Turnover_Tax_Data_Up_010415.txt)

Insert in JL_ZZ_AP_SUPP_AWT_TYPES not done
ORA-00001: unique constraint (JL.JL_ZZ_AP_SUPP_AWT_TYPES_U2) violated

Failed to INSERT into JL_ZZ_AP_SUP_AWT_CD Insert not done
ORA-00001: unique constraint (JL.JL_ZZ_AP_SUP_AWT_CD_U2) violated

Wht Rate and Wht group for this record from Government File doesnt match with AP Wht Tax Setup.
49 .TAXPAYER ID FAILED IN FINAL VALIDATION, FETCHING NEXT RECORD - -20999 -ERROR-
ORA-20999: AN ERROR WAS ENCOUNTERED IN WHT_GROUP_NUM AND WHT_RATE VALIDATION 100 -ERROR-
ORA-01403: no data found

NOTE:
The error being displayed is INVALID because the taxpayer does NOT have a retention rate.
The program is trying to process the retention when it should not.
Other than that AP seems to be working.

NOTE:
Wht Rate and Wht group for this record from Government File does not match with AP Wht Tax Setup.

The cause of this is that the program does not detect that the retention rate is NULL and still tries to do the check to validate whether the tax code exists:

  SELECT 'Y'
INTO WHT_GROUP_NUM_rate_flag
FROM AP_AWT_TAX_RATES
  WHERE tax_name = l_WHT_GROUP_NUM_ALL
AND tax_rate = l_WHT_RATE_ALL;

  SELECT tax_id
INTO l_tax_id
FROM AP_TAX_CODES
WHERE name = l_WHT_GROUP_NUM_ALL
AND tax_type = 'AWT';

l_WHT_GROUP_NUM_ALL and l_WHT_RATE_ALL are both NULL and so these selects will retunr no rows which will throw an exception.
The program needs to detect that the values are NULL (both ar NULL) and then basically not try to do the check for the tax data (ie skip processing any further for retention tax).


The issue has the following business impact:
Due to this issue, users cannot run the report without errors.



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