USP_MERGETASK_STEWARDSHIPPLANS

                CREATE procedure dbo.USP_MERGETASK_STEWARDSHIPPLANS
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier,
                    @DUPLICATESTEWARDSHIPPLANRESOLUTION tinyint = 0
                )
                as
                    set nocount on;

                    declare @CURRENTDATE datetime = getdate();

                    --Transfer all plans from the source to the target that do not already exist on the target
                    if @DUPLICATESTEWARDSHIPPLANRESOLUTION = 0 -- Merge identical plans
                    begin
                        -- If the source and target each have plans with both the same name and type,
                        -- then a suffix will be added to the name to prevent the unique constraint
                        -- violation.                        
                        declare @NEWPLANNAME table
                        (
                            ID uniqueidentifier,
                            NEWNAME nvarchar(100)
                        );

                        if exists
                        (
                            select SOURCESSTEWARDSHIPPLAN.ID
                            from dbo.STEWARDSHIPPLAN SOURCESSTEWARDSHIPPLAN
                            cross apply dbo.STEWARDSHIPPLAN TARGETSSTEWARDSHIPPLAN
                            where 
                                SOURCESSTEWARDSHIPPLAN.CONSTITUENTID = @SOURCEID
                                and TARGETSSTEWARDSHIPPLAN.CONSTITUENTID = @TARGETID
                                and SOURCESSTEWARDSHIPPLAN.NAME = TARGETSSTEWARDSHIPPLAN.NAME
                        )
                        begin
                            -- Pull back duplicate plans
                            declare DUPLICATEPLANCURSOR cursor local fast_forward for
                            select
                                SOURCESSTEWARDSHIPPLAN.ID,
                                SOURCESSTEWARDSHIPPLAN.NAME
                            from dbo.STEWARDSHIPPLAN SOURCESSTEWARDSHIPPLAN
                            cross apply dbo.STEWARDSHIPPLAN TARGETSSTEWARDSHIPPLAN
                            where 
                                SOURCESSTEWARDSHIPPLAN.CONSTITUENTID = @SOURCEID
                                and TARGETSSTEWARDSHIPPLAN.CONSTITUENTID = @TARGETID
                                and SOURCESSTEWARDSHIPPLAN.NAME = TARGETSSTEWARDSHIPPLAN.NAME

                            declare 
                                @DUPLICATEPLANID uniqueidentifier, 
                                @DUPLICATEPLANNAME nvarchar(100);

                            open DUPLICATEPLANCURSOR;
                            fetch next from DUPLICATEPLANCURSOR into @DUPLICATEPLANID, @DUPLICATEPLANNAME;

                            while @@FETCH_STATUS = 0
                            begin
                                declare @SUFFIXCOUNTER int = 0, @PLANNAMEVALID bit = 0;

                                -- Build a name unique to the constituent/plan.  Using a do-while loop to avoid duplicating the name building code.
                                while 1 = 1
                                begin
                                    set @SUFFIXCOUNTER = @SUFFIXCOUNTER + 1;

                                    declare @SUFFIX nvarchar(15), @NEWNAME nvarchar(100);
                                    set @SUFFIX = ' (' + cast(@SUFFIXCOUNTER as nvarchar(10)) + ')';
                                    set @NEWNAME = substring(@DUPLICATEPLANNAME, 1, 100 - len(@SUFFIX)) + @SUFFIX;

                                    if not exists (    select 1 
                                                    from dbo.STEWARDSHIPPLAN
                                                    where
                                                        CONSTITUENTID = @TARGETID and
                                                        NAME = @NEWNAME

                                                    union all

                                                    -- Check source plans to make sure we don't choose a new name that is already in use on the source
                                                    select 1 
                                                    from dbo.STEWARDSHIPPLAN
                                                    where
                                                        CONSTITUENTID = @SOURCEID and
                                                        NAME = @NEWNAME)
                                    begin
                                        set @PLANNAMEVALID = 1;
                                        break;
                                    end
                                end

                                insert into @NEWPLANNAME (ID, NEWNAME)
                                values (@DUPLICATEPLANID, @NEWNAME);

                                fetch next from DUPLICATEPLANCURSOR into @DUPLICATEPLANID, @DUPLICATEPLANNAME;
                            end

                            close DUPLICATEPLANCURSOR
                            deallocate DUPLICATEPLANCURSOR
                        end


                        update dbo.STEWARDSHIPPLAN
                        set
                            NAME =    case
                                        when NEWPLANNAME.ID is null then STEWARDSHIPPLAN.NAME
                                        else NEWPLANNAME.NEWNAME
                                    end,
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        from dbo.STEWARDSHIPPLAN
                        left join @NEWPLANNAME NEWPLANNAME on STEWARDSHIPPLAN.ID = NEWPLANNAME.ID
                        where CONSTITUENTID = @SOURCEID;
                    end
                    else if @DUPLICATESTEWARDSHIPPLANRESOLUTION = 1 -- Do not merge identical stewardship plans
                    begin
                        update dbo.STEWARDSHIPPLAN
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
                        where 
                            CONSTITUENTID = @SOURCEID and
                            not exists
                            (
                                select 1
                                from dbo.STEWARDSHIPPLAN SUBSTEWARDSHIPPLAN
                                where
                                    SUBSTEWARDSHIPPLAN.CONSTITUENTID = @TARGETID and
                                    SUBSTEWARDSHIPPLAN.NAME = STEWARDSHIPPLAN.NAME
                            );
                    end
                    else -- Do not merge constituents
                    begin
                        if exists
                        (
                            select top(1) a.ID
                            from dbo.STEWARDSHIPPLAN a
                            cross apply dbo.STEWARDSHIPPLAN b
                            where a.CONSTITUENTID = @SOURCEID
                            and b.CONSTITUENTID = @TARGETID
                            and a.NAME = b.NAME
                        )
                            RAISERROR('These constituents were not merged because they both have stewardship plans with the same name.  Please resolve this conflict and then run the merge again.', 16, 1);
                        else
                            update dbo.STEWARDSHIPPLAN
                            set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
                            where 
                                CONSTITUENTID = @SOURCEID
                    end

                      --Transfer plan manager to target
                      update
                        dbo.STEWARDSHIPPLAN
                      set
                        MANAGERID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        MANAGERID = @SOURCEID;

                      --Transfer plan stewards to target
                      update
                        SOURCESTEWARD
                      set
                        CONSTITUENTID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      from
                        dbo.STEWARDSHIPPLANSTEWARD SOURCESTEWARD
                      where
                        CONSTITUENTID = @SOURCEID and
                        not exists (select TARGETSTEWARD.ID from dbo.STEWARDSHIPPLANSTEWARD TARGETSTEWARD where TARGETSTEWARD.CONSTITUENTID = @TARGETID and TARGETSTEWARD.PLANID = SOURCESTEWARD.PLANID);

                      --Transfer documentation authors to target
                      update
                        dbo.STEWARDSHIPATTACHMENT
                      set
                        AUTHORID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        AUTHORID = @SOURCEID;

                      update
                        dbo.STEWARDSHIPMEDIALINK
                      set
                        AUTHORID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        AUTHORID = @SOURCEID;

                      update
                        dbo.STEWARDSHIPNOTE
                      set
                        AUTHORID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        AUTHORID = @SOURCEID;

                      --Transfer step assigned constituent and contact person
                      update
                        dbo.STEWARDSHIPPLANSTEP
                      set
                        CONTACTPERSONID = @TARGETID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where
                        CONTACTPERSONID = @SOURCEID;

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

                      return 0;