This stored proc merges the records from one supplier to another one.

Note that this also can be used to "delete" suppliers into one "deleted" supplier. <p>Note 2: Don't forget a "recalculate supplier balances" afterwards.

note; this does not handle all Vendor type records depending on usage and implementation, check before usage!

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_spMergeSuppliers') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _as_spMergeSuppliers

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV, Alexander Toufexis
-- Create date: 21/02/2017
-- Description:	Merge supplier into other supplier account. (no job costing supported currently!), Note; no balances are moved on GL accounts.
-- =============================================
CREATE PROCEDURE _as_spMergeSuppliers
	-- Add the parameters for the stored procedure here
	@FromSupplier varchar(100), 
	@ToSupplier varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-----DECLARES------
	DECLARE @FromSupplierID int
	DECLARE @ToSupplierID int
	DECLARE @FromSupplierCurrencyID int
	DECLARE @ToSupplierCurrencyID int

	-----FETCH BASIC DATA-----
	SELECT @FromSupplierID = DCLink, @FromSupplierCurrencyID = iCurrencyID FROM Vendor V WHERE V.Account = @FromSupplier
	SELECT @ToSupplierID = DCLink, @ToSupplierCurrencyID = iCurrencyID FROM Vendor V WHERE V.Account = @ToSupplier

	-----CHECKS & VALIDATIONS-----
		--Existing accounts
		IF COALESCE(@FromSupplierID,0) = 0 GOTO EarlyOut
		IF COALESCE(@ToSupplierID,0) = 0 GOTO EarlyOut
		--Matching currency
		IF COALESCE(@FromSupplierCurrencyID,0) != COALESCE(@ToSupplierCurrencyID,0) 
		BEGIN
			PRINT 'Currencies do not match between supplier ' + @FromSupplier + '(Currency ID: ' + CAST(COALESCE(@FromSupplierCurrencyID,0) as varchar(200)) + ') into supplier '  + @ToSupplier + '(Currency ID: ' + CAST(COALESCE(@ToSupplierCurrencyID,0) as varchar(200)) + ')';
			GOTO EarlyOut
		END
	-----Merge Supplier Records-----
	PRINT 'Merging supplier ' + @FromSupplier + '(ID: ' + CAST(@FromSupplierID as varchar(200)) + ') into supplier '  + @ToSupplier + '(ID: ' + CAST(@ToSupplierID as varchar(200)) + ')';
		--PostAP & audit
		UPDATE PostAP SET AccountLink = @ToSupplierID WHERE AccountLink = @FromSupplierID;
		IF OBJECT_ID(N'dbo.PostAP_Audit', N'U') IS NOT NULL
			BEGIN
				UPDATE PostAP_Audit SET AccountLink = @ToSupplierID WHERE AccountLink = @FromSupplierID;
			END
		--PostGL (DrCrAccount)  & audit.
		UPDATE PostGL SET DrCrAccount = @ToSupplierID WHERE DrCrAccount = @FromSupplierID AND PostGL.Id IN ('APTx', 'Grv', 'OGrv', 'Rts', 'CBAP', 'TaxAP')
		IF OBJECT_ID(N'dbo.PostGL_Audit', N'U') IS NOT NULL
			BEGIN
				UPDATE PostGL_Audit SET AccountLink = @ToSupplierID WHERE AccountLink = @FromSupplierID;
			END
		--InvNum
		UPDATE InvNum SET AccountID  = @ToSupplierID WHERE AccountID = @FromSupplierID AND DocType IN (2,3,5)
		--
	-----DELETE Supplier Records-----
	DELETE FROM Vendor WHERE DCLink = @FromSupplierID
GOTO ExitNow
EarlyOut:
	DECLARE @msg nvarchar(300)
	SET @msg = 'Cancelling merge between supplier ' + @FromSupplier + '(ID: ' + CAST(COALESCE(@FromSupplierID,0) as varchar(200)) + ') into supplier '  + @ToSupplier + '(ID: ' + CAST(COALESCE(@ToSupplierID,0) as varchar(200)) + ')';
	RaisError(@msg,11,1)
ExitNow:
END