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





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

Oracle 11i to R12 – Upgrade or Re-implement?

It will be sold as the easy, fast and cheapest way to go from Oracle 11i to R12 but is upgrading the right choice?

Unless my client has an extremely well refined 11i ERP solution working without fault with almost no customisation nor any country localisations, I would always say that a re-implementation is the right way forward. Time and time again, when working on upgrade projects, things are always a lot harder than if the project was being implementeded. I liken an 11i to R12 project to a house renovation. Consider 11i to be your house now and R12 to be the super environmentally friendly completely modern house of the future. If you have a very basic house that has been well constructed and has a great layout then making the renovation is straight forward, however, if that house is old, of a poor design and construction that needs all the pipe work and wiring replaced, making it extremely hard to get the underfloor heating in that you wanted, surely it would be better to knock it down and start again? Of course this is where my comparisons end because the costs of knocking down a house are usually the biggest factor as are the costs of any IT project, the 11i to R12 upgrade being one of them. But will the cost be any greater by doing a reimplementation instead of an upgrade? In the long run, with the delays to the project or the late surge in additional resources to fix the issues, update the customisations and ensure that the localisations are working to meet local fiscal policy, the upgrade becomes as costly, if not a higher cost than a clean re-implementation!

I will be putting together a document with all the pitfalls of doing an upgrade over an implementation based on mine and other colleagues experiences. But pleae feel free to comment with issues that you may have faced that support either option.