USP_SIMPLEDATALIST_MEMBERSHIPLEVELUPGRADETERM

                CREATE procedure dbo.USP_SIMPLEDATALIST_MEMBERSHIPLEVELUPGRADETERM
                (
                    @MEMBERSHIPID uniqueidentifier,
          @MEMBERSHIPLEVELID uniqueidentifier
                )
                as

                    set nocount on;

              with NONUPGRADEABLELEVELTERMS as (
        select
            MLT2.ID
        from
          dbo.MEMBERSHIP
          inner join dbo.MEMBERSHIPLEVELTERM MLT on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MLT.ID  --my term

          inner join dbo.MEMBERSHIPLEVELTERM MLT2 on MEMBERSHIP.MEMBERSHIPLEVELID = MLT2.LEVELID and MLT.SEQUENCE >= MLT2.SEQUENCE 
                        and dbo.UFN_MEMBERSHIPLEVEL_ISUPGRADEABLE(MLT2.ID) = 1                  --possible upgrade terms for current level

        where
          MEMBERSHIP.ID = @MEMBERSHIPID

    ) --all non-upgradeable terms for current level


      select 
              MEMBERSHIPLEVELTERM.ID VALUE,
              case MEMBERSHIPPROGRAM.PROGRAMTYPECODE when 0 then cast(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.TERMLENGTH
                                                      when 1 then MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTION
                                                      else case when MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE = 0 then MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION 
                                                                  else cast(MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION + ' payments' end end as LABEL

      from dbo.MEMBERSHIPLEVEL 
          inner join MEMBERSHIPLEVELTERM on MEMBERSHIPLEVEL.ID = MEMBERSHIPLEVELTERM.LEVELID and MEMBERSHIPLEVELTERM.ISACTIVE = 1
          inner join MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
      where 
        MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID                
        and MEMBERSHIPLEVEL.ISACTIVE = 1                
        and MEMBERSHIPLEVELTERM.ID not in (select ID from NONUPGRADEABLELEVELTERMS)
    and ((MEMBERSHIPPROGRAM.PROGRAMTYPECODE != 2) or (MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 2 and MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE = 0))
      order by
          case MEMBERSHIPPROGRAM.PROGRAMTYPECODE when 0 then MEMBERSHIPLEVELTERM.TERMLENGTHCODE when 1 then MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTIONCODE else MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE end,
          case MEMBERSHIPPROGRAM.PROGRAMTYPECODE when 2 then MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS else MEMBERSHIPLEVELTERM.TERMTIMELENGTH end;