Oracle R12 – US Geography data clean up

This is not an approved Oracle approach but if you need to clean up bad data from an 11i upgrade where you have all sorts of States, Counties or Cities then the flowing SQL could help you.

 

DELETE

hz_relationships

where subject_id IN (

select geography_id

from hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

)

and subject_table_name = ‘HZ_GEOGRAPHIES’

;

 

DELETE

hz_relationships

where object_id IN (

select geography_id

from hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

)

and object_table_name = ‘HZ_GEOGRAPHIES’

;

 

The last step must be the delete of the HZ_GEOGRAPHIES as it is used in the queries above

 

DELETE hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

;

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