New VAT (tax) Percentage

--Making a automated update for the VAT codes.

DECLARE @NewInputCode varchar(4)
DECLARE @NewOutputCode varchar(4)
DECLARE @NewInputDesc varchar(30)
DECLARE @NewOutputDesc varchar(30)
DECLARE @NewPerc float

DECLARE @OldInputCode varchar(4)
DECLARE @OldOutputCode varchar(4)
DECLARE @MakeTaxCodes bit
DECLARE @UpdateFields bit

--SETTING VARIABLES:
SET @NewInputCode = '31'
SET @NewOutputCode  = '32'
SET @NewInputDesc = 'BTW Inkoop Laag 9%'
SET @NewOutputDesc = 'BTW Verkoop Laag 9%'
SET @NewPerc = 9

--SET @OldInputCode = '12' -- ook nog 06!!!
SET @OldInputCode = (SELECT Code FROM TaxRate WHERE TaxRate = 6 AND Description LIKE '%Inkoop%')
--SET @OldOutputCode = '2'
SET @OldOutputCode = (SELECT Code FROM TaxRate WHERE TaxRate = 6 AND Description LIKE '%Verkoop%')
SET @MakeTaxCodes = 1
SET @UpdateFields = 1

/*Validate if input and output codes were found, otherwise early out*/
IF (@OldInputCode IS NULL OR @OldOutputCode IS NULL)
	BEGIN
	SELECT @MakeTaxCodes = 0, @UpdateFields = 0
	Raiserror('Old codes not found!',16,1)
	END


IF (@MakeTaxCodes = 1)
BEGIN
	--INPUT:
	IF NOT EXISTS(SELECT * FROM TaxRate WHERE Code = @NewInputCode)
	BEGIN
	INSERT INTO [TaxRate] ([Code] ,[Description] ,[TaxRate])
		 VALUES (@NewInputCode ,@NewInputDesc ,@NewPerc)
	END ELSE Raiserror('Input already existed',16,1)

	--OUTPUT:
	IF NOT EXISTS(SELECT * FROM TaxRate WHERE Code = @NewOutputCode)
	BEGIN
	INSERT INTO [TaxRate] ([Code] ,[Description] ,[TaxRate])
		 VALUES (@NewOutputCode ,@NewOutputDesc ,@NewPerc)
	END ELSE Raiserror('Output already existed',16,1)
END --@MakeTaxCodes = 1


IF (@UpdateFields = 1)
BEGIN
--GET Links of Tax Type
DECLARE @InputLinkNew int
DECLARE @OutputLinkNew int
SET @InputLinkNew = (SELECT idTaxRate FROM TaxRate WHERE Code = @NewInputCode)
SET @OutputLinkNew = (SELECT idTaxRate FROM TaxRate WHERE Code = @NewOutputCode)
DECLARE @InputLinkOud int
DECLARE @OutputLinkOud int
SET @InputLinkOud = (SELECT idTaxRate FROM TaxRate WHERE Code = @OldInputCode)
SET @OutputLinkOud = (SELECT idTaxRate FROM TaxRate WHERE Code = @OldOutputCode)

--Now update SQLs!
 --Accounts:
UPDATE Accounts SET 
	iTaxTypeINVID = CASE 
		WHEN iTaxTypeINVID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxTypeINVID = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxTypeINVID END
	,iTaxTypeCRNID = CASE 
		WHEN iTaxTypeCRNID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxTypeCRNID = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxTypeCRNID END
	,iTaxTypeGRVID = CASE 
		WHEN iTaxTypeGRVID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxTypeGRVID = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxTypeGRVID END
	,iTaxTypeRTSID = CASE 
		WHEN iTaxTypeRTSID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxTypeRTSID = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxTypeRTSID END
 --Inventory Items
UPDATE StkItem SET 
	TTI = CASE 
		WHEN TTI = @OldOutputCode THEN @NewOutputCode
		WHEN TTI = @OldInputCode THEN @NewInputCode
		ELSE TTI END
	,TTC = CASE 
		WHEN TTC = @OldOutputCode THEN @NewOutputCode
		WHEN TTC = @OldInputCode THEN @NewInputCode
		ELSE TTC END
	,TTG = CASE 
		WHEN TTG = @OldOutputCode THEN @NewOutputCode
		WHEN TTG = @OldInputCode THEN @NewInputCode
		ELSE TTG END
	,TTR = CASE 
		WHEN TTR = @OldOutputCode THEN @NewOutputCode
		WHEN TTR = @OldInputCode THEN @NewInputCode
		ELSE TTR END

 --Vendor
UPDATE Vendor SET 
	iDefTaxTypeID = CASE 
		WHEN iDefTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iDefTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iDefTaxTypeID END
 --Client
UPDATE Client SET 
	iDefTaxTypeID = CASE 
		WHEN iDefTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iDefTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iDefTaxTypeID END
 --TrCodes
UPDATE TrCodes SET 
	TaxTypeID = CASE 
		WHEN TaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN TaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE TaxTypeID END
 --Cash Book Batches
UPDATE _btblCbBatches SET 
	iInputTaxID = CASE 
		WHEN iInputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iInputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iInputTaxID END
	,iOutputTaxID = CASE 
		WHEN iOutputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iOutputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iOutputTaxID END
 --Journal Batches
UPDATE _btblJrBatches SET 
	iInputTaxID = CASE 
		WHEN iInputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iInputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iInputTaxID END
	,iOutputTaxID = CASE 
		WHEN iOutputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iOutputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iOutputTaxID END
 --GL Defaults (Journal en CB!)
	--CB Defs:
UPDATE _btblCbBatchDefs SET 
	iInputTaxID = CASE 
		WHEN iInputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iInputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iInputTaxID END
	,iOutputTaxID = CASE 
		WHEN iOutputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iOutputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iOutputTaxID END	
	--Journal Defs:
UPDATE _btblJrBatchDefs SET 
	iInputTaxID = CASE 
		WHEN iInputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iInputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iInputTaxID END
	,iOutputTaxID = CASE 
		WHEN iOutputTaxID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iOutputTaxID = @InputLinkOud THEN @InputLinkNew
		ELSE iOutputTaxID END
	
 --Inventory Defaults
UPDATE StDfTbl SET 
	GrvSplitTaxTypeID = CASE 
		WHEN GrvSplitTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN GrvSplitTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE GrvSplitTaxTypeID END
	,iINVTaxTypeID = CASE 
		WHEN iINVTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iINVTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iINVTaxTypeID END
	,iCRNTaxTypeID = CASE 
		WHEN iCRNTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iCRNTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iCRNTaxTypeID END
	,iGRVTaxTypeID = CASE 
		WHEN iGRVTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iGRVTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iGRVTaxTypeID END
	,iRTSTaxTypeID = CASE 
		WHEN iRTSTaxTypeID = @OutputLinkOud THEN @OutputLinkNew
		WHEN iRTSTaxTypeID = @InputLinkOud THEN @InputLinkNew
		ELSE iRTSTaxTypeID END
 --AR Defaults
UPDATE CliDef SET 
	iTaxRateIDNoCharge = CASE 
		WHEN iTaxRateIDNoCharge = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxRateIDNoCharge = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxRateIDNoCharge END
 --AP Defaults
UPDATE VenDef SET 
	iTaxRateIDNoCharge = CASE 
		WHEN iTaxRateIDNoCharge = @OutputLinkOud THEN @OutputLinkNew
		WHEN iTaxRateIDNoCharge = @InputLinkOud THEN @InputLinkNew
		ELSE iTaxRateIDNoCharge END
 --Andere Defaults??
END --IF (@UpdateFields = 1)