USP_MERGETASK_CONSTITUENTRECOGNITION

                CREATE procedure dbo.USP_MERGETASK_CONSTITUENTRECOGNITION
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier,
                    @DELETEDUPES bit = 0
                )
                as
                    set nocount on;

                    declare @CHANGEDATE datetime = getdate();

                    if @DELETEDUPES = 0
                    begin
                        -- Since we are not ignoring duplicate programs, we need to check that one constituent didn't
                        -- decline a recognition level that another one has earned. If so, throw an exception.
                        if exists
                        (
                            select 'x'
                            from dbo.CONSTITUENTRECOGNITION
                                inner join dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = CONSTITUENTRECOGNITIONDECLINEDLEVEL.RECOGNITIONLEVELID
                            where
                                (
                                    CONSTITUENTRECOGNITION.CONSTITUENTID = @SOURCEID and
                                    CONSTITUENTRECOGNITIONDECLINEDLEVEL.CONSTITUENTID = @TARGETID
                                ) or
                                (
                                    CONSTITUENTRECOGNITION.CONSTITUENTID = @TARGETID and
                                    CONSTITUENTRECOGNITIONDECLINEDLEVEL.CONSTITUENTID = @SOURCEID
                                )
                        )
                        begin
                            raiserror('BBERR_CONSTITUENTRECOGNITION_DECLINEDLEVELCONFLICT',13,1);
                            return 1;
                        end

                        -- Since we are not ignoring duplicate programs, we need to check that one constituent didn't
                        -- decline a recognition program that another one has earned. If so, throw an exception.
                        if exists
                        (
                            select 'x'
                            from dbo.CONSTITUENTRECOGNITION
                                inner join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITIONDECLINEDPROGRAM.RECOGNITIONPROGRAMID
                            where
                                (
                                    CONSTITUENTRECOGNITION.CONSTITUENTID = @SOURCEID and
                                    CONSTITUENTRECOGNITIONDECLINEDPROGRAM.CONSTITUENTID = @TARGETID
                                ) or
                                (
                                    CONSTITUENTRECOGNITION.CONSTITUENTID = @TARGETID and
                                    CONSTITUENTRECOGNITIONDECLINEDPROGRAM.CONSTITUENTID = @SOURCEID
                                )
                        )
                        begin
                            raiserror('BBERR_CONSTITUENTRECOGNITION_DECLINEDPROGRAMCONFLICT',13,1);
                            return 1;
                        end

                        -- Since we are not ignoring duplicate programs, we need to check that one constituent didn't
                        -- decline a recognition program and another one has declined a level to the same program.
                        -- If so, throw an exception.
                        if exists
                        (
                            select 'x'
                            from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL
                                inner join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM on CONSTITUENTRECOGNITIONDECLINEDLEVEL.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITIONDECLINEDPROGRAM.RECOGNITIONPROGRAMID
                            where
                                (
                                    CONSTITUENTRECOGNITIONDECLINEDLEVEL.CONSTITUENTID = @SOURCEID and
                                    CONSTITUENTRECOGNITIONDECLINEDPROGRAM.CONSTITUENTID = @TARGETID
                                ) or
                                (
                                    CONSTITUENTRECOGNITIONDECLINEDLEVEL.CONSTITUENTID = @TARGETID and
                                    CONSTITUENTRECOGNITIONDECLINEDPROGRAM.CONSTITUENTID = @SOURCEID
                                )
                        )
                        begin
                            raiserror('BBERR_CONSTITUENTRECOGNITION_DECLINEDLEVELPROGRAMCONFLICT',13,1);
                            return 1;
                        end

                        update dbo.CONSTITUENTRECOGNITION
                        set 
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE,
                            STATUSCODE = 
                                -- If the status is Active or Pending and there is a duplicate level, change the copied status
                                -- to Inactive (Merge) to prevent a unique constraint violation.
                                case
                                    when STATUSCODE in (0, 2) and ID in
                                    (
                                        select a.ID
                                        from dbo.CONSTITUENTRECOGNITION a
                                            inner join dbo.CONSTITUENTRECOGNITION b on a.RECOGNITIONLEVELID = b.RECOGNITIONLEVELID 
                      inner join dbo.RECOGNITIONPROGRAM on a.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
                                        where 
                                            a.CONSTITUENTID = @SOURCEID
                                            and b.CONSTITUENTID = @TARGETID
                      and (RECOGNITIONPROGRAM.TYPECODE = 1 or year(a.EXPIRATIONDATE) = year(b.EXPIRATIONDATE))
                                    ) then 3
                                    else STATUSCODE
                                end
                        where CONSTITUENTID = @SOURCEID;

                        -- Delete entries where the target constituent has already declined the source's level.
                        -- This prevents a constraint error on the following update statement.
                        delete from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL
                        where ID in (
                            select b.ID
                            from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL a
                                inner join dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL b on a.RECOGNITIONLEVELID = b.RECOGNITIONLEVELID
                            where
                                a.CONSTITUENTID = @SOURCEID and
                                b.CONSTITUENTID = @TARGETID
                        );

                        -- Move the declined level from the source to the target
                        update dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL
                        set 
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID;

                        -- Delete entries where the target constituent has already declined the source's program.
                        -- This prevents a constraint error on the following update statement.
                        delete from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM
                        where ID in (
                            select b.ID
                            from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM a
                                inner join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM b on a.RECOGNITIONPROGRAMID = b.RECOGNITIONPROGRAMID
                            where
                                a.CONSTITUENTID = @SOURCEID and
                                b.CONSTITUENTID = @TARGETID
                        );

                        -- Move the declined program from the source to the target
                        update dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM
                        set 
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID;
                    end
                    else
                    begin
                        declare @DUPLICATEINFO table
                        (
                            RECOGNITIONID uniqueidentifier,
                            RECOGNITIONPROGRAMID uniqueidentifier
                        );

                        insert into @DUPLICATEINFO
                        select
                            a.ID,
                            null
                        from dbo.CONSTITUENTRECOGNITION a
                            inner join dbo.CONSTITUENTRECOGNITION b on a.RECOGNITIONPROGRAMID = b.RECOGNITIONPROGRAMID 
                        where
                            a.CONSTITUENTID = @SOURCEID and
                            b.CONSTITUENTID = @TARGETID

                        union all

                        select
                            null,
                            a.RECOGNITIONPROGRAMID
                        from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL a
                            inner join dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL b on a.RECOGNITIONPROGRAMID = b.RECOGNITIONPROGRAMID 
                        where
                            a.CONSTITUENTID = @SOURCEID and
                            b.CONSTITUENTID = @TARGETID

                        union all

                        select
                            null,
                            a.RECOGNITIONPROGRAMID
                        from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM a
                            inner join dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM b on a.RECOGNITIONPROGRAMID = b.RECOGNITIONPROGRAMID 
                        where
                            a.CONSTITUENTID = @SOURCEID and
                            b.CONSTITUENTID = @TARGETID;

                        --Omit duplicate member records if the @DELETEDUPES flag is set
                        update dbo.CONSTITUENTRECOGNITION
                        set
                            CONSTITUENTID = @TARGETID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where
                            CONSTITUENTID = @SOURCEID and
                            ID not in (select RECOGNITIONID from @DUPLICATEINFO);

                        -- Move declined level information for non-duplicate programs
                        update dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL
                        set 
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where
                            CONSTITUENTID = @SOURCEID and
                            RECOGNITIONPROGRAMID not in (select RECOGNITIONPROGRAMID from @DUPLICATEINFO);

                        -- Move declined program information for non-duplicate programs
                        update dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM
                        set 
                            CONSTITUENTID = @TARGETID, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where
                            CONSTITUENTID = @SOURCEID and
                            RECOGNITIONPROGRAMID not in (select RECOGNITIONPROGRAMID from @DUPLICATEINFO);
                    end

                    return 0;