USP_SPONSORSHIP_COMPLETETRANSFER

CREATE procedure dbo.USP_SPONSORSHIP_COMPLETETRANSFER (
    @TOSPONSORSHIPID uniqueidentifier,
    @FROMSPONSORSHIPID uniqueidentifier,
    @UPDATEAVAILABILITY bit = 1,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @TRANSFERDATE date;
    set @TRANSFERDATE = @CURRENTDATE;

    select @TRANSFERDATE = case when STARTDATE < @TRANSFERDATE then @TRANSFERDATE else STARTDATE end
    from dbo.SPONSORSHIP
    where ID = @FROMSPONSORSHIPID;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    begin try
        -- inactivate the old sponsorship

        exec dbo.USP_SPONSORSHIP_INACTIVATE
            @FROMSPONSORSHIPID,
            @TRANSFERDATE,
            @UPDATEAVAILABILITY,
            @CHANGEAGENTID

        update dbo.SPONSORSHIP
        set ISMOSTRECENTFORCOMMITMENT = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @FROMSPONSORSHIPID;

        -- activate the new sponsorship

        update dbo.SPONSORSHIP
        set STATUSCODE = 1,
            ISMOSTRECENTFORCOMMITMENT = 1,
            STARTDATE = @TRANSFERDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @TOSPONSORSHIPID;

        -- update recurring gift to the new child's designation

        declare @REVENUESPLITID uniqueidentifier
        declare @TOSPONSORSHIPOPPORTUNITYID uniqueidentifier

        select @REVENUESPLITID = REVENUESPLITID,
               @TOSPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITYID
        from dbo.SPONSORSHIP
        where ID = @TOSPONSORSHIPID;

        if @REVENUESPLITID is not null
            exec dbo.USP_SPONSORSHIP_UPDATEDESIGNATION
                @REVENUESPLITID,
                @TOSPONSORSHIPOPPORTUNITYID,
                @CHANGEAGENTID        

        -- record the complete transfer transaction

        insert into dbo.SPONSORSHIPTRANSACTION
        (
            ID,
            SPONSORSHIPCOMMITMENTID,
            TRANSACTIONSEQUENCE,
            ACTIONCODE,
            SPONSORSHIPREASONID,
            CONTEXTSPONSORSHIPID,
            TARGETSPONSORSHIPID,
            TRANSACTIONDATE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            SPONSORSHIPCOMMITMENTID,
            (select max(MAXTRAN.TRANSACTIONSEQUENCE)+1 from dbo.SPONSORSHIPTRANSACTION as MAXTRAN where MAXTRAN.SPONSORSHIPCOMMITMENTID = INITIALTRANSFER.SPONSORSHIPCOMMITMENTID),
            8,
            SPONSORSHIPREASONID,
            CONTEXTSPONSORSHIPID,
            TARGETSPONSORSHIPID,
            @TRANSFERDATE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.SPONSORSHIPTRANSACTION as INITIALTRANSFER
        where CONTEXTSPONSORSHIPID = @FROMSPONSORSHIPID
        and TARGETSPONSORSHIPID = @TOSPONSORSHIPID
        and ACTIONCODE = 6;
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end