Share
ReportWould it be OK to change the Currency ID's in GP after I remove Fabrikam. Below is script I wrote that would accomplish this task. It seems to work quite well. Basically it iterates the system database and each company database searching for Currency ID fields and swapping the value. After running this script the Management Reporter datamart needs to be rebuilt. It can only change an ID, it can't combine Currency ID's. So far, all my testing shows it works fine.
/*
SELECT * FROM MC40200
*/
CREATE PROC CU_Rename_Currency_ID (@FromCurrencyID AS VARCHAR(15), @ToCurrencyID AS VARCHAR(15))
/*
This proc was create to change currency ID's. After it is installed in the GP System Database (ie..DYNAMICS),
then use can use this procedure to change a currency id. The @ToCurrencyID must not exist! MC40200 holds
the master list of Currency ID's and this table is displayed at the end of execution of this script. You should
rebuilt the multicurrency datamart after running this script.
Here is an example of how to run this script, this could be done after the Fabrickam company is installed.
This would get rid of all the "z codes".
EXECUTE CU_Rename_Currency_ID 'Z-AUD', 'AUD';
EXECUTE CU_Rename_Currency_ID 'Z-C$', 'CAD';
EXECUTE CU_Rename_Currency_ID 'Z-EURO', 'EUR';
EXECUTE CU_Rename_Currency_ID 'Z-NZD', 'NZD';
EXECUTE CU_Rename_Currency_ID 'Z-UK', 'GBP';
EXECUTE CU_Rename_Currency_ID 'Z-SGD', 'SGD';
EXECUTE CU_Rename_Currency_ID 'Z-SA', 'ZAR';
EXECUTE CU_Rename_Currency_ID 'Z-US$', 'USD';
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @UToCurrencyID AS VARCHAR(15)
DECLARE @NewIndex AS INT
DECLARE @INTERID AS VARCHAR(128)
DECLARE @TableName AS VARCHAR(128)
DECLARE @Fields AS VARCHAR(128)
DECLARE @CMD AS VARCHAR(5000)
DECLARE @FieldList AS VARCHAR(5000)
--Get Currency Index ------------------------------------------------------------------------------------------------------
SET @UToCurrencyID = UPPER(@ToCurrencyID);
SET @NewIndex = (SELECT @NewIndex FROM MC40200 WHERE CURNCYID=@FromCurrencyID)
--Build a Field List Array -------------------------------------------------------------------------------------------------
SET @FieldList = ' ''CURNCYID'', ''CURIDKEY'', ''OrgCurID'', ''FUNLCURR'', ''RPTGCURR'' '
--Build a Table to Store Changes -------------------------------------------------------------------------------------------
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CU_CurrencyIDChanges') DROP TABLE CU_CurrencyIDChanges;
SELECT TableName=@TableName,
Fields=@Fields
INTO CU_CurrencyIDChanges
DELETE CU_CurrencyIDChanges
--Loop Company List ---------------------------------------------------------------------------------------------------------
DECLARE CompanyCursor CURSOR FOR SELECT RTRIM(INTERID) FROM SY01500 WHERE INTERID IN (SELECT name FROM sys.databases) UNION ALL SELECT DB_NAME();
OPEN CompanyCursor;
FETCH NEXT FROM CompanyCursor INTO @INTERID;
WHILE @@FETCH_STATUS=0
BEGIN
--Notification of Database Start ----------------------------------------------------------------------------------------
Print 'Starting Database: ' + @INTERID
--Build Table List ------------------------------------------------------------------------------------------------------
SET @CMD = 'INSERT INTO CU_CurrencyIDChanges (TableName, Fields)
SELECT A.NAME, B.NAME
FROM '+RTRIM(@INTERID)+'..SYSOBJECTS A
JOIN '+RTRIM(@INTERID)+'..SYSCOLUMNS B
ON A.ID=B.ID
WHERE A.XTYPE=''U''
AND B.NAME IN ( '+@FieldList+' )
';
EXECUTE(@CMD);
--Loop the Table List ---------------------------------------------------------------------------------------------------
DECLARE TableListCursor CURSOR FOR SELECT TableName, Fields FROM CU_CurrencyIDChanges
OPEN TableListCursor;
FETCH NEXT FROM TableListCursor INTO @TableName, @Fields;
WHILE @@FETCH_STATUS=0
BEGIN
SET @CMD='UPDATE '+RTRIM(@INTERID)+'.dbo.'+@TableName+' SET '+@Fields+'='''+@UToCurrencyID+''' WHERE '+@Fields+'='''+@FromCurrencyID+''';';
PRINT @CMD
EXECUTE(@CMD);
FETCH NEXT FROM TableListCursor INTO @TableName, @Fields;
END
CLOSE TableListCursor;
DEALLOCATE TableListCursor;
--Continue Company Loop -------------------------------------------------------------------------------------------------
DELETE CU_CurrencyIDChanges;
Print 'Finished Database: ' + @INTERID
FETCH NEXT FROM CompanyCursor INTO @INTERID;
END
CLOSE CompanyCursor;
DEALLOCATE CompanyCursor;
SET NOCOUNT OFF
SELECT CURNCYID, CRNCYDSC FROM MC40200
END
*This post is locked for comments
All responses (
Answers (
291,280
