UFN_MEMBERSHIPPROGRAM_GETTERMS

CREATE function dbo.UFN_MEMBERSHIPPROGRAM_GETTERMS
    (
        @PROGRAMID uniqueidentifier
  )
  returns table
  as return
  (
        select 
            MEMBERSHIPPROGRAMID
            ,TERMTIMELENGTH
            ,TERMLENGTHCODE
            ,ISACTIVE
            ,SEQUENCE = row_number() over (order by LOGICAL_SEQUENCE) - 1
            ,INUSE
        from (
        select distinct 
                MEMBERSHIPPROGRAMID = MP.ID
                ,TERMTIMELENGTH = MLT.TERMTIMELENGTH
                ,TERMLENGTHCODE = MLT.TERMLENGTHCODE
                ,LOGICAL_SEQUENCE = (min(MLT.SEQUENCE) over( partition by termtimelength, termlength))
                ,ISACTIVE = (max(cast(MLT.ISACTIVE as smallint)) over( partition by termtimelength, termlength))
                ,INUSE = (max(cast(dbo.UFN_MEMBERSHIPLEVELTERM_INUSE(MLT.ID) as smallint)) over ( partition by termtimelength, termlength))
        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) a
    )