USP_MERGETASK_CONSTITUENTTEAMFUNDRAISING

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

                    declare @CURRENTDATE datetime;
          declare @SOURCEROLE int,
                      @TARGETROLE int,
                      @SRCBIT bit,
                      @TRGTBIT bit,
                      @EVENTID uniqueidentifier,
                      @SRCFRID uniqueidentifier,
                      @TRGTFRID uniqueidentifier,
                      @SRCLEADERID uniqueidentifier,
                      @TRGTLEADERID uniqueidentifier;

                    set @CURRENTDATE = getdate();

                    -- If the source does not have a team fundraiser record,

                    -- then there is no need to continue

                    if exists
                    (
                        select top(1) ID
                        from dbo.TEAMFUNDRAISER
                        where CONSTITUENTID = @SOURCEID
                    )
                    begin
                        declare @contextCache varbinary(128);

                        -- Next we must determine if the target has any team fundraising

                        -- records for appeals for which the source has team fundraising

                        -- records

                        if exists
                        (
                            select top(1) b.ID
                            from dbo.TEAMFUNDRAISER a
                            cross apply dbo.TEAMFUNDRAISER b
              join dbo.EVENT e on a.APPEALID = e.APPEALID
              join dbo.EVENTEXTENSION ex ON e.ID = ex.EVENTID
                            where a.CONSTITUENTID = @SOURCEID
                            and b.CONSTITUENTID = @TARGETID
                            and a.APPEALID = b.APPEALID
                        )
                        begin

                            -- Create a temporary table that holds all

                            -- of the redundant source-target registrant

                            -- pairs

                            create table #REDUNDANTTEAMFUNDRAISERS(SRCFRID uniqueidentifier, TRGTFRID uniqueidentifier, EVENTID uniqueidentifier);
                            begin try

                                insert into #REDUNDANTTEAMFUNDRAISERS
                                select a.ID, b.ID, e.ID
                                from dbo.TEAMFUNDRAISER a
                                cross apply dbo.TEAMFUNDRAISER b
                                join dbo.EVENT e on a.APPEALID = e.APPEALID
                                join dbo.EVENTEXTENSION ex ON e.ID = ex.EVENTID
                                where a.CONSTITUENTID = @SOURCEID
                                and b.CONSTITUENTID = @TARGETID
                                and a.APPEALID = b.APPEALID;

                update dbo.TEAMFUNDRAISER
                                set CONSTITUENTID = @TARGETID, 
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where CONSTITUENTID = @SOURCEID
                                and ID not in
                                (
                                    select SRCFRID from #REDUNDANTTEAMFUNDRAISERS
                                );                    

                                while exists(select top 1 SRCFRID from #REDUNDANTTEAMFUNDRAISERS)
                                begin
                                    select top 1 @EVENTID=EVENTID, @SRCFRID=SRCFRID, @TRGTFRID=TRGTFRID from #REDUNDANTTEAMFUNDRAISERS

                                    select @SRCLEADERID=a.ID, @TRGTLEADERID=b.ID
                                    from dbo.TEAMFUNDRAISINGTEAMCAPTAIN a
                                    cross apply dbo.TEAMFUNDRAISINGTEAMCAPTAIN b 
                                    join dbo.TEAMEXTENSION txa on a.TEAMFUNDRAISINGTEAMID = txa.TEAMFUNDRAISINGTEAMID
                                    join dbo.TEAMEXTENSION txb on b.TEAMFUNDRAISINGTEAMID = txb.TEAMFUNDRAISINGTEAMID
                                    where a.CONSTITUENTID = @SOURCEID
                                        and b.CONSTITUENTID = @TARGETID
                                        and txa.EVENTID = txb.EVENTID and txa.EVENTID = @EVENTID

                                    /* source fundraiser not in a team */
                                    select @SOURCEROLE = RO.RoleCode, @SRCBIT = case when RO.RoleCode in (0,1,3) then 1 else 0 end from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, null) RO where CONSTITUENTID = @SOURCEID
                                    select @TARGETROLE = RO.RoleCode, @TRGTBIT= case when RO.RoleCode in (0,1,3) then 1 else 0 end from dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID, null) RO where CONSTITUENTID = @TARGETID    

                                    -- 0: company leader, 1: team leader, 2: team member, 3:household leader, 4:household member

                                    --case if source is in a higher hierarchy and target is lower hierarchy        

                                    -- OR source bit and target bit are same but source role is higher than target role

                                    if ((@SRCBIT > @TRGTBIT)  )     -- S = Leader and T = Member

                                    begin        
                                    -- remove extra team fundraiser whose role is lower than source's role

                                        delete from dbo.TEAMFUNDRAISER where ID = @TRGTFRID

                                        update dbo.TEAMFUNDRAISER
                                        set CONSTITUENTID = @TARGETID, 
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        where ID = @SRCFRID

                                        update dbo.TEAMFUNDRAISINGTEAMCAPTAIN                                     
                                        set CONSTITUENTID = @TARGETID, 
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        where ID = @SRCLEADERID
                                    end

                                    delete from #REDUNDANTTEAMFUNDRAISERS where SRCFRID = @SRCFRID and TRGTFRID = @TRGTFRID and EVENTID = @EVENTID                                
                                end                

                                -- delete the source's lingering team fundraiser records

                                set @contextCache = CONTEXT_INFO();
                                if not @CHANGEAGENTID is null
                                    set CONTEXT_INFO @CHANGEAGENTID;
                                delete from dbo.TEAMFUNDRAISER
                                where CONSTITUENTID = @SOURCEID;
                                if not @contextCache is null
                                    set CONTEXT_INFO @contextCache;

                                -- Finally, drop the redundant registrant table

                                drop table #REDUNDANTTEAMFUNDRAISERS;
                            end try
                            begin catch
                                drop table #REDUNDANTTEAMFUNDRAISERS;

                                DECLARE @ErrorMessage NVARCHAR(4000);
                                DECLARE @ErrorSeverity INT;
                                DECLARE @ErrorState INT;

                                SELECT 
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                -- Use RAISERROR inside the CATCH block to return error

                                -- information about the original error that caused

                                -- execution to jump to the CATCH block.

                                RAISERROR (@ErrorMessage, -- Message text.

                                           @ErrorSeverity, -- Severity.

                                           @ErrorState -- State.

                                           );
                            end catch
                        end
                        else
                        -- Otherwise the target & source do not have any team

                        -- fundraiser records that share appeals, so the 

                        -- operation it much simpler

                        begin
                            update dbo.TEAMFUNDRAISER
                            set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE
                            where CONSTITUENTID = @SOURCEID;        
                        end
                    end


                    -- Make the target the captain of any teams where the source

                    -- is the captain, except where the target is already a

                    -- captain of the team.

                    update dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                    set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE
                    where CONSTITUENTID = @SOURCEID
                    and ID not in
                    (
                        /*select a.ID
                        from dbo.TEAMFUNDRAISINGTEAMCAPTAIN a
                        cross apply dbo.TEAMFUNDRAISINGTEAMCAPTAIN b
                        where a.CONSTITUENTID = @SOURCEID
                        and b.CONSTITUENTID = @TARGETID
                        and a.TEAMFUNDRAISINGTEAMID = b.TEAMFUNDRAISINGTEAMID

            union all*/

            select a.ID
                        from dbo.TEAMFUNDRAISINGTEAMCAPTAIN a
                        cross apply dbo.TEAMFUNDRAISINGTEAMCAPTAIN b
            join dbo.TEAMEXTENSION txa on a.TEAMFUNDRAISINGTEAMID = txa.TEAMFUNDRAISINGTEAMID
                        join dbo.TEAMEXTENSION txb on b.TEAMFUNDRAISINGTEAMID = txb.TEAMFUNDRAISINGTEAMID
                                    where a.CONSTITUENTID = @SOURCEID
                                        and b.CONSTITUENTID = @TARGETID
                                        and txa.EVENTID = txb.EVENTID            
                    );


        if (select dbo.UFN_CONSTITUENT_HASTEAMCONSTITUENTCONFLICT(@SOURCEID,@TARGETID)) = 0
          update dbo.TEAMEXTENSION set  
              TEAMCONSTITUENTID = @TARGETID,
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED = @CURRENTDATE
          where TEAMCONSTITUENTID = @SOURCEID
          and TEAMFUNDRAISINGTEAMID not in 
          (
    select a.TEAMFUNDRAISINGTEAMID 
            from dbo.TEAMEXTENSION a
            cross apply dbo.TEAMEXTENSION b 
            where a.TEAMCONSTITUENTID = @SOURCEID
            and b.TEAMCONSTITUENTID = @TARGETID
            and a.EVENTID = b.EVENTID
          )

                    -- Delete the source's redundant team captain records

                    set @contextCache = CONTEXT_INFO();
                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID;
                    delete from dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                    where CONSTITUENTID = @SOURCEID;
                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache;


                    return 0;