USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVEMEMBERSHIPS

            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_ACTIVEMEMBERSHIPS
            (
                @PROGRAMID uniqueidentifier,
                @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                @STATUSCODE tinyint = null,
                @MEMBERSHIPLEVELID uniqueidentifier = null,
                @TIERCODEID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @CURRENTDATE date = getdate();

                set @MEMBERSHIPPROGRAMID = @PROGRAMID;

                --100 For backward compatibility.  This datalist was defined before Lapsed was an official status.

                if @STATUSCODE = 100
                    set @STATUSCODE = 4

                select
                    MEMBERSHIP.ID,
                    MEMBERSHIP.STATUS,
                    MEMBER.ID,
                    CONSTITUENT.NAME,
                    MEMBERSHIPLEVEL.NAME as [LEVEL],
                    TIERCODE.DESCRIPTION,
                    cast(MEMBERSHIP.EXPIRATIONDATE as date) EXPIRATIONDATE,
                    MEMBERSHIP.ISGIFT,
                    MEMBERSHIP.GIVENBYID,
                    case MEMBERSHIP.ISGIFT
                        when 1 then (select NAME from dbo.CONSTITUENT where ID = MEMBERSHIP.GIVENBYID)
                        else null
                    end
                from dbo.MEMBERSHIP
                inner join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                left join dbo.TIERCODE
                    on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
                inner join dbo.MEMBER
                    on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                inner join (
                    select
                        case
                            when C.ISORGANIZATION = 1 then 
                                case when len(C.KEYNAMEPREFIX) = 0 
                                    then C.KEYNAME 
                                    else C.KEYNAME + ', ' + C.KEYNAMEPREFIX 
                                end
                            else dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null)
                        end as NAME
                        , C.ID
                    from dbo.CONSTITUENT C
                ) as CONSTITUENT 
                    on MEMBER.CONSTITUENTID = CONSTITUENT.ID
                where MEMBER.ISPRIMARY = 1
                    and MEMBER.ISDROPPED = 0
                    and MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
                    and (@MEMBERSHIPLEVELID is null or MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID)
                    and (@TIERCODEID is null or MEMBERSHIPLEVEL.TIERCODEID = @TIERCODEID)
                    and (MEMBERSHIP.STATUSCODE = @STATUSCODE or @STATUSCODE is null)
                order by
                    CONSTITUENT.NAME