UFN_MEMBERSHIP_RENEWTRANSACTIONEXISTS_BEFORETHISDATE

CREATE FUNCTION dbo.[UFN_MEMBERSHIP_RENEWTRANSACTIONEXISTS_BEFORETHISDATE]
    (@MEMBERSHIPID uniqueidentifier, @ORIGINALMEMBERSHIPTRANSACTIONID uniqueidentifier, @ORIGINALTRANSACTIONEXPIRATIONDATE datetime, @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE datetime, @FROMDATE datetime, @TODATE datetime)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN

    declare @RENEWTRANSACTIONDATE datetime = null
    declare @FIRSTDATEABLETORENEW datetime
    declare @DAYAFTERORIGINALTRANSACTIONDATE datetime
    declare @ORIGINALTRANSACTIONDATE datetime
    declare @RENEWALWINDOWSTARTSINMONTHS tinyint
    declare @CURRENTMEMBERSHIPSTATUS tinyint
    declare @LOWESTLEVELAMOUNT money
    declare @OBTAINLEVELCODE tinyint
    declare @MEMBERSHIPLEVELID uniqueidentifier
    declare @MEMBERSHIPPROGRAMID uniqueidentifier
    declare @WHATHAPPENSIFTHEYGIVEMORE tinyint

    select    @RENEWALWINDOWSTARTSINMONTHS = ML.BEFOREEXPIRATION,
            @CURRENTMEMBERSHIPSTATUS = M.STATUSCODE,
            @OBTAINLEVELCODE = ML.OBTAINLEVELCODE,
            @MEMBERSHIPLEVELID = ML.ID,
            @MEMBERSHIPPROGRAMID = ML.MEMBERSHIPPROGRAMID,
            @WHATHAPPENSIFTHEYGIVEMORE = C.WHATHAPPENSIFTHEYGIVEMORECODE
    from dbo.MEMBERSHIP M
    join dbo.MEMBERSHIPLEVEL ML
      on M.MEMBERSHIPLEVELID = ML.ID
    join dbo.MEMBERSHIPLEVELRENEWALRULES RR
      on ML.ID = RR.MEMBERSHIPLEVELID
    join dbo.MEMBERSHIPPROGRAM P
      on P.ID = ML.MEMBERSHIPPROGRAMID
    left join dbo.MEMBERSHIPPROGRAMCONTRIBUTION C
      on P.ID = C.ID
    where M.ID = @MEMBERSHIPID

    if @OBTAINLEVELCODE = 0 --dues based

    begin
        select @LOWESTLEVELAMOUNT = min(T.AMOUNT)
        from MEMBERSHIPLEVEL L
        join MEMBERSHIPLEVELTERM T
          on L.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
         and L.ID = T.LEVELID
    end
    else
    begin
        select @LOWESTLEVELAMOUNT = min(T.LOWAMOUNT)
        from MEMBERSHIPLEVEL L
        join MEMBERSHIPLEVELTERM T
          on L.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
         and L.ID = T.LEVELID
    end

    select    @ORIGINALTRANSACTIONDATE = TRANSACTIONDATE
    from dbo.[MEMBERSHIPTRANSACTION]
    where ID = @ORIGINALMEMBERSHIPTRANSACTIONID

    if @RENEWALWINDOWSTARTSINMONTHS = 13
    begin
        set @FIRSTDATEABLETORENEW = @ORIGINALTRANSACTIONDATE
    end
    else
    begin
        set @FIRSTDATEABLETORENEW = DATEADD(month, -1 * @RENEWALWINDOWSTARTSINMONTHS, @ORIGINALTRANSACTIONEXPIRATIONDATE)
    end

    set @FIRSTDATEABLETORENEW = dbo.UFN_DATE_GETEARLIESTTIME(@FIRSTDATEABLETORENEW)

    set @DAYAFTERORIGINALTRANSACTIONDATE = DATEADD(day, 1, @ORIGINALTRANSACTIONDATE)

    select @RENEWTRANSACTIONDATE = MT.TRANSACTIONDATE             
    from dbo.[MEMBERSHIPTRANSACTION] MT
    join dbo.[FINANCIALTRANSACTIONLINEITEM] TLI
    on MT.REVENUESPLITID = TLI.ID
    join dbo.[FINANCIALTRANSACTION] FT
    on FT.ID = TLI.FINANCIALTRANSACTIONID
    where MEMBERSHIPID = @MEMBERSHIPID
    and MT.ID != @ORIGINALMEMBERSHIPTRANSACTIONID
    and ((@OBTAINLEVELCODE = 0 and MT.TRANSACTIONDATE between @DAYAFTERORIGINALTRANSACTIONDATE and @TODATE)
        or
       (@OBTAINLEVELCODE = 1 and @WHATHAPPENSIFTHEYGIVEMORE = 2 and MT.TRANSACTIONDATE between @DAYAFTERORIGINALTRANSACTIONDATE and @TODATE)
       or
       (@OBTAINLEVELCODE = 1 and @WHATHAPPENSIFTHEYGIVEMORE <> 2 and MT.TRANSACTIONDATE between @FIRSTDATEABLETORENEW and @TODATE)
      )
    and ((MT.TRANSACTIONDATE <= @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE) or (@CURRENTMEMBERSHIPSTATUS <> 4 and @FROMDATE >= MT.TRANSACTIONDATE))
    and MT.ACTIONCODE != 4 --Not a drop transaction

    and FT.TRANSACTIONAMOUNT >= @LOWESTLEVELAMOUNT

    return @RENEWTRANSACTIONDATE

END