UFN_SALESORDER_GETPATRONUPGRADELEVELS

            create function dbo.UFN_SALESORDER_GETPATRONUPGRADELEVELS
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns table
            as
            return
            (
                select
                    MEMBERSHIP.ID as MEMBERSHIPID,
                    MEMBERSHIPPROGRAM.ID as MEMBERSHIPPROGRAMID,
                    MEMBERSHIPPROGRAM.NAME as MEMBERSHIPPROGRAMNAME,
                    MEMBERSHIPLEVEL.ID as MEMBERSHIPLEVELID,
                    MEMBERSHIPLEVEL.NAME AS MEMBERSHIPLEVELNAME,
                    MEMBERSHIPLEVELTERM.ID as MEMBERSHIPLEVELTERMID,
                    MEMBERSHIP.EXPIRATIONDATE,
                    MEMBERSHIP.LOOKUPID,
                    MEMBERSHIPLEVEL.SEQUENCE
                from dbo.MEMBER
                inner join dbo.MEMBERSHIP
                    on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID and MEMBER.ISDROPPED = 0
                inner join dbo.MEMBERSHIPPROGRAM
                    on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                inner join dbo.MEMBERSHIPLEVEL as INITIALML
                    on MEMBERSHIP.MEMBERSHIPLEVELID = INITIALML.ID
                inner join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                inner join dbo.MEMBERSHIPLEVELTERM
                    on MEMBERSHIPLEVEL.ID = MEMBERSHIPLEVELTERM.LEVELID and
                        MEMBERSHIPLEVELTERM.TERMLENGTHCODE = (select MLT.TERMLENGTHCODE from dbo.MEMBERSHIPLEVELTERM as MLT where MLT.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID) and
                        MEMBERSHIPLEVELTERM.TERMTIMELENGTH = (select MLT.TERMTIMELENGTH from dbo.MEMBERSHIPLEVELTERM as MLT where MLT.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID)
                where
                    MEMBER.CONSTITUENTID = @CONSTITUENTID and
                    MEMBERSHIPPROGRAM.ISACTIVE = 1 and
                    MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and
                    MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE = 0 and
                    MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM = 1 and
                    MEMBERSHIP.STATUSCODE = 0 and
                    MEMBERSHIP.EXPIRATIONDATE >= getdate() and
                    MEMBERSHIPLEVEL.CHILDRENALLOWED >= MEMBERSHIP.NUMBEROFCHILDREN and
                    MEMBERSHIPLEVEL.ISACTIVE = 1 and
                    MEMBERSHIPLEVELTERM.ISACTIVE = 1 and
                    --Level allows the number of members in the membership

                    (
                        (    
                            select count(1) 
                            from dbo.MEMBER 
                            where 
                                MEMBER.MEMBERSHIPID = MEMBERSHIP.ID 
                                and MEMBER.ISDROPPED = 0
                        ) <= MEMBERSHIPLEVEL.MEMBERSALLOWED
                    ) and
                    --Level allows for the number of membership cards in the membership

                    (
                        (
                            select count(1) 
                            from dbo.MEMBERSHIPCARD 
                            inner join dbo.MEMBER 
                                on MEMBERSHIPCARD.MEMBERID = MEMBER.ID 
                            where 
                                MEMBER.MEMBERSHIPID = MEMBERSHIP.ID and 
                                MEMBERSHIPCARD.STATUSCODE <> 2
                        ) <= MEMBERSHIPLEVEL.CARDSALLOWED
                    ) and
                    MEMBERSHIPLEVEL.SEQUENCE > INITIALML.SEQUENCE
            )