USP_MERGETASK_CONSTITUENTACCOUNT

                CREATE procedure dbo.USP_MERGETASK_CONSTITUENTACCOUNT
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @CHANGEDATE datetime = getdate();

                    -- Open the symmetric key for decryption

                    exec dbo.USP_GET_KEY_ACCESS;

                    -- Determine how many redundant accounts

                    -- there are between the source and target

                    declare @redundantAccountCount int;
                    select  @redundantAccountCount = count(*)
                    from dbo.CONSTITUENTACCOUNT a
                    inner join dbo.CONSTITUENTACCOUNT b
                    on a.FINANCIALINSTITUTIONID = b.FINANCIALINSTITUTIONID
                    and coalesce(convert(nvarchar(50), DecryptByKey(a.ACCOUNTNUMBER)),'') = coalesce(convert(nvarchar(50), DecryptByKey(b.ACCOUNTNUMBER)),'')
                    where a.CONSTITUENTID = @SOURCEID
                    and b.CONSTITUENTID = @TARGETID;

                    if @redundantAccountCount > 0
                    begin
                        -- Create a temporary table that holds all

                        -- of the redundant source-target account pairs

                        create table #REDUNDANTACCOUNTS(SRCACCTID uniqueidentifier, TRGTACCTID uniqueidentifier);
                        insert into #REDUNDANTACCOUNTS
                        select a.ID, b.ID
                        from dbo.CONSTITUENTACCOUNT a
                        inner join dbo.CONSTITUENTACCOUNT b
                        on a.FINANCIALINSTITUTIONID = b.FINANCIALINSTITUTIONID
                        and coalesce(convert(nvarchar(50), DecryptByKey(a.ACCOUNTNUMBER)),'') = coalesce(convert(nvarchar(50), DecryptByKey(b.ACCOUNTNUMBER)),'')
                        where a.CONSTITUENTID = @SOURCEID
                        and b.CONSTITUENTID = @TARGETID;

                        -- Move all of the accounts that aren't redundant

                        update dbo.CONSTITUENTACCOUNT
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                        and ID not in
                        (
                            select SRCACCTID from #REDUNDANTACCOUNTS
                        );

                        -- If an account is redundant then it cannot be

                        -- moved to the target.  However, any payment

                        -- details associated with the source account

                        -- need to be preserved by being re-associated

                        -- with the corresponding target account

                        update CHECKPAYMENTMETHODDETAIL
                        set CONSTITUENTACCOUNTID = TRGTACCTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        from #REDUNDANTACCOUNTS
                        where CONSTITUENTACCOUNTID = SRCACCTID;

                        update DIRECTDEBITPAYMENTMETHODDETAIL
                        set CONSTITUENTACCOUNTID = TRGTACCTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        from #REDUNDANTACCOUNTS
                        where CONSTITUENTACCOUNTID = SRCACCTID;

                        update REVENUESCHEDULEDIRECTDEBITPAYMENT
                        set CONSTITUENTACCOUNTID = TRGTACCTID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        from #REDUNDANTACCOUNTS
                        where CONSTITUENTACCOUNTID = SRCACCTID;

                        drop table #REDUNDANTACCOUNTS;
                    end
                    else
                        -- There were no redundant accounts so

                        -- the operation is much simpler

                        update dbo.CONSTITUENTACCOUNT
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID;

                    close symmetric key sym_BBInfinity;

                    return 0;