Oracle R12 – SQL to delete unwanted tax rates and tax statuses

How to delete tax rates and statuses from the back end

Here is the thing, you have just set up a country in eBTax, the full regime to rate then realised you need to change the naming convention but have all these rates and statuses in the system. Rates can be deleted but statuses cannot be so whlst there won’t be any logical issues, having old data int eh system is not great from a cosmetic point of view. So, a simple way of clearing out old statuses and rates is to do the following.

Step 1.

End date all the statuses that you no longer need

Step 2.

run the following scripts, putting in the Tax Regime where you have made the changes. Make sure you run them in this order.

delete from ZX_RATES_TL

where tax_rate_id in (select tax_rate_id from zx_rates_b

where tax_status_code in (select tax_status_code from ZX_STATUS_B

where tax_regime_code = &TAX_REGIME

and effective_to is not null));

 

delete from ZX_ID_TCC_MAPPING_ALL

where tax_rate_code_id in (select tax_rate_id from zx_rates_b

where tax_status_code in (select tax_status_code from ZX_STATUS_B

where tax_regime_code = &TAX_REGIME

and effective_to is not null));

 

delete from zx_rates_b

where tax_status_code in (select tax_status_code from ZX_STATUS_B

where tax_regime_code = &TAX_REGIME

and effective_to is not null);

 

delete from ZX_STATUS_tl

where tax_status_id in (select tax_status_id from ZX_STATUS_B

where tax_regime_code = &TAX_REGIME

and effective_to is not null);

 

delete from ZX_STATUS_B

where tax_regime_code = &TAX_REGIME

and effective_to is not null;

 

commit;

If you had already entered the rates to rules then you can delete from the ZX_PROCESS_RESULTS first but there is another way of updating the rules which means you don’t need to delete from zx_process_results.

OK, so this is not an ‘approved’ set of scripts from Oracle, but when ever do they approve anything unless its to fix bugs in their code. So, use with caution.

Andrew Bohnet
Follow Us

Andrew Bohnet

Managing Director and Oracle Fusion Tax / eBTax expert at eBiz Answers Ltd
Andrew Bohnet is the current chair for the Oracle Tax Management SIG. He founded eBiz Answers to offer clients complete tax solutions making the most of the rich functionality of the R12 and Fusion tax modules. Whether it is analysis, design, configuration or support, eBiz Answers provide an unparalleled service when it comes to a complete tax solution. Having worked on one of the first Oracle R12 implementations in Europe, Andrew was exposed to the tax module early on and worked closely with Oracle to fix bugs, enhance functionality and present on numerous occasions on the subject of tax and Oracle. When it comes to presenting on eBTax, you wont find a more experienced consultant outside of Oracle.
Andrew Bohnet
Follow Us