USP_REPORT_APPEALPROFILE_SOLICITORS

            CREATE procedure dbo.USP_REPORT_APPEALPROFILE_SOLICITORS
            (
                @APPEALID uniqueidentifier = null,
                @SHOW bit = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @REPORTUSERID nvarchar(128) = null,
                @CURRENCYCODE tinyint = null,
                @ALTREPORTUSERID nvarchar(128) = null
            )
            as
                set nocount on;

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;

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

                if @CURRENTAPPUSERID is null
                    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);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

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

                        -- the joins/constraints of the below query.


                        -- Determine the sum of the solicitor goals here since each solicitor only contributes

                        -- once, no matter how many teams they are on.

                        declare @SOLICITORGOALSUM as money
                        select @SOLICITORGOALSUM = sum(
                            case @CURRENCYCODE
                                when 0 then TEAMFUNDRAISER.GOAL
                                else TEAMFUNDRAISER.ORGANIZATIONGOAL
                            end )
                        from dbo.TEAMFUNDRAISER where APPEALID = @APPEALID

                        select
                            '' as [CONSTITUENTID],
                            TEAMFUNDRAISINGTEAM.ID,
                            case 
                                when count(CONSTITUENT.ID) > 0 then TEAMFUNDRAISINGTEAM.NAME + ' (' + dbo.UDA_BUILDLIST(CONSTITUENT.NAME) + ')'
                                else TEAMFUNDRAISINGTEAM.NAME
                            end as NAME,                            
                            TEAMFUNDRAISINGTEAM.PARENTTEAMID,
                            0 as [TYPE],
                            case @CURRENCYCODE
                                when 0 then TEAMFUNDRAISINGTEAM.GOAL
                                else TEAMFUNDRAISINGTEAM.ORGANIZATIONGOAL
                            end as TEAMGOAL,
                            0 as SOLICITORGOAL,
                            @SOLICITORGOALSUM,
                            CURRENCYPROPERTIES.ID as CURRENCYID,
                            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                        from dbo.TEAMFUNDRAISINGTEAM
                            left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                                on TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
                            left join dbo.CONSTITUENT
                                on CONSTITUENT.ID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
                                    and (@ISADMIN = 1 or 
                                            @APPUSER_IN_NONRACROLE = 1 or
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
                                        )
                            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@ORGANIZATIONCURRENCYID, TEAMFUNDRAISINGTEAM.BASECURRENCYID)) CURRENCYPROPERTIES
                        where TEAMFUNDRAISINGTEAM.APPEALID = @APPEALID
                        group by
                            TEAMFUNDRAISINGTEAM.ID,
                            TEAMFUNDRAISINGTEAM.NAME,
                            TEAMFUNDRAISINGTEAM.PARENTTEAMID,
                            TEAMFUNDRAISINGTEAM.GOAL,
                            TEAMFUNDRAISINGTEAM.ORGANIZATIONGOAL,
                            CURRENCYPROPERTIES.ID,
                            CURRENCYPROPERTIES.ISO4217,
                            CURRENCYPROPERTIES.DECIMALDIGITS,
                            CURRENCYPROPERTIES.CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE

                        union all

                        select 
                            -- Use the TEAMFUNDRAISINGTEAMMEMBER if it's set.  We try to 

                            -- use this since the report groups based on the ID and if 

                            -- TEAMFUNDRAISER.ID were always used and the same constituent

                            -- was a member of a different team, they wouldn't show up twice.

                            'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36),CONSTITUENT.ID) as [CONSTITUENTID],
                            case 
                                when TEAMFUNDRAISINGTEAMMEMBER.ID is not null then TEAMFUNDRAISINGTEAMMEMBER.ID
                                else TEAMFUNDRAISER.ID
                            end 'ID',
                            CONSTITUENT.NAME,
                            TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID as PARENTTEAMID,
                            1 as [TYPE],
                            0 as TEAMGOAL,
                            case @CURRENCYCODE
                                when 0 then TEAMFUNDRAISER.GOAL
                                else TEAMFUNDRAISER.ORGANIZATIONGOAL
                            end as SOLICITORGOAL,
                            @SOLICITORGOALSUM,
                            CURRENCYPROPERTIES.ID as CURRENCYID,
                            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                        from dbo.TEAMFUNDRAISER
                            inner join dbo.CONSTITUENT
                                on TEAMFUNDRAISER.CONSTITUENTID = CONSTITUENT.ID
                            left join TEAMFUNDRAISINGTEAMMEMBER
                                on TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
                            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@ORGANIZATIONCURRENCYID, TEAMFUNDRAISER.BASECURRENCYID)) CURRENCYPROPERTIES
                        where TEAMFUNDRAISER.APPEALID = @APPEALID
                            and (@ISADMIN = 1 or 
                                @APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)


                        order by TYPE, NAME
                    end
                end try

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

                return 0;