USP_REPORT_TAXCLAIMBREAKDOWN_SUMMARY

            CREATE procedure dbo.USP_REPORT_TAXCLAIMBREAKDOWN_SUMMARY
            (
                @SELECTIONID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @REPORTUSERID nvarchar(128) = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED nvarchar(max) = null,
                @CURRENCYCODE smallint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            as
                set nocount on

                declare @CURRENTAPPUSERID uniqueidentifier
                set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

                declare @ISADMIN bit
                declare @APPUSER_IN_NONRACROLE bit
                declare @APPUSER_IN_NOSECGROUPROLE bit

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)

                if @ISADMIN = 0
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID)

                if @ISADMIN = 0 and @APPUSER_IN_NOSECGROUPROLE = 0
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID)

                declare @STARTDATEEARLIESTTIME datetime
                if @STARTDATE is not null
                    set @STARTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)

                declare @ENDDATELATESTTIME datetime
                if @ENDDATE is not null
                    set @ENDDATELATESTTIME = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)

                declare @SITESSELECTEDXML xml
                set @SITESSELECTEDXML = dbo.UFN_REPORT_TAXCLAIMBREAKDOWN_GETSITESSELECTEDXML(@SITESSELECTED)


                declare @SELECTEDCURRENCYID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                if @CURRENCYCODE = 3
                begin
                    if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
                    begin                    
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID,
                                @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
                                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                            from dbo.CURRENCYSET
                            inner join dbo.CURRENCY on CURRENCYSET.BASECURRENCYID = CURRENCY.ID
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
                    end
                    else
                    begin
                        select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID,
                                @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
                                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                            from dbo.CURRENCYSET
                            inner join dbo.CURRENCY on CURRENCYSET.BASECURRENCYID = CURRENCY.ID
                            where
                                CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
                    end
                end
                else
                begin
                    select @SELECTEDCURRENCYID = CURRENCY.ID,
                            @ORGANIZATIONCURRENCYID = CURRENCY.ID,
                            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                        from
                            dbo.CURRENCY
                        where
                            CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end

                select
                    REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
                    count(*) as TAXCLAIMCOUNT,
                    sum(REVENUESPLIT.AMOUNTINCURRENCY) + sum(TAXCLAIMAMOUNTBULK.TAXCLAIMAMOUNTINCURRENCY) as GROSSAMOUNT, 
                    sum(TAXCLAIMAMOUNTBULK.TAXCLAIMAMOUNTINCURRENCY) as TAXCLAIMAMOUNT,
                    sum(REVENUESPLIT.AMOUNTINCURRENCY) as SPLITAMOUNT,
                    DESIGNATION.NAME as DESIGNATION,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) as REVENUESPLITGIFTAID
                inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
                inner join dbo.UFN_GIFTAID_GETTAXCLAIMAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) TAXCLAIMAMOUNTBULK on TAXCLAIMAMOUNTBULK.ID = REVENUESPLITGIFTAID.ID
                inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
                -- Using a left join instead of the in operator since the function was being run for every row

                left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUESPLIT.ID = SELECTION.ID
                where
                    (@STARTDATEEARLIESTTIME is null or REVENUE.DATE >= @STARTDATEEARLIESTTIME) and
                    (@ENDDATELATESTTIME is null or REVENUE.DATE <= @ENDDATELATESTTIME) and
                    (@SELECTIONID is null or SELECTION.ID is not null) and
                    (@SITEFILTERMODE = 0
                        or exists
                        (
                            select 1
                            from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESPLITSITE
                            inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTEDXML) SITEFILTER on REVENUESPLITSITE.SITEID = SITEFILTER.SITEID
                        )
                    ) and

                    -- Record security filter

                    (@ISADMIN = 1 or 
                        @APPUSER_IN_NONRACROLE = 1 or
                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUE.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)

                    -- Site security filter

                    and exists
                    (
                        select HASPERMISSION
                        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                        cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '05da06a7-9727-48a4-af23-0494972f73cd', REVSITES.SITEID)
                    )                    
                group by REVENUESPLITGIFTAID.TAXCLAIMNUMBER, 
                         DESIGNATION.ID, 
                         DESIGNATION.NAME,
                         CURRENCYPROPERTIES.ISO4217, 
                         CURRENCYPROPERTIES.CURRENCYSYMBOL,
                         CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                         CURRENCYPROPERTIES.DECIMALDIGITS
                order by REVENUESPLITGIFTAID.TAXCLAIMNUMBER, DESIGNATION.NAME