USP_DATALIST_MEMBERSHIPLEVEL

Displays the list of membership levels for a program.
Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPPROGRAMID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDEINACTIVE	bit	IN	Include inactive levels
@INCLUDEOFFLINEMICROSITE	bit	IN	Include level not online via web forms
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPLEVEL
                    (
                        @MEMBERSHIPPROGRAMID uniqueidentifier,
                        @INCLUDEINACTIVE bit = 0,
                        @INCLUDEOFFLINEMICROSITE bit = 1
                    )
                as
                    set nocount on;

                    declare @HIGHESTSEQUENCE int;
                    declare @LOWESTSEQUENCE int;

                    select 
                        @HIGHESTSEQUENCE = max(SEQUENCE)
                    from 
                        dbo.MEMBERSHIPLEVEL
                    where 
                        MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                        and (MEMBERSHIPLEVEL.ISACTIVE = 1 or @INCLUDEINACTIVE = 1)

                    select
                        @LOWESTSEQUENCE = min(SEQUENCE)
                    from 
                        dbo.MEMBERSHIPLEVEL
                    where 
                        MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                        and (MEMBERSHIPLEVEL.ISACTIVE = 1 or @INCLUDEINACTIVE = 1)

                    select 
                        ML.ID,
                        ML.NAME,
                        ML.DESCRIPTION,
                        TIERCODE.DESCRIPTION AS TIER,
                        ML.MEMBERSALLOWED,
                        ML.CHILDRENALLOWED,
                        ML.CARDSALLOWED,
                        ML.ISACTIVE,
                        case when ML.SEQUENCE = @HIGHESTSEQUENCE then 0 else 1 end as CANPROMOTE,
                        case when ML.SEQUENCE = @LOWESTSEQUENCE then 0 else 1 end as CANDEMOTE
                     from 
                        dbo.MEMBERSHIPLEVEL ML
                        left join dbo.TIERCODE on ML.TIERCODEID = TIERCODE.ID
                        left join dbo.MICROSITEMEMBERSHIPLEVEL on ML.ID = MICROSITEMEMBERSHIPLEVEL.ID
                    where 
                        MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
                        (ML.ISACTIVE = 1 or @INCLUDEINACTIVE = 1) and
                        (@INCLUDEOFFLINEMICROSITE = 1 or MICROSITEMEMBERSHIPLEVEL.ID is not null)
                    order by ML.SEQUENCE asc