USP_MERGETASK_CONSTITUENTSALESORDER

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

    declare @CHANGEDATE datetime = getdate();

    -- write sql to handle merging the data for the given source and target IDs


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

    update dbo.SALESORDER
    set RECIPIENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where RECIPIENTID = @SOURCEID;

    update dbo.SALESORDERATTACHMENT
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;

    update dbo.SALESORDERITEMMEMBER
    set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where 
        CONSTITUENTID = @SOURCEID and
        not exists( --Cannot make constituent a member on a membership they already belong

            select 1
            from dbo.[SALESORDERITEMMEMBER] [SOIM]
            where 
                [SALESORDERITEMMEMBER].[SALESORDERITEMMEMBERSHIPID] = [SOIM].[SALESORDERITEMMEMBERSHIPID] and
                [SOIM].[CONSTITUENTID] = @TARGETID
        );

    update dbo.SALESORDERITEMMEMBERSHIP
    set GIVENBYID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where GIVENBYID = @SOURCEID;

    update dbo.SALESORDERMEDIALINK
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;

    update dbo.SALESORDERNOTE
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;

    update dbo.ITINERARY
    set LEADERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where LEADERID = @SOURCEID;    

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

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

    update dbo.RESERVATIONATTACHMENT
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;    

    update dbo.RESERVATIONMEDIALINK
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;        

    update dbo.RESERVATIONNOTE
    set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
    where AUTHORID = @SOURCEID;    

    return 0;