SQL - Change Tax Type

/*Usage:*/
EXEC _as_ChangeTaxType '684540.0001', '2.2', '3.2';


/*Quick exec creation EXAMPLE with a select: */
select concat('EXEC _as_ChangeTaxType ''',s.cAuditNumber,''', ''2.2'', ''3.2'';'),* from PostGL s
	where s.TxDate >= '2025-10-01'
		and s.TaxTypeId = 15
		and s.Id = 'oGrv'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Alexander Toufexis
-- Create date: 27/05/2015
-- Description: This Stored Proc enables the easy changing of a tax type to another tax type. Do note that the tax types should already be the same percentage, otherwise there will be issues.
-- =============================================
CREATE OR ALTER PROCEDURE _as_ChangeTaxType
    -- Add the parameters for the stored procedure here
    @AuditNumber varchar(50),
    @OldTaxType varchar(10),
    @NewTaxType varchar(10)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
 
--USAGE:
--EXEC _as_ChangeTaxType '28929.0020', '6', '7'
 
    --DECLARES:
    DECLARE @OldTaxID as int
    DECLARE @NewTaxID as int
    DECLARE @OldTaxPerc as float
    DECLARE @NewTaxPerc as float
    DECLARE @InvNumID as bigint
 
    --Check if tax types have the same percentage.
    SELECT @OldTaxID = idTaxRate, @OldTaxPerc = TaxRate FROM TaxRate WHERE Code = @OldTaxType
    SELECT @NewTaxID = idTaxRate, @NewTaxPerc = TaxRate FROM TaxRate WHERE Code = @NewTaxType
 
    --CHECK if percentages are the same:
    IF (@OldTaxPerc <> @NewTaxPerc)
        BEGIN
        RaisError('Tax Rates are not the same!',17,1)
        RETURN -1
        END
 
    --Getting InvNumID to update invoice lines:
    SELECT @InvNumID = COALESCE((SELECT InvNumKey FROM PostAP WHERE cAuditNumber = @AuditNumber),(SELECT InvNumKey FROM PostAR WHERE cAuditNumber = @AuditNumber),0)
    --UPDATE:
    UPDATE PostGL SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
    UPDATE PostAR SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
    UPDATE PostAP SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
    UPDATE PostST SET TaxTypeID = @NewTaxID WHERE TaxTypeID = @OldTaxID AND cAuditNumber = @AuditNumber
    UPDATE _btblInvoiceLines SET iTaxTypeID = @NewTaxID WHERE iInvoiceID = @InvNumID AND iTaxTypeID = @OldTaxID
	/*updating GRV's underlying as well for full correctness:*/
	UPDATE il SET iTaxTypeID = @NewTaxID 
		from _btblInvoiceLines il
		WHERE il.iInvoiceID IN (SELECT OrigDocID FROM InvNum I WHERE I.OrigDocID > 0 AND I.AutoIndex = @InvNumID)
			AND iTaxTypeID = @OldTaxID
 
    PRINT 'Updated Tax Types for Audit Number: ' + @AuditNumber
END
GO