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.