USP_SPONSOR_MAKEACTIVE

CREATE procedure dbo.USP_SPONSOR_MAKEACTIVE (
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE date,
    @SPONSORTYPECODE tinyint,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    if @CONSTITUENTID is null
    begin
        raiserror('BBERR_NOCONSTITUENT', 13, 1);
        return 1
    end
    if @STARTDATE is null
    begin
        raiserror('BBERR_NOSTARTDATE', 13, 1);
        return 1
    end

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

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

    begin try
        ------------------------------------------

        -- SPONSORDATERANGE

        ------------------------------------------

        -- cache current context

        declare @CONTEXTCACHE varbinary(128);
        set @CONTEXTCACHE = CONTEXT_INFO();
        if @CHANGEAGENTID is not null
            set CONTEXT_INFO @CHANGEAGENTID

        -- delete all future sponsordaterange

        delete from dbo.SPONSORDATERANGE
        where CONSTITUENTID = @CONSTITUENTID
        and SPONSORTYPECODE = @SPONSORTYPECODE
        and DATEFROM > @STARTDATE

        -- restore cached context

        if not @CONTEXTCACHE is null
            set CONTEXT_INFO @CONTEXTCACHE

        declare @SPONSORDATERANGEID uniqueidentifier

        -- find current sponsordaterange

        select @SPONSORDATERANGEID = ID
        from dbo.SPONSORDATERANGE
        where CONSTITUENTID = @CONSTITUENTID
        and SPONSORTYPECODE = @SPONSORTYPECODE
        and DATEFROM <= @STARTDATE
        and (DATETO is null or DATETO >= @STARTDATE)

        if @SPONSORDATERANGEID is not null
            update dbo.SPONSORDATERANGE
            set DATETO = null,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @SPONSORDATERANGEID
        else
            insert into dbo.SPONSORDATERANGE
            (
                ID,
                CONSTITUENTID,
                SPONSORTYPECODE,
                DATEFROM,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                newid(),
                @CONSTITUENTID,
                @SPONSORTYPECODE,
                @STARTDATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

        ------------------------------------------

        -- SPONSOR

        ------------------------------------------

        if not exists(select 'x' from dbo.SPONSOR where ID = @CONSTITUENTID)
            insert into dbo.SPONSOR
            (
                ID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @CONSTITUENTID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end