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’

;