USP_REPORT_GROUPFUNDRAISINGANDGIVING_RECOGNITIONSUMMARY

            CREATE procedure dbo.USP_REPORT_GROUPFUNDRAISINGANDGIVING_RECOGNITIONSUMMARY
            (
                @GROUPID uniqueidentifier,
                @REPORTUSERID nvarchar(128) = null,
                @CURRENCYCODE smallint = null, --3 = My base, (null, 1) = Organization

                @ALTREPORTUSERID nvarchar(128) = null
            )
            as

            declare @CURRENTAPPUSERID uniqueidentifier
            declare @ISADMIN bit;
            declare @APPUSER_IN_NONRACROLE bit;
            declare @APPUSER_IN_NOSECGROUPROLE bit;
            declare @APPUSER_IN_NONSITEROLE bit;
            declare @APPUSER_IN_NOSITEROLE bit;

            set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
            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);
            set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
            set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

            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,
                           @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);

                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end
                else
                begin
                    select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID,
                           @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();

                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end
            end
            else
                select
                        @ORGANIZATIONCURRENCYID = CURRENCY.ID,
                        @SELECTEDCURRENCYID = CURRENCY.ID,
                        @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                        @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                    from
                        dbo.CURRENCY
                    where
                        CURRENCY.ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();


            with REVENUESOLICITOR_CTE as
            (select REVENUESOLICITOR.ID, 
                    REVENUESOLICITOR.REVENUESPLITID, 
                    REVENUESOLICITOR.CONSTITUENTID, 
                    REVENUESOLICITORBULK.AMOUNTINCURRENCY as AMOUNT,
                    REVENUE.CONSTITUENTID DONORID,
                    REVENUE.ID REVENUEID,
                    CONSTITUENT.KEYNAME
            from dbo.REVENUESOLICITOR
            inner join dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESOLICITORBULK on REVENUESOLICITOR.ID = REVENUESOLICITORBULK.ID
            inner join dbo.REVENUESPLIT
                on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
            inner join dbo.REVENUE
                on REVENUE.ID = REVENUESPLIT.REVENUEID
            inner join dbo.CONSTITUENT
                on REVENUESOLICITOR.CONSTITUENTID = CONSTITUENT.ID
            left join
                dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = REVENUE.ID
            left join
                dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
            where 
                (REVENUE.TRANSACTIONTYPECODE = 1 or --Pledge

                 REVENUE.TRANSACTIONTYPECODE = 3 or --MG Pledge

                (REVENUE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

                ((REVENUE.TRANSACTIONTYPECODE = 0 and exists(select top 1 ID from dbo.REVENUESPLIT where REVENUEID = REVENUE.ID and APPLICATIONCODE in (0, 3, 4))))) and --Payment (Gift or Recurring gift payment)

                exists(select RSSUB.ID from REVENUESOLICITOR RSSUB 
                    where RSSUB.REVENUESPLITID = REVENUESOLICITOR.REVENUESPLITID 
                        and RSSUB.CONSTITUENTID = @GROUPID)
                and
                    (
                    REVENUESOLICITOR.CONSTITUENTID = @GROUPID 
                    or
                    exists(select ID from dbo.GROUPMEMBER where GROUPMEMBER.GROUPID = @GROUPID and GROUPMEMBER.MEMBERID = REVENUESOLICITOR.CONSTITUENTID)
                    )
                and
                (@ISADMIN = 1 or 
                    (@APPUSER_IN_NONRACROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVENUESOLICITOR.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    and
                    (@APPUSER_IN_NONSITEROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, REVENUESOLICITOR.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
                )
            )
            select 'http://www.blackbaud.com/CONSTITID?CONSTITID=' + CONVERT(nvarchar(36),CONSTITUENTID) as CONSTITURL, 
                    dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTID) NAME,
                    KEYNAME,
                    SUM(AMOUNT) AMT, 
                    COUNT(distinct DONORID) DONORCOUNT, 
                    COUNT(distinct REVENUEID) GIFTCOUNT,
                    MAX(AMOUNT) LARGESTGIFT,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
            from REVENUESOLICITOR_CTE
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
            where not exists(select ID from REVENUESOLICITOR_CTE RSSUB where RSSUB.CONSTITUENTID <> @GROUPID and RSSUB.REVENUESPLITID = REVENUESOLICITOR_CTE.REVENUESPLITID)
            group by CONSTITUENTID, KEYNAME, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS
            union
            select 'http://www.blackbaud.com/CONSTITID?CONSTITID=' + CONVERT(nvarchar(36),CONSTITUENTID) as CONSTITURL, 
                    dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTID),
                    KEYNAME,
                    SUM(AMOUNT) AMT, 
                    COUNT(distinct DONORID) DONORCOUNT, 
                    COUNT(distinct REVENUEID) GIFTCOUNT,
                    MAX(AMOUNT) LARGESTGIFT,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
            from REVENUESOLICITOR_CTE
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
            where CONSTITUENTID <> @GROUPID
            group by CONSTITUENTID, KEYNAME, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS
            order by KEYNAME;