UFN_MEMBERSHIPLEVEL_ISUPGRADEABLE

      CREATE function dbo.UFN_MEMBERSHIPLEVEL_ISUPGRADEABLE(@MEMBERSHIPLEVELTERMID uniqueidentifier)
      returns bit
      as begin
      declare @ISUPGRADEABLE bit = 0

      select 
          @ISUPGRADEABLE = case when (ML.ID IS NULL) or (MLT.ID IS NULL and UML.ID IS NULL) then 0 else 1 end
      from 
          MEMBERSHIPLEVELTERM MYTERM
          inner join MEMBERSHIPLEVEL ML on ML.ID = MYTERM.LEVELID
          left join MEMBERSHIPLEVELTERM MLT on MLT.ID = MYTERM.ID and dbo.UFN_MEMBERSHIPLEVELTERM_ISUPGRADEABLE(MLT.ID) = 1 --any upgradeable terms at current level

          left join (select ML2.* from MEMBERSHIPLEVEL ML2                                                                  --any upgradeable levels above current level  

                             inner join MEMBERSHIPLEVELTERM MLT2 on ML2.ID = MLT2.LEVELID and MLT2.ISACTIVE = 1
                   where ML2.ISACTIVE = 1) UML
                       on ML.MEMBERSHIPPROGRAMID = UML.MEMBERSHIPPROGRAMID and ML.SEQUENCE < UML.SEQUENCE

      where
          MYTERM.ID = @MEMBERSHIPLEVELTERMID

      return @ISUPGRADEABLE;
      end