UFN_MEMBERSHIP_ISUPGRADEABLE

CREATE function dbo.UFN_MEMBERSHIP_ISUPGRADEABLE(@MEMBERSHIPID uniqueidentifier, @BASEDONDATE datetime = null)
returns bit
as begin
    declare @ISUPGRADEABLE bit = 0
  declare @REVENUEID uniqueidentifier = null
  declare @BALANCE money = null

  set @REVENUEID = dbo.UFN_MEMBERSHIP_GETPLEDGE(@MEMBERSHIPID)
  set @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(@REVENUEID)
    if @BASEDONDATE is null set @BASEDONDATE = getdate()


    if ((NOT @BALANCE is null) and @BALANCE > 0)
        return 0

  select @ISUPGRADEABLE = 1 where exists (
    select
          MP.ID
      from 
           dbo.MEMBERSHIP M
              inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = M.MEMBERSHIPPROGRAMID
              inner join MEMBERSHIPLEVEL ML on M.MEMBERSHIPPROGRAMID = ML.MEMBERSHIPPROGRAMID
              inner join MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
              inner join 
                    (
                        select
                            M.ID,
                            dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(M.MEMBERSHIPLEVELID, M.EXPIRATIONDATE) AS RENEWALDATE
                        from MEMBERSHIP M
                        where
                            M.ID = @MEMBERSHIPID
              and dbo.UFN_MEMBERSHIPLEVEL_ISUPGRADEABLE(M.MEMBERSHIPLEVELTERMID) = 1
                        union

                        select
                            M.ID,
                            dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(M.MEMBERSHIPLEVELID, M.EXPIRATIONDATE) AS RENEWALDATE
                        from 
                            MEMBERSHIP M
                            inner join dbo.MEMBERSHIPLEVEL ML on M.MEMBERSHIPLEVELID = ML.ID
                            inner join dbo.MEMBERSHIPLEVELTERM MLT on M.MEMBERSHIPLEVELTERMID = MLT.ID and dbo.UFN_MEMBERSHIPLEVELTERM_ISUPGRADEABLE(MLT.ID) = 1
                        where
                            M.ID = @MEMBERSHIPID
                    ) UPGRADEABLELEVELS on M.ID = UPGRADEABLELEVELS.ID
      where
          M.ID = @MEMBERSHIPID
          and dbo.UFN_MEMBERSHIPLEVEL_ISUPGRADEABLE(MLT.ID) = 1
          and MP.ISACTIVE = 1 
          and M.STATUSCODE = 0
          and ((MP.PROGRAMTYPECODE = 0) or (MP.PROGRAMTYPECODE = 2 and MLT.LIFETIMEPAYMENTOPTIONCODE = 0))
            --when there is no renewal date or the @BASEDONDATE is between the RENEWALDATE and the JOINDATE, then allow the upgrade.

            and (UPGRADEABLELEVELS.RENEWALDATE IS NULL or (UPGRADEABLELEVELS.RENEWALDATE >= @BASEDONDATE and M.JOINDATE <= @BASEDONDATE))
  )

return @ISUPGRADEABLE

end