USP_DATALIST_MEMBERSHIPLEVELINFO

            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPLEVELINFO
            (
                @MEMBERSHIPLEVELID uniqueidentifier
            )
            as
                set nocount on;

                select 
                    MEMBERSHIPLEVEL.ID,
                    (
                        select DESIGNATION.ID, DESIGNATION.NAME, MEMBERSHIPLEVELDESIGNATION.[PERCENT]
                        from dbo.MEMBERSHIPLEVELDESIGNATION
                        inner join dbo.DESIGNATION on MEMBERSHIPLEVELDESIGNATION.DESIGNATIONID = DESIGNATION.ID
                        where MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                        for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELDESGINATIONS'),BINARY BASE64
                    ) as MEMBERSHIPLEVELDESIGNATIONS,
                    (
                        select 
                            MEMBERSHIPLEVELBENEFIT.BENEFITID
                            , BENEFIT.NAME
                            , MEMBERSHIPLEVELBENEFIT.UNITVALUE
                            , MEMBERSHIPLEVELBENEFIT.BASECURRENCYID
                            , MEMBERSHIPLEVELBENEFIT.QUANTITY
                            , MEMBERSHIPLEVELBENEFIT.USEPERCENT
                            , MEMBERSHIPLEVELBENEFIT.VALUEPERCENT
                            , MEMBERSHIPLEVELBENEFIT.FREQUENCYCODE
                            , MEMBERSHIPLEVELBENEFIT.DETAILS
                        from dbo.MEMBERSHIPLEVELBENEFIT
                        inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
                        where MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                        order by MEMBERSHIPLEVELBENEFIT.SEQUENCE
                        for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELBENEFITS'),BINARY BASE64
                    ) as MEMBERSHIPLEVELBENEFITS,
                    coalesce(
                        case
                            -- Entire amount

                            when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 0 then
                                case
                                    when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 then MEMBERSHIPLEVEL.RECEIPTAMOUNT
                                    when MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 2 then (select max(AMOUNT * LIFETIMENUMBEROFPAYMENTS) from dbo.MEMBERSHIPLEVELTERM where LEVELID = @MEMBERSHIPLEVELID)
                                    else (select max(AMOUNT) from dbo.MEMBERSHIPLEVELTERM where LEVELID = @MEMBERSHIPLEVELID)
                                end
                            -- Portion

                            when MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE = 1 then MEMBERSHIPLEVEL.RECEIPTAMOUNT
                            -- None

                            else 0
                        end, 0) as TAXDEDUCTIBLEAMOUNT,
                    MEMBERSHIPLEVEL.OBTAINLEVELCODE,
                    MEMBERSHIPLEVEL.CARDSALLOWED,
                    MEMBERSHIPLEVEL.MEMBERSALLOWED,
                    MEMBERSHIPLEVEL.CHILDRENALLOWED,
                    case
                                    when exists (select * from dbo.MEMBERSHIPLEVELTYPE 
                                                    inner join dbo.MEMBERSHIPLEVEL
                                                            on MEMBERSHIPLEVELTYPE.LEVELID = MEMBERSHIPLEVEL.ID
                                                    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                                                    and MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID)
                                    then 1
                                    else 0
                    end,
                    MEMBERSHIPLEVEL.NAME
                    from dbo.MEMBERSHIPLEVEL
                        inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                where MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID