Sage Evolution
Space shortcuts
Page tree
Skip to end of metadata
Go to start of metadata



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!


See for the Vendor / Supplier version this page: SQL - Merge Suppliers


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
  • No labels