UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETEXPIRATIONDATE

create function dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETEXPIRATIONDATE(@MEMBERSHIPID as uniqueidentifier, @ORIGINALTRANSACTIONEXPIRATIONDATE datetime)
returns datetime
with execute as caller
as begin

    declare @RENEWALWINDOWSTARTSINMONTHS tinyint
    declare @TOTALGRACEPERIODINMONTHS tinyint = 0
    declare @GRACEPERIODINMONTHS tinyint = 0
    declare @RENEWALRULESID uniqueidentifier
    declare @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE datetime

    select    @RENEWALWINDOWSTARTSINMONTHS = ML.BEFOREEXPIRATION,
            @TOTALGRACEPERIODINMONTHS = RR.AFTEREXPIRATION,
            @RENEWALRULESID = RR.ID
    from dbo.MEMBERSHIP M
    join dbo.MEMBERSHIPLEVEL ML
      on M.MEMBERSHIPLEVELID = ML.ID
    join dbo.MEMBERSHIPLEVELRENEWALRULES RR
      on ML.ID = RR.MEMBERSHIPLEVELID
    where M.ID = @MEMBERSHIPID

    if @TOTALGRACEPERIODINMONTHS > 0
    begin
        select @GRACEPERIODINMONTHS = R.INTERVALCODE + 1 --The code is off by one, 0 = 1 month, 1 = 2 months

        from MEMBERSHIPLEVELRENEWALRULES RR
        join MEMBERSHIPLEVELRENEWAL R
          on RR.ID = @RENEWALRULESID
         and R.ID = RR.MEMBERSHIPLEVELRENEWAL1ID
        where R.STATUSCODE != 4 --Don't include any graceperiod if during that time the membership is lapsed anyway


        select @GRACEPERIODINMONTHS = @GRACEPERIODINMONTHS + R.INTERVALCODE + 1 --The code is off by one, 0 = 1 month, 1 = 2 months

        from MEMBERSHIPLEVELRENEWALRULES RR
        join MEMBERSHIPLEVELRENEWAL R
          on RR.ID = @RENEWALRULESID
         and R.ID = RR.MEMBERSHIPLEVELRENEWAL2ID
        where R.STATUSCODE != 4 --Don't include any graceperiod if during that time the membership is lapsed anyway


        select @GRACEPERIODINMONTHS = @GRACEPERIODINMONTHS + R.INTERVALCODE + 1 --The code is off by one, 0 = 1 month, 1 = 2 months

        from MEMBERSHIPLEVELRENEWALRULES RR
        join MEMBERSHIPLEVELRENEWAL R
          on RR.ID = @RENEWALRULESID
         and R.ID = RR.MEMBERSHIPLEVELRENEWAL3ID
        where R.STATUSCODE != 4 --Don't include any graceperiod if during that time the membership is lapsed anyway

    end    

    if @GRACEPERIODINMONTHS = 13
    begin
        set @GRACEPERIODINMONTHS = @TOTALGRACEPERIODINMONTHS
    end

    if @GRACEPERIODINMONTHS > 0
    begin
        set @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE = DATEADD(month, @GRACEPERIODINMONTHS, @ORIGINALTRANSACTIONEXPIRATIONDATE)
    end
    else
    begin
        set @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE = @ORIGINALTRANSACTIONEXPIRATIONDATE
    end

    return dbo.UFN_DATE_GETLATESTTIME(@ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE)
end