Oracle R12 US Geographies data cleanse before new load

If you have upgraded from 11i to R12 and have the misfortune to have brought across all the bad geographies for states through to Zip Code then the following SQL will allow you to remove this bad data to clear the way for a 3rd party data file such as one from TTR

 

 

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’;