USP_DATALIST_MEMBERSHIPLEVELTERMAMOUNT

            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPLEVELTERMAMOUNT
            (
                @MEMBERSHIPPROGRAMID uniqueidentifier = null,
        @INCLUDEINACTIVETERMS bit = 0
            )
            as
                set nocount on;

                select
                    MEMBERSHIPLEVELTERM.ID,
                    case
                        when TERMCODE = 0 then 1
                        when TERMCODE = 1 then 2
                        when TERMCODE = 2 then 3
                        when TERMCODE = 3 then 4
                        when TERMCODE = 4 then 5
                        when TERMCODE = 5 then 10
                        when TERMCODE = 6 then 255 -- lifetime

                        else 0
                    end AS TERM,
                    case MEMBERSHIPLEVEL.OBTAINLEVELCODE
                        when 0 then MEMBERSHIPLEVELTERM.AMOUNT
                        else MEMBERSHIPLEVELTERM.LOWAMOUNT
                    end as AMOUNT,
                    MEMBERSHIPLEVELTERM.LEVELID,
                    MEMBERSHIPLEVELTERM.BASECURRENCYID,
                    MEMBERSHIPLEVELTERM.TERMTIMELENGTH,
                    MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
                    MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE as PAYMENTOPTION,
                    case
                        -- Recurring/Sustaining

                        when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 1 then
                            case MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTIONCODE
                                when 0 then 1 -- Annually

                                when 1 then 2 -- Semi-annually

                                when 2 then 4 -- Quarterly

                                else 12       -- Monthly

                            end
                        -- Lifetime

                        when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 2 then MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS
                        else 0
                    end as LIFETIMENUMBEROFPAYMENTS
                from dbo.MEMBERSHIPLEVELTERM
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID 
          and ((MEMBERSHIPLEVELTERM.ISACTIVE = 1) or (@INCLUDEINACTIVETERMS = 1))
                order by MEMBERSHIPLEVELTERM.AMOUNT desc, MEMBERSHIPLEVEL.SEQUENCE desc