UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION_2

CREATE function dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION_2
(
    @EXPIRATIONDATE datetime,
    @TERMID uniqueidentifier
)
returns datetime
as
begin
    declare @DATALOADED bit = 0;
    declare @RENEWALEXPIRATIONDATE datetime = null;
    declare @PROGRAMTYPECODE tinyint = 0;
    declare @TERMLENGTHCODE tinyint = 0;
    declare @TERMTIMELENGTH smallint = 0;
    declare @CUTOFFDAY tinyint;
    declare @EXPIRESONCODE tinyint;

    select
        @CUTOFFDAY = MEMBERSHIPPROGRAM.CUTOFFDAY,
        @EXPIRESONCODE = MEMBERSHIPPROGRAM.EXPIRESONCODE,
        @DATALOADED = 1,
        @TERMLENGTHCODE = MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
        @TERMTIMELENGTH = MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
        @PROGRAMTYPECODE = MEMBERSHIPPROGRAM.PROGRAMTYPECODE
    from dbo.MEMBERSHIPLEVELTERM
    inner join dbo.MEMBERSHIPLEVEL
        on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
    inner join dbo.MEMBERSHIPPROGRAM
        on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    where MEMBERSHIPLEVELTERM.ID = @TERMID;

    if @DATALOADED = 1 and @PROGRAMTYPECODE = 0
        begin
            set @RENEWALEXPIRATIONDATE = 
                    case @TERMLENGTHCODE
                        when 0 then dateadd(mm, @TERMTIMELENGTH, @EXPIRATIONDATE)
                        when 1 then dateadd(yy, @TERMTIMELENGTH, @EXPIRATIONDATE)
                    end;
        end

    if @EXPIRESONCODE = 1
        begin
            if @CUTOFFDAY <= day(@EXPIRATIONDATE)
                begin
                    set @RENEWALEXPIRATIONDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@RENEWALEXPIRATIONDATE, 1);
                end
                else
                begin
                    set @RENEWALEXPIRATIONDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@RENEWALEXPIRATIONDATE, 1);
                end
        end
    return @RENEWALEXPIRATIONDATE;
end