USP_SIMPLEDATALIST_SALESORDER_PATRONMEMBERSHIPSAVAILABLEFORUPGRADE

CREATE procedure dbo.USP_SIMPLEDATALIST_SALESORDER_PATRONMEMBERSHIPSAVAILABLEFORUPGRADE
(
    @SALESORDERID uniqueidentifier
)
as

    declare @CONSTITUENTID uniqueidentifier;

    select
        @CONSTITUENTID = CONSTITUENTID
    from
        dbo.SALESORDER
    where
        ID = @SALESORDERID

    select distinct
        MEMBERSHIP.ID as VALUE,
        dbo.UFN_MEMBERSHIP_GETDESCRIPTION(MEMBER.MEMBERSHIPID) as LABEL
    from dbo.MEMBERSHIPPROGRAM
    inner join dbo.MEMBERSHIPLEVEL 
          on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
    inner join dbo.MEMBERSHIPLEVELTERM 
          on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID      
    inner join dbo.MEMBERSHIP
          on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
    inner join dbo.MEMBER
          on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID 
    inner join dbo.MEMBERSHIPLEVEL CURRENTLEVEL
          on CURRENTLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
    inner join dbo.MEMBERSHIPLEVELTERM CURRENTLEVELTERM
          on CURRENTLEVELTERM.ID = MEMBERSHIP.MEMBERSHIPLEVELTERMID 
    where 
        MEMBER.CONSTITUENTID = @CONSTITUENTID and
        MEMBERSHIPLEVELTERM.TERMLENGTHCODE = CURRENTLEVELTERM.TERMLENGTHCODE and 
        MEMBERSHIPLEVELTERM.TERMTIMELENGTH = CURRENTLEVELTERM.TERMTIMELENGTH and
        MEMBERSHIPLEVEL.SEQUENCE > CURRENTLEVEL.SEQUENCE and
        MEMBERSHIPLEVEL.MEMBERSALLOWED >= CURRENTLEVEL.MEMBERSALLOWED  and 
        MEMBERSHIPLEVEL.ISACTIVE = 1 and
        MEMBERSHIP.STATUSCODE = 0 and
        MEMBER.ISDROPPED = 0 and
        MEMBERSHIP.EXPIRATIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()) and
        MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and
        MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE = 0 and
        MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM = 1