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!
See for customer / client version this page: SQL - Merge Customers
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