USP_REPORT_APPEALPROFILE_BENEFITS

            CREATE procedure dbo.USP_REPORT_APPEALPROFILE_BENEFITS
            (
                @APPEALID uniqueidentifier = null,
                @SHOW bit = null,
                @CURRENCYCODE tinyint = null
            )
            as
                set nocount on;

                begin try
                    if @SHOW = 1
                    begin
                        -- NOTE: Be sure to update AppealProfileReportSectionsHaveData.View.xml if you modify

                        -- the joins/constraints of the below query.


                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                        declare @LEASTSIGNIFICANTAMOUNT money;

                        if coalesce(@CURRENCYCODE, 1) = 1
                        begin
                            set @CURRENCYCODE = 1;
                            set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                            --power() function returns the same number of decimal digits as the first parameter has, using a number with the maximum decimal digits a money type can have

                            select @LEASTSIGNIFICANTAMOUNT = power(10.0000,-CURRENCY.DECIMALDIGITS)
                                from dbo.CURRENCY
                                where CURRENCY.ID = @ORGANIZATIONCURRENCYID
                        end
                        else begin
                            set @CURRENCYCODE = 0;

                            select @LEASTSIGNIFICANTAMOUNT = power(10.0000,-CURRENCY.DECIMALDIGITS)
                                from dbo.APPEAL
                                    left join dbo.CURRENCY on CURRENCY.ID = APPEAL.BASECURRENCYID
                                where APPEAL.ID = @APPEALID;
                        end;

                        --this is to correctly assign levels to benefits,

                        --doing this inline does not work correctly

                        with LEVELMAP( LEVEL, AMOUNT ) as
                        (
                            select distinct
                                RANK() OVER(order by AMOUNT),
                                AMOUNT
                            from
                            dbo.APPEALBENEFIT
                            where
                                APPEALID = @APPEALID
                        )
                        select
                            APPEALBENEFIT.ID,
                            LEVELMAP.LEVEL,
                            case @CURRENCYCODE
                                when 0 then APPEALBENEFIT.AMOUNT
                                else APPEALBENEFIT.ORGANIZATIONAMOUNT
                            end as MINAMOUNT,
                            (select top (1) (
                                case @CURRENCYCODE
                                    when 0 then NEXTAPPEALBENEFITLEVEL.AMOUNT
                                    else NEXTAPPEALBENEFITLEVEL.ORGANIZATIONAMOUNT
                                end - @LEASTSIGNIFICANTAMOUNT) from DBO.APPEALBENEFIT NEXTAPPEALBENEFITLEVEL
                                        where NEXTAPPEALBENEFITLEVEL.APPEALID = @APPEALID 
                                            and NEXTAPPEALBENEFITLEVEL.AMOUNT > APPEALBENEFIT.AMOUNT
                                        order by NEXTAPPEALBENEFITLEVEL.AMOUNT asc) MAXAMOUNT,
                            BENEFIT.NAME,
                            APPEALBENEFITDETAIL.QUANTITY,
                            case @CURRENCYCODE
                                when 0 then APPEALBENEFITDETAIL.VALUE
                                else APPEALBENEFITDETAIL.ORGANIZATIONVALUE
                            end as COST,
                            BENEFIT.VALUE,
                            CURRENCYPROPERTIES.ID as CURRENCYID,
                            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            DETAILCURRENCYPROPERTIES.ID as DETAILCURRENCYID,
                            DETAILCURRENCYPROPERTIES.ISO4217 as DETAILCURRENCYISO,
                            DETAILCURRENCYPROPERTIES.DECIMALDIGITS as DETAILCURRENCYDECIMALDIGITS,
                            DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL as DETAILCURRENCYSYMBOL,
                            DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE
                        from
                            dbo.APPEALBENEFIT
                            left join dbo.APPEALBENEFITDETAIL on APPEALBENEFIT.ID = APPEALBENEFITDETAIL.APPEALBENEFITID
                            left join dbo.BENEFIT on APPEALBENEFITDETAIL.BENEFITID = BENEFIT.ID
                            left join LEVELMAP on APPEALBENEFIT.AMOUNT = LEVELMAP.AMOUNT
                            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@ORGANIZATIONCURRENCYID, APPEALBENEFIT.BASECURRENCYID)) CURRENCYPROPERTIES
                            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@ORGANIZATIONCURRENCYID, APPEALBENEFITDETAIL.BASECURRENCYID)) DETAILCURRENCYPROPERTIES
                        where
                            APPEALID = @APPEALID
                        order by
                            APPEALBENEFIT.AMOUNT;
                    end
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;