USP_MERGETASK_CONSTITUENTREVENUE

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

          declare @NOW as datetime = getdate();

          update dbo.FINANCIALTRANSACTION
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          update dbo.REVENUERECOGNITION
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          update dbo.RECOGNITIONCREDIT
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          update dbo.REVENUESOLICITOR
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          /* WI 347665:  Remove the duplicate revenue letter rows from the revenue letter table where there exists two 
             revenue letters that only differ by acknowledgee. */
          declare @REVENUELETTERDUPLICATES table (
            [REVENUELETTERID] uniqueidentifier primary key
          );

          /* Insert revenue letter ids with the source constituent as an acknowledgee that have duplicate letters 
             added to the revenue (differing only by acknowledgee) */
          insert into @REVENUELETTERDUPLICATES (REVENUELETTERID)
            select ID
            from dbo.REVENUELETTER
          where 
            (REVENUELETTER.ACKNOWLEDGEEID = @SOURCEID or REVENUELETTER.ACKNOWLEDGEEID = @TARGETID)
            and exists (select top 1 1 
                        from dbo.REVENUELETTER as INNERREVENUELETTER
                        where             
                          INNERREVENUELETTER.REVENUEID = REVENUELETTER.REVENUEID
                          and INNERREVENUELETTER.LETTERCODEID = REVENUELETTER.LETTERCODEID
                          and INNERREVENUELETTER.OUTOFDATE = 0
                        group by
                          REVENUEID,
                          LETTERCODEID,
                          MKTPACKAGEID
                        having count(*) > 1);

          /* Cache current context information for deleting */
          declare @CONTEXTCACHE varbinary(128);
          set @CONTEXTCACHE = CONTEXT_INFO();

          if @CHANGEAGENTID is not null
            set CONTEXT_INFO @CHANGEAGENTID;

          /* First remove record of non acknowledged source revenue letter - if duplicates still exist */
          delete from dbo.REVENUELETTER
          where
            ID in (select REVENUELETTERID from @REVENUELETTERDUPLICATES)
            and (REVENUELETTER.ACKNOWLEDGEEID = @SOURCEID and REVENUELETTER.ACKNOWLEDGEDATE is null);

          /* Next remove record of non acknowledged target revenue letter - if duplicates still exist */
          if exists(select top 1 1 
                    from dbo.REVENUELETTER
                    where REVENUELETTER.OUTOFDATE = 0 and REVENUELETTER.ID in (select REVENUELETTERID from @REVENUELETTERDUPLICATES)
                    group by REVENUEID, LETTERCODEID, MKTPACKAGEID
                    having count(*) > 1)
            begin
              delete from dbo.REVENUELETTER
              where
                ID in (select REVENUELETTERID from @REVENUELETTERDUPLICATES)
                and (REVENUELETTER.ACKNOWLEDGEEID = @TARGETID and REVENUELETTER.ACKNOWLEDGEDATE is null);
            end

          /* Next, if both revenue letters were acknowledged remove source record - if duplicates still exist */
          if exists(select top 1 1 
                    from dbo.REVENUELETTER
                    where REVENUELETTER.OUTOFDATE = 0 and  REVENUELETTER.ID in (select REVENUELETTERID from @REVENUELETTERDUPLICATES)
                    group by REVENUEID, LETTERCODEID, MKTPACKAGEID
                    having count(*) > 1)
            begin
              delete from dbo.REVENUELETTER
              where
                ID in (select REVENUELETTERID from @REVENUELETTERDUPLICATES)
                and (REVENUELETTER.ACKNOWLEDGEEID = @SOURCEID);
            end

          /* Reset CONTEXT_INFO to previous value */
          if not @CONTEXTCACHE is null
            set CONTEXT_INFO @CONTEXTCACHE;

          update dbo.REVENUELETTER
          set ACKNOWLEDGEEID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where ACKNOWLEDGEEID = @SOURCEID;

          --Merge origination information

          declare @SOURCEORIGINATIONINFOSOURCEID uniqueidentifier = null;
          declare @SOURCEORIGINATIONREVENUEID uniqueidentifier = null;
          declare @SOURCEORIGINCODE tinyint = null;

          select 
            @SOURCEORIGINATIONREVENUEID = REVENUEID,
            @SOURCEORIGINATIONINFOSOURCEID = INFOSOURCECODEID,
            @SOURCEORIGINCODE = ORIGINCODE
          from 
            dbo.CONSTITUENTORIGINATION 
          where 
            ID = @SOURCEID

          if @SOURCEORIGINATIONREVENUEID is not null
          begin
            declare @TARGETORIGINATIONSOURCEID uniqueidentifier = null;
            declare @TARGETORIGINATIONREVENUEID uniqueidentifier = null;
            declare @TARGETORIGINCODE tinyint = null;
            declare @TARGETHASDATA bit = 0;

            select 
              @TARGETHASDATA = 1,
              @TARGETORIGINATIONSOURCEID = INFOSOURCECODEID,
              @TARGETORIGINATIONREVENUEID = REVENUEID,
              @TARGETORIGINCODE = ORIGINCODE
            from 
              dbo.CONSTITUENTORIGINATION 
            where 
              ID = @TARGETID

            if (@TARGETORIGINATIONSOURCEID is null) and (@TARGETORIGINATIONREVENUEID is null) and (@TARGETORIGINCODE is null or @TARGETORIGINCODE = 0)
            begin
              delete from dbo.CONSTITUENTORIGINATION where ID = @SOURCEID

              if @TARGETHASDATA = 1
                update dbo.CONSTITUENTORIGINATION set 
                  INFOSOURCECODEID = @SOURCEORIGINATIONINFOSOURCEID,
                  REVENUEID = @SOURCEORIGINATIONREVENUEID,
                  ORIGINCODE = coalesce(@SOURCEORIGINCODE,0),
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @NOW
                where ID = @TARGETID
              else
                insert into dbo.CONSTITUENTORIGINATION(ID, INFOSOURCECODEID, REVENUEID, ORIGINCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values(@TARGETID, @SOURCEORIGINATIONINFOSOURCEID, @SOURCEORIGINATIONREVENUEID, coalesce(@SOURCEORIGINCODE,0), @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate())
            end
          end

          -- Merge revenue batch records

          update dbo.BATCHREVENUE
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUE
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUE.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUE
          set MGMATCHINGCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUE
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUE.MGMATCHINGCONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUERECOGNITION
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUERECOGNITION
          inner join dbo.BATCHREVENUE on BATCHREVENUERECOGNITION.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUERECOGNITION.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUESOLICITOR
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUESOLICITOR
          inner join dbo.BATCHREVENUE on BATCHREVENUESOLICITOR.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUESOLICITOR.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
          set MATCHINGCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
          inner join dbo.BATCHREVENUE on BATCHREVENUEENHANCEDMATCHINGGIFTS.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUEENHANCEDMATCHINGGIFTS.MATCHINGCONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
          set ORGANIZATIONID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUEENHANCEDMATCHINGGIFTS
          inner join dbo.BATCHREVENUE on BATCHREVENUEENHANCEDMATCHINGGIFTS.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where 
            BATCHREVENUEENHANCEDMATCHINGGIFTS.ORGANIZATIONID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEAPPLICATIONPLEDGE
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUEAPPLICATIONPLEDGE
          inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATIONPLEDGE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID
          inner join dbo.BATCHREVENUE on BATCHREVENUEAPPLICATION.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where
            BATCHREVENUEAPPLICATIONPLEDGE.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP
          set GIVENBYID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP
          inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID
          inner join dbo.BATCHREVENUE on BATCHREVENUEAPPLICATION.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where
            BATCHREVENUEAPPLICATIONMEMBERSHIP.GIVENBYID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER
          set 
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
          from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER
          inner join dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER.BATCHREVENUEAPPLICATIONMEMBERSHIPID = BATCHREVENUEAPPLICATIONMEMBERSHIP.ID
          inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONMEMBERSHIPID
          inner join dbo.BATCHREVENUE on BATCHREVENUEAPPLICATION.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where
            BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUELETTER
          set
            ACKNOWLEDGEEID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
          from dbo.BATCHREVENUELETTER
          inner join dbo.BATCHREVENUE on BATCHREVENUELETTER.BATCHREVENUEID = BATCHREVENUE.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where
            BATCHREVENUELETTER.ACKNOWLEDGEEID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          -- Throw an error if updating BATCHREVENUEREGISTRANT will violate CK_BATCHREVENUEREGISTRANT_VALIDCONSTITUENTID (which .

          -- Throwing an explicit error so the batch number can be outputted.

          declare @DUPLICATEREGISTRANTBATCHNUMBER nvarchar(100);
          select top 1 
            @DUPLICATEREGISTRANTBATCHNUMBER = BATCHNUMBER
          from dbo.BATCH
          inner join dbo.BATCHREVENUEREGISTRANT on BATCH.ID = BATCHREVENUEREGISTRANT.BATCHID
          where
            BATCH.STATUSCODE in (0, 1, 3) and -- Uncommitted, Committed,Committing

            BATCHREVENUEREGISTRANT.CONSTITUENTID = @SOURCEID and
            dbo.UFN_REVENUEBATCHREGISTRANT_GETREGISTRATIONCOUNTFORCONSTITUENTID(BATCHREVENUEREGISTRANT.EVENTID, @TARGETID, BATCHREVENUEREGISTRANT.BATCHID) > 0;

          if @DUPLICATEREGISTRANTBATCHNUMBER is not null
          begin
            declare @DUPLICATEREGISTRANTERRORMESSAGE nvarchar(500);
            set @DUPLICATEREGISTRANTERRORMESSAGE = 'These constituents were not merged because the source constituent has an event registration in batch ''' + @DUPLICATEREGISTRANTBATCHNUMBER + ''' for an event that the target constituent already has an event registration for.  As a result, the event registration cannot be copied from the source to the target.  To resolve this, the batch row with the source''s event registration application needs to be removed from the batch.';
            raiserror(@DUPLICATEREGISTRANTERRORMESSAGE, 13, 1);
          end

          update dbo.BATCHREVENUEREGISTRANT
          set 
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
          from dbo.BATCHREVENUEREGISTRANT
          inner join dbo.BATCH on BATCHREVENUEREGISTRANT.BATCHID = BATCH.ID
          where
            BATCHREVENUEREGISTRANT.CONSTITUENTID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing


          update dbo.BATCHREVENUEREGISTRANTPACKAGE
          set 
            CONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
          where
            BATCHREVENUEREGISTRANTPACKAGE.CONSTITUENTID = @SOURCEID and
            exists
            (
              select BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
              from dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
              inner join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
              inner join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
              inner join dbo.BATCH on BATCHREVENUEREGISTRANT.BATCHID = BATCH.ID
              where
                BATCH.STATUSCODE in (0, 1, 3)  -- Uncommitted, Committed, Committing

            );

          update dbo.BATCHREVENUEREGISTRANTPACKAGE
          set 
            GUESTOFCONSTITUENTID = @TARGETID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @NOW
          where
            BATCHREVENUEREGISTRANTPACKAGE.GUESTOFCONSTITUENTID = @SOURCEID and
            exists
            (
              select BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTPACKAGEID
              from dbo.BATCHREVENUEREGISTRANTREGISTRATIONMAP
              inner join dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = BATCHREVENUEREGISTRANTREGISTRATION.ID
              inner join dbo.BATCHREVENUEREGISTRANT on BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID = BATCHREVENUEREGISTRANT.ID
              inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEREGISTRANTID
              inner join dbo.BATCHREVENUE on BATCHREVENUEAPPLICATION.BATCHREVENUEID = BATCHREVENUE.ID
              inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
              where
                BATCH.STATUSCODE in (0, 1, 3) -- Uncommitted, Committed, Committing

            );

          -- Merge constituents referenced by revenue batch constituents

          with CTE_VALIDBATCHREVENUECONSTITUENT as
          (
            select
              BATCHREVENUECONSTITUENT.ID
            from dbo.BATCHREVENUECONSTITUENT
            inner join dbo.BATCHREVENUE on BATCHREVENUE.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
            inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
            where
              BATCH.STATUSCODE in (0, 1, 3)  -- Uncommitted, Committed, Committing                    

          )                    
          update dbo.BATCHREVENUECONSTITUENT
          set EXISTINGCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where 
            BATCHREVENUECONSTITUENT.ID in
            (
              select ID
              from CTE_VALIDBATCHREVENUECONSTITUENT

              union all

              select
                BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
              from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
              inner join CTE_VALIDBATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = CTE_VALIDBATCHREVENUECONSTITUENT.ID

              union all

              select
                BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID
              from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
              inner join CTE_VALIDBATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID = CTE_VALIDBATCHREVENUECONSTITUENT.ID

              union all

              select
                BATCHREVENUECONSTITUENTRELATION.RELATIONID
              from dbo.BATCHREVENUECONSTITUENTRELATION
              inner join CTE_VALIDBATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = CTE_VALIDBATCHREVENUECONSTITUENT.ID
            ) and
            BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID = @SOURCEID;

          -- PARENTCORPID can only be set for using the add org form.  That doesn't allow relationships or group members so the advanced logic

          -- used in the above query to update BATCHREVENUECONSTITUENT isn't needed.

          update dbo.BATCHREVENUECONSTITUENT set
            PARENTCORPID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          from dbo.BATCHREVENUECONSTITUENT
          inner join dbo.BATCHREVENUE on BATCHREVENUE.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
          inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
          where
            BATCHREVENUECONSTITUENT.PARENTCORPID = @SOURCEID and
            BATCH.STATUSCODE in (0, 1, 3);  -- Uncommitted, Committed, Committing                    



          -- Recurring gift amendments

          if @CHANGEAGENTID is not null
            set CONTEXT_INFO @CHANGEAGENTID;

          -- Remove constituent change amendments where the merge will result in there no longer being a change.

          delete from dbo.RECURRINGGIFTAMENDMENT
          where (CONSTITUENTID = @SOURCEID and PREVIOUSCONSTITUENTID = @TARGETID)
          or (PREVIOUSCONSTITUENTID = @SOURCEID and CONSTITUENTID = @TARGETID);

          if not @CONTEXTCACHE is null
            set CONTEXT_INFO @CONTEXTCACHE;

          update dbo.RECURRINGGIFTAMENDMENT
          set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where CONSTITUENTID = @SOURCEID;

          update dbo.RECURRINGGIFTAMENDMENT
          set PREVIOUSCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @NOW
          where PREVIOUSCONSTITUENTID = @SOURCEID;

          return 0;