SQL - Move AR/AP/GL Transaction (by Audit Number)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Asamco BV, Alexander Toufexis and Sam van Gisbergen
-- Create date: 11-11-2019
-- Description: This changes transactions from one AR/AP/GL account to another for a specific transaction
-- NOTE: DOES CATER for master-sub linked accounts!
-- =============================================
ALTER PROCEDURE _as_MoveTransactions
    -- Add the parameters for the stored procedure here
    @ARAPGL as varchar(2),
    @OldAccount as varchar(100),
    @NewAccount as varchar(100)/*,
    @AuditNumber as varchar(100)*/
    ,@RenameOldAccount bit = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
  
    -- Insert statements for procedure here
  
    --DECLARES
    DECLARE @ARAPLinkOld as int
    DECLARE @ARAPLinkNew as int
    DECLARE @GLLinkOld as int
    DECLARE @GLLinkNew as int
    DECLARE @InvNumKey as int
    DECLARE @cAccountName as varchar(300)
    DECLARE @OldLinkAccCode as int
    DECLARE @NewLinkAccCode as int, @TempARAPlinkNew int, @TempARAPlinkOld int
    SELECT @OldLinkAccCode  = 0, @NewLinkAccCode = 0
  
    --GETTING ACCOUNT LINKS:
    IF (@ARAPGL = 'AR')
        BEGIN
            SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Client WHERE Account = @OldAccount),0)
            SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Client WHERE Account = @NewAccount),0)
            SELECT @cAccountName = COALESCE((SELECT C.Name from Client C WHERE DCLink = @ARAPLinkNew),'')
            SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @OldAccount),0)
            SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @NewAccount),0)
            --SELECT @InvNumKey = COALESCE((SELECT TOP 1  InvNumKey FROM PostAR WHERE cAuditNumber = @AuditNumber AND InvNumKey > 0),0)
            SELECT @OldLinkAccCode = COALESCE(((SELECT MainAccLink FROM Client WHERE DCLink = @ARAPLinkOld)),0)
            SELECT @NewLinkAccCode = COALESCE(((SELECT MainAccLink FROM Client WHERE DCLink = @ARAPLinkNew)),0)
 
            IF @OldLinkAccCode > 0 /* switch master account in the client ID field, and sub acc in linkaccCode */
                BEGIN
					SELECT @TempARAPlinkOld = @ARAPLinkOld
                    SELECT @ARAPLinkOld = @OldLinkAccCode, @OldLinkAccCode = @TempARAPlinkOld
                END
            IF @NewLinkAccCode > 0 /* switch master account in the client ID field, and sub acc in linkaccCode */
                BEGIN
					SELECT @TempARAPlinkNew = @ARAPLinkNew
                    SELECT @ARAPLinkNew = @NewLinkAccCode, @NewLinkAccCode = @TempARAPlinkNew
                END
        END
    IF (@ARAPGL = 'AP')
        BEGIN
            SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @OldAccount),0)
            SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @NewAccount),0)
            SELECT @cAccountName = COALESCE((SELECT C.Name from Vendor C WHERE DCLink = @ARAPLinkNew),'')
            SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @OldAccount),0)
            SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @NewAccount),0)
            /*SELECT @InvNumKey = COALESCE((SELECT TOP 1 InvNumKey FROM PostAP WHERE cAuditNumber = @AuditNumber AND InvNumKey > 0),0)*/
        END
    IF (@ARAPGL = 'GL')
        BEGIN
            SELECT @ARAPLinkOld = 0
            SELECT @ARAPLinkNew = 0
            SELECT @GLLinkOld = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @OldAccount),0)
            SELECT @GLLinkNew = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @NewAccount),0)
        END
      
    --CHECKS:
    IF (@GLLinkOld = 0 OR @GLLinkNew = 0) RaisError('One of the GL Accounts not found.',17,1)
    IF (@ARAPGL != 'GL' AND (@GLLinkOld = 0 OR @GLLinkNew = 0)) RaisError('One of the GL Accounts not found.',17,1)
  
    --UPDATING:
    IF (@ARAPGL = 'GL')
        BEGIN
            UPDATE PostGL SET AccountLink = @GLLinkNew WHERE AccountLink = @GLLinkOld
        END
    IF (@ARAPGL != 'GL')
        BEGIN
              
            UPDATE PostGL SET AccountLink = @GLLinkNew, DrCrAccount = @ARAPLinkNew WHERE AccountLink = @GLLinkOld AND DrCrAccount = @ARAPLinkOld --AND cAuditNumber = @AuditNumber
            --update invnum:
            --IF (@InvNumKey > 0)
            --    BEGIN
                    UPDATE InvNum SET AccountID = CASE WHEN @NewLinkAccCode = 0 THEN @ARAPLinkNew ELSE @NewLinkAccCode END
                    , cAccountName = @cAccountName
                        WHERE /*AutoIndex = @InvNumKey */ ((DocType IN (0,1,4,6) AND @ARAPGL = 'AR') OR (DocType IN (2,3,5,7) AND @ARAPGL = 'AP'))
                        AND AccountID = CASE WHEN @OldLinkAccCode = 0 THEN @ARAPLinkOld ELSE @OldLinkAccCode END
                --END
            --update PostAR/PostAP records:
            IF (@ARAPGL = 'AR')
                BEGIN
                    UPDATE _rtblContracts SET iDebtorID = CASE WHEN @NewLinkAccCode = 0 THEN @ARAPLinkNew ELSE @NewLinkAccCode END
                        WHERE iDebtorID = CASE WHEN @OldLinkAccCode = 0 THEN @ARAPLinkOld ELSE @OldLinkAccCode END
                    UPDATE RecurRL SET Account = CASE WHEN @NewLinkAccCode = 0 THEN @ARAPLinkNew ELSE @NewLinkAccCode END
                        WHERE Account = CASE WHEN @OldLinkAccCode = 0 THEN @ARAPLinkOld ELSE @OldLinkAccCode END
 
                    IF (@NewLinkAccCode > 0 AND @OldLinkAccCode > 0)
                    BEGIN
                            --and new link acc code, AND old one... NOTE: won't block on allocations! might cause invalid allocations!
                        UPDATE PostAR SET AccountLink = @ARAPLinkNew, LinkAccCode = @NewLinkAccCode WHERE AccountLink = @ARAPLinkOld AND LinkAccCode = @OldLinkAccCode /* AND cAuditNumber = @AuditNumber*/
                        --moving any old client accounts from the old master account, to the new master account?
                        UPDATE Client SET MainAccLink = @ARAPLinkNew WHERE MainAccLink = @ARAPLinkOld
                    END
                    ELSE IF (@NewLinkAccCode > 0 AND @OldLinkAccCode = 0)
                        BEGIN
                            --New acc has master acc, old one didn't: (note: if old link acc had sub accs, we're switching it over)
                            UPDATE PostAR SET AccountLink = @ARAPLinkNew, LinkAccCode = @NewLinkAccCode WHERE AccountLink = @ARAPLinkOld /*AND cAuditNumber = @AuditNumber */
                            --updating client sub accounts to the new client's master account:
                            UPDATE Client SET MainAccLink = @ARAPLinkNew WHERE MainAccLink = @ARAPLinkOld
                        END
                    ELSE IF (@NewLinkAccCode = 0 AND @OldLinkAccCode = 0)
                        BEGIN
                            --new acc has no master account (might be one though!), and the old account also does not have a master account
                            UPDATE PostAR SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld /*AND cAuditNumber = @AuditNumber */
                            --updating client sub accounts to the new client's master account:
                            UPDATE Client SET MainAccLink = @ARAPLinkNew WHERE MainAccLink = @ARAPLinkOld
                        END
                    ELSE IF (@NewLinkAccCode = 0 AND @OldLinkAccCode > 0)
                        BEGIN
                            --new account has no master account (might be one htough), and the old account does have a master account
                            UPDATE PostAR SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld AND LinkAccCode = @OldLinkAccCode
                        END
 
                END
            ELSE -- (@ARAPGL = 'AR')
                BEGIN
                    UPDATE PostAP SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld /*AND cAuditNumber = @AuditNumber*/
                END
        --delete old account (only ARAP):
        IF (@RenameOldAccount = 1 AND @ARAPGL = 'AR')
            UPDATE Client SET Account = Account + '_DELETE' WHERE DCLink = @ARAPLinkOld
        ELSE IF (@RenameOldAccount = 1 AND @ARAPGL = 'AP')
            UPDATE Vendor SET Account = Account + '_DELETE' WHERE DCLink = @ARAPLinkOld
         
        END --IF (@ARAPGL != 'GL')
 
 
          
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV, Alexander Toufexis and Sam van Gisbergen
-- Create date: 11-11-2019
-- Description:	This changes transactions from one AR/AP/GL account to another for a specific transaction
-- NOTE: does not cater for master-sub linked accounts!
-- =============================================
CREATE PROCEDURE _as_MoveTransactionsByAuditTrail
	-- Add the parameters for the stored procedure here
	@ARAPGL as varchar(2),
	@OldAccount as varchar(100),
	@NewAccount as varchar(100),
	@AuditNumber as varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	--DECLARES
	DECLARE @ARAPLinkOld as int
	DECLARE @ARAPLinkNew as int
	DECLARE @GLLinkOld as int
	DECLARE @GLLinkNew as int
	DECLARE @InvNumKey as int
	DECLARE @cAccountName as varchar(300)

	--GETTING ACCOUNT LINKS:
	IF (@ARAPGL = 'AR')
		BEGIN
			SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Client WHERE Account = @OldAccount),0)
			SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Client WHERE Account = @NewAccount),0)
			SELECT @cAccountName = COALESCE((SELECT C.Name from Client C WHERE DCLink = @ARAPLinkNew),'')
			SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @NewAccount),0)
			SELECT @InvNumKey = COALESCE((SELECT TOP 1  InvNumKey FROM PostAR WHERE cAuditNumber = @AuditNumber AND InvNumKey > 0),0)
		END
	IF (@ARAPGL = 'AP')
		BEGIN
			SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @OldAccount),0)
			SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @NewAccount),0)
			SELECT @cAccountName = COALESCE((SELECT C.Name from Vendor C WHERE DCLink = @ARAPLinkNew),'')
			SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @NewAccount),0)
			SELECT @InvNumKey = COALESCE((SELECT TOP 1 InvNumKey FROM PostAP WHERE cAuditNumber = @AuditNumber AND InvNumKey > 0),0)
		END
	IF (@ARAPGL = 'GL')
		BEGIN
			SELECT @ARAPLinkOld = 0
			SELECT @ARAPLinkNew = 0
			SELECT @GLLinkOld = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @NewAccount),0)
		END
	
	--CHECKS:
	IF (@GLLinkOld = 0 OR @GLLinkNew = 0) RaisError('One of the GL Accounts not found.',17,1)
	IF (@ARAPGL != 'GL' AND (@GLLinkOld = 0 OR @GLLinkNew = 0)) RaisError('One of the GL Accounts not found.',17,1)

	--UPDATING:
	IF (@ARAPGL = 'GL')
		BEGIN
			UPDATE PostGL SET AccountLink = @GLLinkNew WHERE AccountLink = @GLLinkOld AND cAuditNumber = @AuditNumber
		END
	IF (@ARAPGL != 'GL')
		BEGIN
			
			UPDATE PostGL SET AccountLink = @GLLinkNew, DrCrAccount = @ARAPLinkNew WHERE AccountLink = @GLLinkOld AND DrCrAccount = @ARAPLinkOld AND cAuditNumber = @AuditNumber
			--update invnum:
			IF (@InvNumKey > 0)
				BEGIN
					UPDATE InvNum SET AccountID = @ARAPLinkNew, cAccountName = @cAccountName WHERE AutoIndex = @InvNumKey AND AccountID = @ARAPLinkOld
				END
			--update PostAR/PostAP records:
			IF (@ARAPGL = 'AR')
				BEGIN
					UPDATE PostAR SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld AND cAuditNumber = @AuditNumber
				END
			ELSE
				BEGIN
					UPDATE PostAP SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld AND cAuditNumber = @AuditNumber
				END
		END
	
		
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alexander Toufexis
-- Create date: 16-09-2015
-- Description:	This changes transactions from one AR/AP/GL account to another
-- =============================================
CREATE PROCEDURE _as_MoveTransactions
	-- Add the parameters for the stored procedure here
	@ARAPGL as varchar(2),
	@OldAccount as varchar(100),
	@NewAccount as varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	--DECLARES
	DECLARE @ARAPLinkOld as int
	DECLARE @ARAPLinkNew as int
	DECLARE @GLLinkOld as int
	DECLARE @GLLinkNew as int

	--GETTING ACCOUNT LINKS:
	IF (@ARAPGL = 'AR')
		BEGIN
			SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Client WHERE Account = @OldAccount),0)
			SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Client WHERE Account = @NewAccount),0)
			SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Client LEFT JOIN CliClass ON Client.iClassID = CliClass.idCliClass WHERE Account = @NewAccount),0)
		END
	IF (@ARAPGL = 'AP')
		BEGIN
			SELECT @ARAPLinkOld = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @OldAccount),0)
			SELECT @ARAPLinkNew = COALESCE((SELECT DCLink FROM Vendor WHERE Account = @NewAccount),0)
			SELECT @GLLinkOld = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT iAccountsIDControlAcc FROM Vendor LEFT JOIN VenClass ON Vendor.iClassID = VenClass.idVenClass WHERE Account = @NewAccount),0)
		END
	IF (@ARAPGL = 'GL')
		BEGIN
			SELECT @ARAPLinkOld = 0
			SELECT @ARAPLinkNew = 0
			SELECT @GLLinkOld = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @OldAccount),0)
			SELECT @GLLinkNew = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @NewAccount),0)
		END
	
	--CHECKS:
	IF (@GLLinkOld = 0 OR @GLLinkNew = 0) RaisError('One of the GL Accounts not found.',17,1)
	IF (@ARAPGL != 'GL' AND (@GLLinkOld = 0 OR @GLLinkNew = 0)) RaisError('One of the GL Accounts not found.',17,1)

	--UPDATING:
	IF (@ARAPGL = 'GL')
		BEGIN
			UPDATE PostGL SET AccountLink = @GLLinkNew WHERE AccountLink = @GLLinkOld
		END
	IF (@ARAPGL != 'GL')
		BEGIN
			
			UPDATE PostGL SET AccountLink = @GLLinkNew, DrCrAccount = @ARAPLinkNew WHERE AccountLink = @GLLinkOld AND DrCrAccount = @ARAPLinkOld
			IF (@ARAPGL = 'AR')
				BEGIN
					UPDATE PostAR SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld
				END
			ELSE
				BEGIN
					UPDATE PostAP SET AccountLink = @ARAPLinkNew WHERE AccountLink = @ARAPLinkOld
				END
		END
	
		
END