USP_DATALIST_MEMBERSHIPPROGRAM_REVENUEBYLEVEL

            CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAM_REVENUEBYLEVEL
            (
                @PROGRAMID uniqueidentifier,
                @BREAKDOWNTYPE tinyint = null,
                @FROMDATE datetime = null,
                @TODATE datetime = null,
                @MEMBERSHIPPROGRAMID uniqueidentifier = null output
            )
            as
                set nocount on;

                set @MEMBERSHIPPROGRAMID = @PROGRAMID

                set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
                set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @SELECTEDCURRENCYID uniqueidentifier;

                set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;


                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;
                declare @CURRENCYISO nvarchar(6);
                declare @CURRENCYSYMBOL nvarchar(10);
                declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

                select
                    @CURRENCYISO = CURRENCY.ISO4217,
                    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                    @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
                    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
                from
                    dbo.CURRENCY
                where
                    CURRENCY.ID = @SELECTEDCURRENCYID;

                if @BREAKDOWNTYPE = 0
                begin

                    declare @MEMBERSHIPLEVELREVENUE table(TOTAL money, LEVELID uniqueidentifier);

                    insert into @MEMBERSHIPLEVELREVENUE
                    select
                        sum(TOTAL),
                        LEVELID
                    from (
                        select
                            REVENUESPLITS.AMOUNTINCURRENCY - coalesce(REFUND.CREDITTOTAL, 0) as TOTAL,
                            MEMBERSHIPSPLITS.LEVELID as LEVELID
                        from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                            @SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE
                        ) as REVENUESPLITS
                        left join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as REFUND 
                            on REFUND.SOURCELINEITEMID = REVENUESPLITS.ID
                        inner join dbo.UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM(@PROGRAMID, @FROMDATE, @TODATE) MEMBERSHIPSPLITS
                            on MEMBERSHIPSPLITS.REVENUESPLITID = REVENUESPLITS.ID
                        where 
                            REVENUESPLITS.TYPECODE in (2,18)
                        ) as RAWMEMBERSHIPREVENUE
                        group by LEVELID

                    select top(6)
                        TOTAL,
                        MEMBERSHIPLEVEL.NAME as LEVEL
                    from @MEMBERSHIPLEVELREVENUE LEVELREVENUE
                    inner join dbo.MEMBERSHIPLEVEL
                        on LEVELREVENUE.LEVELID = MEMBERSHIPLEVEL.ID
                    order by TOTAL

                end

                else
                begin

                    declare @MEMBERSHIPTIERREVENUE table(TOTAL money, TIERID uniqueidentifier);

                    insert into @MEMBERSHIPTIERREVENUE
                    select
                        sum(TOTAL),
                        TIERID
                    from (  
                        select
                            REVENUESPLITS.AMOUNTINCURRENCY - coalesce(REFUND.CREDITTOTAL, 0) as TOTAL,
                            MEMBERSHIPSPLITS.TIERCODEID as TIERID
                        from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                            @SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE
                        ) as REVENUESPLITS
                        left join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as REFUND 
                            on REFUND.SOURCELINEITEMID = REVENUESPLITS.ID
                        inner join dbo.UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM(@PROGRAMID, @FROMDATE, @TODATE) MEMBERSHIPSPLITS
                            on MEMBERSHIPSPLITS.REVENUESPLITID = REVENUESPLITS.ID
                        where 
                            REVENUESPLITS.TYPECODE in (2,18)                                  
                        ) as RAWMEMBERSHIPREVENUE
                        group by TIERID

                    select top(6)
                        TOTAL,
                        coalesce(TIERCODE.[DESCRIPTION], 'No reporting group') as LEVEL
                    from @MEMBERSHIPTIERREVENUE TIERREVENUE
                    left join dbo.TIERCODE
                        on TIERREVENUE.TIERID = TIERCODE.ID
                    order by TOTAL

                end