Ian - Thanks for being the only one to give it a shot but...
The Currency Id is the ISO code that they are referring to. Basically what I am certain that I need to do is:
1) create new Currency Id's (3 chars)
2) Create the Tax Table data for the new currency ids from the old 2 char
3) Identify every table with Currency Id and create scripts to update them to the new currency id's
4) Additionally for every table that uses the CURRNIDX I'll also have to change it to the new index for the new currency
4) delete the old currency id's in the system so that Web Services is able to be deployed
I have scripts for identifying every table that certain fields are in but still it's a daunting task and I can't miss any table or there will be data integrity errors.
5) Some of the currency fields are PCurrencyID, ISOCURRC, TranslationCurrencyID, STCURRID, Last_Currency_ID, Currency_Note_Index, FUNLCURR, RPTGCURR, DENOMCURR, FROMCURR, ICCURRID, ICCURRIX, USRCURR1, USRCURR2, VALCURR1, VALCURR2 ... so as you may see this is a bit of a daunting task in that I must replace every currency_Id and or currencyidx with the new values and it has to be complete for every table with data
I'll use the following script to identify all tables with column name Currency_Id:
/* Following stmt will check in the tables only (replace xxxx with column name you are looking for */
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name like '%Curr%'
ORDER BY schema_name, table_name;
/* Following stmt will search entire database (including views) */
-- select name,* from sysobjects where id in(select id from syscolumns where name like '%Curr%')
