Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
if exists(select * from sys.objects where object_id = object_id('dbo.pp_ChangeBaseCury') and objectproperty(object_id,'IsProcedure')=1)
drop proc dbo.pp_ChangeBaseCury
go
create procedure dbo.pp_ChangeBaseCury @companyID int, @oldCury NVARCHAR(10), @newCury NVARCHAR(10), @description NVARCHAR(255), @symbol NVARCHAR(10) AS
BEGIN
DECLARE @stmt NVARCHAR(MAX)
DECLARE @table NVARCHAR(255)
DECLARE @column NVARCHAR(255)
DECLARE @anyfailed bit
--Temp Columns
IF OBJECT_ID('tempdb..#tempcolumns') IS NOT NULL DROP TABLE #tempcolumns
CREATE TABLE #tempcolumns (column_name sysname COLLATE DATABASE_DEFAULT)
INSERT INTO #tempcolumns Values('Cury')
INSERT INTO #tempcolumns Values('CuryID')
INSERT INTO #tempcolumns Values('Currency')
INSERT INTO #tempcolumns Values('CurrencyID')
INSERT INTO #tempcolumns Values('BaseCuryID')
INSERT INTO #tempcolumns Values('FromCuryID')
INSERT INTO #tempcolumns Values('ToCuryID')
INSERT INTO #tempcolumns Values('DefaultCuryID')
INSERT INTO #tempcolumns Values('DestCuryID')
INSERT INTO #tempcolumns Values('InCuryID')
INSERT INTO #tempcolumns Values('OrigCuryID')
INSERT INTO #tempcolumns Values('OutCuryID')
INSERT INTO #tempcolumns Values('PriceListCuryID')
INSERT INTO #tempcolumns Values('ReportCuryID')
INSERT INTO #tempcolumns Values('RUTROTCuryID')
INSERT INTO #tempcolumns Values('PriceListCuryID')
INSERT INTO #tempcolumns Values('PriceListCuryID')
--Temp Tables
IF OBJECT_ID('tempdb..#temptables') IS NOT NULL DROP TABLE #temptables
CREATE TABLE #temptables (table_name sysname COLLATE DATABASE_DEFAULT, column_name sysname COLLATE DATABASE_DEFAULT)
INSERT INTO #temptables SELECT col.TABLE_NAME, col.COLUMN_NAME FROM INFORMATION_SCHEMA.Columns col
JOIN INFORMATION_SCHEMA.Tables t on t.TABLE_NAME = col.TABLE_NAME
WHERE col.DATA_TYPE = 'nvarchar' and t.TABLE_TYPE='BASE TABLE'
and col.COLUMN_NAME in (SELECT column_name FROM #tempcolumns)
--Validation first
Set @anyfailed = 0
DECLARE tables CURSOR FOR SELECT table_name, column_name FROM #temptables
OPEN tables
FETCH NEXT FROM tables INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
declare @cnt int
set @stmt = N'select @counter=count(*) from dbo.[' + @table + '] WHERE CompanyID = ' + convert(varchar(10), @companyID) + ' AND ' + @column + ' = ''' + @newCury + ''''
exec sp_executesql @stmt, N'@counter int output', @counter=@cnt output
if @cnt <> 0 begin
set @stmt = 'Table [' + @table + '] already contains CuryID [' + @newCury + '] '
SET @anyfailed = 1
raiserror(@stmt, 16, 1)
end
FETCH NEXT FROM tables INTO @table, @column
END
CLOSE tables
DEALLOCATE tables
if(@anyfailed = 1) return
--Updating currency
DECLARE tables CURSOR FOR SELECT table_name, column_name FROM #temptables
OPEN tables
FETCH NEXT FROM tables INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = ''
SET @stmt = @stmt + 'UPDATE dbo.[' + @table + '] SET ' + @column + ' = ''' + @newCury + ''' '
SET @stmt = @stmt + 'WHERE CompanyID = ' + convert(varchar(10), @companyID) + ' AND ' + @column + ' = ''' + @oldCury + ''' '
--print @stmt
EXEC sp_executesql @stmt
--Informing Acumatica About the changes
SET @stmt = 'INSERT INTO WatchDog (CompanyID, TableName) values(' + convert(varchar(10), @companyID) + ',''' + @table + ''')'
exec sp_executesql @stmt
FETCH NEXT FROM tables INTO @table, @column
END
CLOSE tables
DEALLOCATE tables
Update CurrencyList set Description = @description, CurySymbol = @symbol where CuryID = @newCury
END
GO
begin tran
exec pp_ChangeBaseCury 1, 'USD' , 'TZS', 'Tanzanian shilling', 'T$'
--exec pp_ChangeBaseCury 1, 'TZS' , 'USD'
rollback tran
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.