USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLEVEL

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMLEVEL
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @LEVELS xml = null output,
                    @PROGRAMTYPECODE tinyint = null output,
                    @PROGRAMBASEDONCODE tinyint = null output,
                    @TERMCOUNT tinyint = null output,
                    @MULTIPLETERMS tinyint = null output
                )
                as
                    set nocount on;

                    -- be sure to set this, in case the select returns no rows
                    set @DATALOADED = 1;

                    declare @numterms int = 0
                    --select @numterms = count(*) from dbo.UFN_MEMBERSHIPPROGRAM_GETTERMS(@ID)

                    select
                        @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
                        @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE,
                        @TERMCOUNT = @numterms,
                        @MULTIPLETERMS = MP.MULTIPLETERMS
                    from
                        dbo.MEMBERSHIPPROGRAM MP
                    where MP.ID = @ID

                    declare @HIGHESTSEQUENCE int;
                    declare @LOWESTSEQUENCE int;

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

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

                    -- populate the output parameters, which correspond to fields on the form.  Note that
                    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
                    -- will display a "no data loaded" message.
                    set @LEVELS = (
                        select * from (
                            select
                                ROWNUM = row_number() over (partition by ML.ID order by ML.ID, ML.SEQUENCE),
                                ML.NAME,
                                ML.[DESCRIPTION],
                                ML.OBTAINLEVEL as HOWPEOPLEOBTAIN,
                                case when @numterms > 1 then NULL else MLT.LOWAMOUNT END AS LOWAMOUNT,
                                case when @numterms > 1 then NULL else MLT.AMOUNT END AS AMOUNT,
                                ML.MEMBERSALLOWED as MEMBERCOUNT,
                                ML.CARDSALLOWED,
                                ML.CHILDRENALLOWED,
                                TC.DESCRIPTION as TIER,
                                case when ML.FORCEMANUALDOWNGRADES = 0 then 'No' ELSE 'Yes' end as FORCEMANUALDOWNGRADES,
                                ML.SEQUENCE,
                                ML.ID,
                                ISACTIVE = case ML.ISACTIVE when 0 then 'No' when 1 then 'Yes' end,
                                MP.BASECURRENCYID,
                                case when ML.SEQUENCE = @HIGHESTSEQUENCE then 0 else 1 end as PROMOTABLE,
                                case when ML.SEQUENCE = @LOWESTSEQUENCE then 0 else 1 end as DEMOTABLE                
                            from dbo.MEMBERSHIPPROGRAM MP
                                inner join dbo.MEMBERSHIPLEVEL ML on MP.ID = ML.MEMBERSHIPPROGRAMID
                                left join dbo.MEMBERSHIPLEVELTERM MLT on ML.ID = MLT.LEVELID
                                left outer join dbo.TIERCODE TC on ML.TIERCODEID = TC.ID
                            where MP.ID = @ID
                                and ML.ISACTIVE = 1
                            ) as L
                            where rownum = 1        --using the row_number partitioned by LEVEL.ID ensures that you don't see the same level for different prices
                            order by SEQUENCE
                            for xml raw ('ITEM'), type, elements, root('LEVELS'), BINARY BASE64);

                return 0;