UFN_MEMBERSHIPPROGRAM_GETLIFETIMEPAYMENTOPTIONS

create function dbo.UFN_MEMBERSHIPPROGRAM_GETLIFETIMEPAYMENTOPTIONS(
        @PROGRAMID uniqueidentifier
  )
  returns table
  as return
  (
        select 
            MEMBERSHIPPROGRAMID
            ,LIFETIMENUMBEROFPAYMENTS
            ,LIFETIMEPAYMENTOPTIONCODE
            ,LIFETIMEPAYMENTOPTION
            ,ISACTIVE
            ,SEQUENCE = row_number() over (order by LOGICAL_SEQUENCE) - 1
            ,INUSE
        from (
        select distinct 
                MEMBERSHIPPROGRAMID = MP.ID
                ,LIFETIMENUMBEROFPAYMENTS = MLT.LIFETIMENUMBEROFPAYMENTS
                ,LIFETIMEPAYMENTOPTIONCODE = MLT.LIFETIMEPAYMENTOPTIONCODE
                ,LIFETIMEPAYMENTOPTION = MLT.LIFETIMEPAYMENTOPTION
                ,LOGICAL_SEQUENCE = (min(MLT.SEQUENCE) over( partition by LIFETIMENUMBEROFPAYMENTS, LIFETIMEPAYMENTOPTIONCODE))
                ,ISACTIVE = (max(cast(MLT.ISACTIVE as smallint)) over( partition by LIFETIMENUMBEROFPAYMENTS, LIFETIMEPAYMENTOPTIONCODE))
                ,INUSE = (max(cast(dbo.UFN_MEMBERSHIPLEVELTERM_INUSE(MLT.ID) as smallint)) over ( partition by LIFETIMENUMBEROFPAYMENTS, LIFETIMEPAYMENTOPTIONCODE))
        from
            MEMBERSHIPPROGRAM MP
            inner join MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
            inner join MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID 
            where MP.ID = @PROGRAMID and MP.PROGRAMTYPECODE = 2) a
    )