This stored proc merges the records from one customer to another one.
Note that this also can be used to "delete" customers into one "deleted" customer. <p>Note 2: Don't forget a "recalculate customer balances" afterwards.
note; this does not handle all Client type transactions depending on usage and implementation, check before usage!
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'_as_spMergeCustomers') AND type IN ( N'P', N'PC' ) )
DROP PROCEDURE _as_spMergeCustomers
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Asamco BV, Alexander Toufexis
-- Create date: 22/05/2023
-- Description: Merge customer into other customer account. (no job costing supported currently!), Note; no balances are moved on GL accounts.
-- =============================================
CREATE PROCEDURE _as_spMergeCustomers
-- Add the parameters for the stored procedure here
@FromCustomer varchar(100),
@ToCustomer varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-----DECLARES------
DECLARE @FromCustomerID int
DECLARE @ToCustomerID int
DECLARE @FromCustomerCurrencyID int
DECLARE @ToCustomerCurrencyID int
-----FETCH BASIC DATA-----
SELECT @FromCustomerID = DCLink, @FromCustomerCurrencyID = iCurrencyID FROM Client V WHERE V.Account = @FromCustomer
SELECT @ToCustomerID = DCLink, @ToCustomerCurrencyID = iCurrencyID FROM Client V WHERE V.Account = @ToCustomer
-----CHECKS & VALIDATIONS-----
--Existing accounts
IF COALESCE(@FromCustomerID,0) = 0 GOTO EarlyOut
IF COALESCE(@ToCustomerID,0) = 0 GOTO EarlyOut
--Matching currency
IF COALESCE(@FromCustomerCurrencyID,0) != COALESCE(@ToCustomerCurrencyID,0)
BEGIN
PRINT 'Currencies do not match between customer ' + @FromCustomer + '(Currency ID: ' + CAST(COALESCE(@FromCustomerCurrencyID,0) as varchar(200)) + ') into customer ' + @ToCustomer + '(Currency ID: ' + CAST(COALESCE(@ToCustomerCurrencyID,0) as varchar(200)) + ')';
GOTO EarlyOut
END
-----Merge Supplier Records-----
PRINT 'Merging customer ' + @FromCustomer + '(ID: ' + CAST(@FromCustomerID as varchar(200)) + ') into customer ' + @ToCustomer + '(ID: ' + CAST(@ToCustomerID as varchar(200)) + ')';
--PostAR & audit
UPDATE PostAR SET AccountLink = @ToCustomerID WHERE AccountLink = @FromCustomerID;
IF OBJECT_ID(N'dbo.PostAR_Audit', N'U') IS NOT NULL
BEGIN
UPDATE PostAR_Audit SET AccountLink = @ToCustomerID WHERE AccountLink = @FromCustomerID;
END
--PostGL (DrCrAccount) & audit.
UPDATE PostGL SET DrCrAccount = @ToCustomerID WHERE DrCrAccount = @FromCustomerID AND PostGL.Id IN ('ARTx', 'Inv', 'OInv', 'Crn', 'CBAR', 'TaxAR')
IF OBJECT_ID(N'dbo.PostGL_Audit', N'U') IS NOT NULL
BEGIN
UPDATE PostGL_Audit SET AccountLink = @ToCustomerID WHERE AccountLink = @FromCustomerID;
END
--InvNum
UPDATE InvNum SET AccountID = @ToCustomerID WHERE AccountID = @FromCustomerID AND DocType IN (0,1,4)
--
-----DELETE Supplier Records-----
--DELETE FROM Vendo-r WHERE DCLink = @FromCustomerID
--RECODE CUSTOMER
UPDATE Client SET Account = CONCAT('ZZ_',Account) where Account = @FromCustomer
GOTO ExitNow
EarlyOut:
DECLARE @msg nvarchar(300)
SET @msg = 'Cancelling merge between customer ' + @FromCustomer + '(ID: ' + CAST(COALESCE(@FromCustomerID,0) as varchar(200)) + ') into customer ' + @ToCustomer + '(ID: ' + CAST(COALESCE(@ToCustomerID,0) as varchar(200)) + ')';
RaisError(@msg,11,1)
ExitNow:
END
select distinct Id from PostGL