USP_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED_2

        CREATE procedure dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED_2(
        @CONSTITUENTID uniqueidentifier,
        @ISGROUP bit = 0,
        @HOUSEHOLDID uniqueidentifier = null,
        @RECOGNITIONFILTERID uniqueidentifier = null,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @SITEFILTERMODE tinyint = 0,
        @SITESSELECTED xml = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @CURRENCYCODE tinyint = 1,
        @RECOGNITIONTOTALNUMBER int = null output,
        @RECOGNITIONTOTALAMOUNT money = null output,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD money = null output,
        @RECOGNITIONTOTALYEARS int = null output,
        @RECOGNITIONCONSECUTIVEYEARS int = null output,
        @RECOGNITIONGIVENSINCEFISCALYEAR nvarchar(24) = null output,
        @TOTALRECOGNITIONWITHGIFTAID money = null output,

        @RECOGNITIONFIRSTID uniqueidentifier = null output,
        @RECOGNITIONFIRSTRECORDID uniqueidentifier = null output,
        @RECOGNITIONFIRSTDATE datetime = null output,
        @RECOGNITIONFIRSTTYPECODE tinyint = null output,
        @RECOGNITIONFIRSTTYPE nvarchar(50) = null output,
        @RECOGNITIONFIRSTAMOUNT money = null output,
        @RECOGNITIONLATESTID uniqueidentifier = null output,
        @RECOGNITIONLATESTRECORDID uniqueidentifier = null output,
        @RECOGNITIONLATESTDATE datetime = null output,
        @RECOGNITIONLATESTTYPECODE tinyint = null output,
        @RECOGNITIONLATESTTYPE nvarchar(50) = null output,
        @RECOGNITIONLATESTAMOUNT money = null output,

        @CURRENCYISOCURRENCYCODE nvarchar(3) = null output,
        @CURRENCYDECIMALDIGITS tinyint = 0 output,
        @CURRENCYSYMBOL nvarchar(5) = null output,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0 output,

        @BREAKDOWNBY tinyint = 0,
        @CAMPAIGNFILTERMODE tinyint = 0,
        @CAMPAIGNSSELECTED xml = null
    )
        with EXECUTE as OWNER      
    as
    begin
        set nocount on


        declare @CONSTITID uniqueidentifier;
        declare @DATEFROM datetime;
        declare @DATETO datetime;
        declare @ISUK bit;
        declare @CURRENCYROUNDINGTYPECODE tinyint = 0;

        /* Get currency info */
        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        declare @CURRENCYID uniqueidentifier = null;
        if @CURRENCYCODE = 1
          set @CURRENCYID = @ORGANIZATIONCURRENCYID;
        else
          set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);        

        select
          @CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
          @CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
          @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE,    
          @CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE   
        from dbo.CURRENCY where ID = @CURRENCYID;

        set @ISUK = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');

        /* Get Recognition IDs */
        if object_id('tempdb..#TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS') is not null
          drop table #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS;

        create table #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS
        (  
          ID uniqueidentifier
        );

        declare @SQL nvarchar(max) = '';
        declare @RECOGNITIONCREDITS_SQL nvarchar(max) = '';

        if @CAMPAIGNFILTERMODE != 0
        begin
          set @SQL = '
              declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
              insert into @CAMPAIGNFILTERTABLE
              select T.c.value(''(ID)[1]'',''uniqueidentifier'')
              from @CAMPAIGNSSELECTED.nodes(''/CAMPAIGNSSELECTED/ITEM'') T(c);' + char(13);
          set @RECOGNITIONCREDITS_SQL = replace(@SQL, '@CAMPAIGNFILTERTABLE', '@CAMPAIGNFILTERTABLE_RC');
        end

        select @SQL = @SQL + '
          with CONSTITS_CTE as (
            select @CONSTITUENTID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE
            union all
            select
              GROUPMEMBER.MEMBERID as CONSTITUENTID,
              case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
              case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
            from dbo.GROUPMEMBER
            left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
            where
              GROUPMEMBER.GROUPID = @CONSTITUENTID
          )
          insert into #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS(ID)
          select distinct
            REVENUERECOGNITION.ID
          from dbo.REVENUERECOGNITION ' + char(13);   

        select @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + replace(@SQL, 'REVENUERECOGNITION', 'RECOGNITIONCREDIT')

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        declare @REVENUEFILTERID uniqueidentifier
        declare @REVENUEFILTERFUNCTION nvarchar(250)
        declare @PLEDGERECOGNITIONCODE tinyint
        if @RECOGNITIONFILTERID is not null
        begin
            select 
                @REVENUEFILTERID = RECOGNITIONFILTER.REVENUEFILTERID,
                @PLEDGERECOGNITIONCODE = RECOGNITIONFILTER.PLEDGERECOGNITIONCODE,
                @REVENUEFILTERFUNCTION = REVENUEFILTER.FUNCTIONNAME
            from 
                dbo.RECOGNITIONFILTER 
                left join dbo.REVENUEFILTER on REVENUEFILTER.ID = RECOGNITIONFILTER.REVENUEFILTERID
            where
                RECOGNITIONFILTER.ID = @RECOGNITIONFILTERID

            if @REVENUEFILTERID is not null and @REVENUEFILTERFUNCTION is not null and @REVENUEFILTERFUNCTION <> ''
            begin
                select @SQL = @SQL + '
                    inner join ' + @REVENUEFILTERFUNCTION + '() FILTERED on FILTERED.ID = REVENUERECOGNITION.REVENUESPLITID ' + CHAR(13);
            end

          set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'inner join dbo.UFN_RECOGNITIONCREDIT_VALIDFILTER(@RECOGNITIONFILTERID) FILTERED on RECOGNITIONCREDIT.ID = FILTERED.ID ' + char(13);
        end

        set @SQL = @SQL + '
            inner join CONSTITS_CTE CONSTITS on CONSTITS.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID ' + char(13)

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        if @RECOGNITIONFILTERID is not null
        begin
            select @SQL = @SQL + '
            left join dbo.RECOGNITIONFILTERRECOGNTIONTYPE on RECOGNITIONFILTERRECOGNTIONTYPE.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID
                and RECOGNITIONFILTERRECOGNTIONTYPE.RECOGNITIONFILTERID = @RECOGNITIONFILTERID ' + char(13)

            if @PLEDGERECOGNITIONCODE <> 1
            begin
                select @SQL = @SQL + '
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                    left join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID
                where
                    FINANCIALTRANSACTION.DELETEDON is null and
                    FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                    FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and ' + CHAR(13)
            end
            else
            begin
                select @SQL = @SQL + '
                    where ' + CHAR(13)
            end
        end
        else
        begin
            select @SQL = @SQL + '
                where ' + CHAR(13)
        end

        select @SQL = @SQL + '
            (CONSTITS.STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= CONSTITS.STARTDATE) and
            (CONSTITS.ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= CONSTITS.ENDDATE) ' + char(13);

        set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
          '
          inner join CONSTITS_CTE CONSTITS
            on CONSTITS.CONSTITUENTID = RECOGNITIONCREDIT.CONSTITUENTID
          left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
          where
            (CONSTITS.STARTDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE >= CONSTITS.STARTDATE)
            and
            (CONSTITS.ENDDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE <= CONSTITS.ENDDATE)
            and
            ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))';

        if @CAMPAIGNFILTERMODE != 0
        begin
            set @SQL = @SQL +
                'and exists (
                    select 1
                    from dbo.REVENUESPLITCAMPAIGN
                        inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                    where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUERECOGNITION.REVENUESPLITID
                ) ' + char(13);

            set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
                'and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or exists (
                    select 1
                    from dbo.REVENUESPLITCAMPAIGN
                        inner join @CAMPAIGNFILTERTABLE_RC CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                    where REVENUESPLITCAMPAIGN.REVENUESPLITID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                ))' + char(13);
        end

        --BBNT\RyanDow 2012-04-20

        --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

        --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

        --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

        if @RECOGNITIONFILTERID is not null
        begin
            if @PLEDGERECOGNITIONCODE <> 1
            begin
                select @SQL = @SQL + '
                    and
                    (
                        -- Find commitments or payments other than pledges

                        (FINANCIALTRANSACTION.TYPECODE <> 0 or REVENUESPLIT_EXT.APPLICATIONCODE <> 2)
                        or
                        -- Find payments without pledge recognition

                        (
                            (FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 2)
                            and
                            (not exists (select top 1 RR.ID from dbo.REVENUERECOGNITION as RR inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = RR.REVENUESPLITID where RR.CONSTITUENTID in (select @CONSTITUENTID union select GM.MEMBERID from dbo.GROUPMEMBER as GM left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID where GM.GROUPID = @CONSTITUENTID) and FTLI.FINANCIALTRANSACTIONID = PLEDGE.ID))
                        )
                    ) ' + char(13)
            end

            select @SQL = @SQL + '
                and 
                (
                    RECOGNITIONFILTERRECOGNTIONTYPE.ID is not null
                    or not exists(select top 1 ID from dbo.RECOGNITIONFILTERRECOGNTIONTYPE RSUB where RSUB.RECOGNITIONFILTERID = @RECOGNITIONFILTERID)
                ) ' + char(13)
        end

        exec sp_executesql @SQL, N'@CONSTITUENTID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
        @CONSTITUENTID, @RECOGNITIONFILTERID, @STARTDATE, @ENDDATE, @CAMPAIGNSSELECTED;

        exec sp_executesql @RECOGNITIONCREDITS_SQL, N'@CONSTITUENTID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
        @CONSTITUENTID, @RECOGNITIONFILTERID, @STARTDATE, @ENDDATE, @CAMPAIGNSSELECTED;

        /*remove based on site filter*/
        delete FILTERED 
        from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
        inner join dbo.REVENUERECOGNITION on FILTERED.ID = REVENUERECOGNITION.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
        where
          not exists 
          (
            select top 1 REVSITES.SITEID
            from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
            where
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            case 
                when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                else 0 
              end = 1
            and 
            (
              @SITEFILTERMODE = 0
              or 
              exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
            )
          )

        delete FILTERED 
        from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
        inner join dbo.RECOGNITIONCREDIT on FILTERED.ID = RECOGNITIONCREDIT.ID
        where not exists (
            select top 1 RECOGNITIONCREDIT.ID 
            from dbo.RECOGNITIONCREDIT
            where RECOGNITIONCREDIT.ID = FILTERED.ID
              -- Using a case statement since the standard site extension filters

              -- resulted in a poor plan

              and case 
                when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                when exists (
                    select 1 
                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                    where SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) or (SITEID is null and dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) is null)
                  ) then 1
                else 0 
              end = 1
              and (
                @SITEFILTERMODE = 0
                  or exists(
                    select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID 
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
                    where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID)
                  )
              )
          );

        /* Get Constituent totals */
        /* This IF ELSE is a small duplication of code, but it drastically helps non-UK customers */
        /* The difference is the left join to get Gift Aid amounts in the ELSE */
        if @ISUK = 0
        begin
            select
              @RECOGNITIONTOTALNUMBER = count(RECOGNITION.ID),
              @RECOGNITIONTOTALAMOUNT = isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0),
              @TOTALRECOGNITIONWITHGIFTAID = cast(isnull(sum(RECOGNITION.GA_AMOUNTINCURRENCY), 0) as money),
              @RECOGNITIONTOTALYEARS = count(distinct(
                                                          case when @BREAKDOWNBY = 0 then YEAR(RECOGNITION.EFFECTIVEDATE) else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) end
                                                        ))
            from (
              select RR.ID, RR.AMOUNTINCURRENCY, 0 as GA_AMOUNTINCURRENCY, RR.EFFECTIVEDATE
              from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID

              union

              select RC.ID, RC.AMOUNTINCURRENCY, 0 as GA_AMOUNTINCURRENCY, RC.EFFECTIVEDATE
              from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID
            ) RECOGNITION
        end
        else
        begin
            select
              @RECOGNITIONTOTALNUMBER = count(RECOGNITION.ID),
              @RECOGNITIONTOTALAMOUNT = isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0),
              @RECOGNITIONTOTALYEARS = count(distinct(
                                                          case when @BREAKDOWNBY = 0 then YEAR(RECOGNITION.EFFECTIVEDATE) else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) end
                                                        ))
            from (
              select RR.ID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE
              from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID

              union

              select RC.ID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE
              from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
                inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID
            ) RECOGNITION

            select
              @TOTALRECOGNITIONWITHGIFTAID = cast(isnull(sum(RRWGA.AMOUNTINCURRENCY), 0) as money)
            from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            inner join dbo.UFN_REVENUERECOGNITIONWITHGIFTAID_GETAMOUNTSINCURRENCY_BULK_2(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE, @ISUK, @CONSTITUENTID) RRWGA on RRWGA.ID = FILTERED.ID
        end

        set @RECOGNITIONTOTALNUMBER = coalesce(@RECOGNITIONTOTALNUMBER, 0);
        set @RECOGNITIONTOTALAMOUNT = coalesce(@RECOGNITIONTOTALAMOUNT, 0);

        declare @YEARSQL nvarchar(max);
        declare @YEARSNIPPET nvarchar(100);

        if @BREAKDOWNBY = 0 
          set @YEARSNIPPET = 'dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(coalesce(RR.EFFECTIVEDATE, RC.EFFECTIVEDATE),0)';
        else
          set @YEARSNIPPET = 'dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(coalesce(RR.EFFECTIVEDATE, RC.EFFECTIVEDATE))';


        set @YEARSQL = 
          'select ' + @YEARSNIPPET + ' FISCALYEAR_FIRSTDAY
          from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
          left join dbo.REVENUERECOGNITION RR on FILTERED.ID = RR.ID
          left join dbo.RECOGNITIONCREDIT RC on FILTERED.ID = RC.ID
          where (RR.ID is not null) or (RC.ID is not null)
          group by ' + @YEARSNIPPET + '
          order by ' + @YEARSNIPPET + ' desc'                     


        declare @THISYEAR datetime;
        declare @LASTYEAR datetime;
        declare @CURRENTDATE datetime = getdate();

        set @THISYEAR = case when @BREAKDOWNBY = 0 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@CURRENTDATE, 0) else dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE, 0) end;
        set @LASTYEAR = case when @BREAKDOWNBY = 0 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) else dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE), 0) end;

        declare @RECOGNITIONGIVENSINCEYEAR datetime = null;

        --CONSECUTIVE YEARS

        exec dbo.USP_CONSTITUENT_GETCONSECUTIVEYEARSFROMGIVENSET
          @YEARSQL,
          @THISYEAR,
          @LASTYEAR,
          @RECOGNITIONCONSECUTIVEYEARS output,
          @RECOGNITIONGIVENSINCEYEAR output

        set @RECOGNITIONGIVENSINCEFISCALYEAR = case when @BREAKDOWNBY = 0 then cast(YEAR(@RECOGNITIONGIVENSINCEYEAR) as nvarchar(24)) else dbo.UFN_GLFISCALYEAR_GETLABEL(@RECOGNITIONGIVENSINCEYEAR) end;

        --FIRST GIFT

        --Only run conversions for the first gift.

        select top 1
          @RECOGNITIONFIRSTID = RECOGNITION.ID,
          @RECOGNITIONFIRSTRECORDID = RECOGNITION.REVENUEID,
          @RECOGNITIONFIRSTDATE = RECOGNITION.EFFECTIVEDATE,
          @RECOGNITIONFIRSTTYPECODE = RECOGNITION.TRANSACTIONTYPECODE,
          @RECOGNITIONFIRSTTYPE = RECOGNITION.TRANSACTIONTYPE
        from (
          select REVENUERECOGNITION.ID, FINANCIALTRANSACTION.ID REVENUEID, REVENUERECOGNITION.EFFECTIVEDATE, FINANCIALTRANSACTION.TYPECODE TRANSACTIONTYPECODE, FINANCIALTRANSACTION.TYPE TRANSACTIONTYPE, REVENUERECOGNITION.DATEADDED
          from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            inner join dbo.REVENUERECOGNITION on FILTERED.ID = REVENUERECOGNITION.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID

          union

          select RC.ID, null REVENUEID, RC.EFFECTIVEDATE, 8, 'Donor challenge claim - Internal sponsor', RC.DATEADDED
          from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            inner join dbo.RECOGNITIONCREDIT RC on FILTERED.ID = RC.ID
            inner join dbo.DONORCHALLENGEENCUMBERED on RC.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
        ) RECOGNITION            
        order by
          RECOGNITION.EFFECTIVEDATE asc, RECOGNITION.DATEADDED asc;

        if exists (select 1 from dbo.REVENUERECOGNITION where ID = @RECOGNITIONFIRSTID)
          set @RECOGNITIONFIRSTAMOUNT = dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(@RECOGNITIONFIRSTID, @CURRENCYID)
        else
          if exists (select 1 from dbo.RECOGNITIONCREDIT where ID = @RECOGNITIONFIRSTID)
            set @RECOGNITIONFIRSTAMOUNT = dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY(@RECOGNITIONFIRSTID, @CURRENCYID)

        --LATEST GIFT

        select top 1
          @RECOGNITIONLATESTID = RECOGNITION.ID,
          @RECOGNITIONLATESTRECORDID = RECOGNITION.REVENUEID,
          @RECOGNITIONLATESTDATE = RECOGNITION.EFFECTIVEDATE,
          @RECOGNITIONLATESTTYPECODE = RECOGNITION.TRANSACTIONTYPECODE,
          @RECOGNITIONLATESTTYPE = RECOGNITION.TRANSACTIONTYPE
        from (
          select REVENUERECOGNITION.ID, FINANCIALTRANSACTION.ID REVENUEID, REVENUERECOGNITION.EFFECTIVEDATE, FINANCIALTRANSACTION.TYPECODE TRANSACTIONTYPECODE, FINANCIALTRANSACTION.TYPE TRANSACTIONTYPE, REVENUERECOGNITION.DATEADDED
          from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            inner join dbo.REVENUERECOGNITION on FILTERED.ID = REVENUERECOGNITION.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID

          union

          select RC.ID, null REVENUEID, RC.EFFECTIVEDATE, 8, 'Donor challenge claim - Internal sponsor', RC.DATEADDED
          from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            inner join dbo.RECOGNITIONCREDIT RC on FILTERED.ID = RC.ID
            inner join dbo.DONORCHALLENGEENCUMBERED on RC.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
        ) RECOGNITION            
        order by
          RECOGNITION.EFFECTIVEDATE desc, RECOGNITION.DATEADDED desc;

        if exists (select 1 from dbo.REVENUERECOGNITION where ID = @RECOGNITIONLATESTID)
          set @RECOGNITIONLATESTAMOUNT = dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(@RECOGNITIONLATESTID, @CURRENCYID)
        else
          if exists (select 1 from dbo.RECOGNITIONCREDIT where ID = @RECOGNITIONLATESTID)
            set @RECOGNITIONLATESTAMOUNT = dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY(@RECOGNITIONLATESTID, @CURRENCYID)

        /* Get Household figures */
        if @ISGROUP = 1 or @HOUSEHOLDID is not null
        begin

          if object_id('tempdb..#TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS') is not null
            drop table #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS;


          create table #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS
          (  
            ID uniqueidentifier
          );


          if @ISGROUP = 1
          begin
            --Insert members from previous get to keep from having to get them twice

            insert into #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS(ID) 
            select FILTERED.ID
            from #TMP_DATA_RECOGNITIONSUMMARY_CONSTITRECOGNITIONIDS FILTERED
            left join dbo.REVENUERECOGNITION RR on RR.ID = FILTERED.ID
            left join dbo.RECOGNITIONCREDIT RC on RC.ID = FILTERED.ID
            where (RR.ID is not null and RR.CONSTITUENTID <> @CONSTITUENTID) or (RC.ID is not null and RC.CONSTITUENTID <> @CONSTITUENTID);
          end
          else
          begin

              if @CAMPAIGNFILTERMODE != 0
              begin
                set @SQL = '
                  declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
                  insert into @CAMPAIGNFILTERTABLE
                  select T.c.value(''(ID)[1]'',''uniqueidentifier'')
                  from @CAMPAIGNSSELECTED.nodes(''/CAMPAIGNSSELECTED/ITEM'') T(c);' + char(13);
                set @RECOGNITIONCREDITS_SQL = replace(@SQL, '@CAMPAIGNFILTERTABLE', '@CAMPAIGNFILTERTABLE_RC');
              end
              else
              begin
                set @SQL = '';
                set @RECOGNITIONCREDITS_SQL = '';
              end

              select @SQL = @SQL + '                    
                with CONSTITS_CTE as (
                  select @HOUSEHOLDID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE
                  union all
                  select
                    GROUPMEMBER.MEMBERID as CONSTITUENTID,
                    case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
                    case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
                  from dbo.GROUPMEMBER
                  left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                  where
                    GROUPMEMBER.GROUPID = @HOUSEHOLDID
                )
                insert into #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS(ID)
                select distinct
                    REVENUERECOGNITION.ID
                from dbo.REVENUERECOGNITION ' + char(13);

              select @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + replace(@SQL, 'REVENUERECOGNITION', 'RECOGNITIONCREDIT')

              --BBNT\RyanDow 2012-04-20

              --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

              --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

              --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

              if @RECOGNITIONFILTERID is not null
              begin
                    if @REVENUEFILTERID is not null and @REVENUEFILTERFUNCTION is not null and @REVENUEFILTERFUNCTION <> ''
                    begin
                        select @SQL = @SQL + '
                            inner join ' + @REVENUEFILTERFUNCTION + '() FILTERED on FILTERED.ID = REVENUERECOGNITION.REVENUESPLITID ' + CHAR(13);
                    end

                set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'inner join dbo.UFN_RECOGNITIONCREDIT_VALIDFILTER(@RECOGNITIONFILTERID) FILTERED on RECOGNITIONCREDIT.ID = FILTERED.ID ' + char(13);
              end

              set @SQL = @SQL + '
                  inner join CONSTITS_CTE CONSTITS on CONSTITS.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID ' + char(13)

              --BBNT\RyanDow 2012-04-20

              --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

              --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

              --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

              if @RECOGNITIONFILTERID is not null
              begin
                  select @SQL = @SQL + '
                    left join dbo.RECOGNITIONFILTERRECOGNTIONTYPE on RECOGNITIONFILTERRECOGNTIONTYPE.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID
                        and RECOGNITIONFILTERRECOGNTIONTYPE.RECOGNITIONFILTERID = @RECOGNITIONFILTERID ' + CHAR(13)

                if @PLEDGERECOGNITIONCODE <> 1
                  begin
                      select @SQL = @SQL + '
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
                      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                      inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                    left join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID ' + CHAR(13)
                  end
              end

              select @SQL = @SQL + '
                  where
                    (CONSTITS.STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= CONSTITS.STARTDATE)
                    and
 (CONSTITS.ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= CONSTITS.ENDDATE) ' + char(13);

              set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
                '
                  inner join CONSTITS_CTE CONSTITS
                  on CONSTITS.CONSTITUENTID = RECOGNITIONCREDIT.CONSTITUENTID
                  left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                  where
                    (CONSTITS.STARTDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE >= CONSTITS.STARTDATE)
                    and
                    (CONSTITS.ENDDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE <= CONSTITS.ENDDATE)
                    and 
                    ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))
                ';

              if @CAMPAIGNFILTERMODE != 0
              begin
                  set @SQL = @SQL +
                      'and exists (
                          select 1
                          from dbo.REVENUESPLITCAMPAIGN
                              inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                          where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUERECOGNITION.REVENUESPLITID
                      ) ' + char(13);

                  set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
                      'and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or exists (
                          select 1
                          from dbo.REVENUESPLITCAMPAIGN
                              inner join @CAMPAIGNFILTERTABLE_RC CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                          where REVENUESPLITCAMPAIGN.REVENUESPLITID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                      ))' + char(13);
              end

                --BBNT\RyanDow 2012-04-20

                --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains

                --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)

                --If this logic is changed, the other places that use revenue/recognition filters will need to be updated

                if @RECOGNITIONFILTERID is not null
                begin
                    if @PLEDGERECOGNITIONCODE <> 1
                    begin
                        select @SQL = @SQL + '
                            and
                            (
                              -- Find commitments or payments other than pledges

                              (FINANCIALTRANSACTION.TYPECODE <> 0 or REVENUESPLIT_EXT.APPLICATIONCODE <> 2)
                              or
                              -- Find payments without pledge recognition

                              (
                                  (FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 2)
                                  and
                                  (not exists (select top 1 RR.ID from dbo.REVENUERECOGNITION as RR inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = RR.REVENUESPLITID where RR.CONSTITUENTID in (select @HOUSEHOLDID union select GM.MEMBERID from dbo.GROUPMEMBER as GM left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID where GM.GROUPID = @HOUSEHOLDID) and FTLI.FINANCIALTRANSACTIONID = PLEDGE.ID))
                              )
                            ) ' + char(13)
                    end

                    select @SQL = @SQL + '
                        and 
                        (
                            RECOGNITIONFILTERRECOGNTIONTYPE.ID is not null
                            or not exists(select top 1 ID from dbo.RECOGNITIONFILTERRECOGNTIONTYPE RSUB where RSUB.RECOGNITIONFILTERID = @RECOGNITIONFILTERID)
                        ) ' + char(13)
                end

        exec sp_executesql @SQL, N'@HOUSEHOLDID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
              @HOUSEHOLDID, @RECOGNITIONFILTERID, @STARTDATE,  @ENDDATE, @CAMPAIGNSSELECTED;

              exec sp_executesql @RECOGNITIONCREDITS_SQL, N'@HOUSEHOLDID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime,  @ENDDATE datetime, @CAMPAIGNSSELECTED xml', 
              @HOUSEHOLDID, @RECOGNITIONFILTERID, @STARTDATE,  @ENDDATE, @CAMPAIGNSSELECTED;
            end

          /*remove based on site filter*/
          delete FILTERED 
          from #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS FILTERED
          inner join dbo.REVENUERECOGNITION on FILTERED.ID = REVENUERECOGNITION.ID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
          where
            not exists 
            (
              select top 1 REVSITES.SITEID
              from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
              where
              -- Using a case statement since the standard site extension filters

              -- resulted in a poor plan

              case 
                  when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                  when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                  else 0 
                end = 1
              and 
              (
                @SITEFILTERMODE = 0
                or 
                exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
              )
            )

          delete FILTERED 
          from #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS FILTERED
          inner join dbo.RECOGNITIONCREDIT on FILTERED.ID = RECOGNITIONCREDIT.ID
          where not exists (
              select top 1 RECOGNITIONCREDIT.ID 
              from dbo.RECOGNITIONCREDIT
              where RECOGNITIONCREDIT.ID = FILTERED.ID
                -- Using a case statement since the standard site extension filters

                -- resulted in a poor plan

                and case 
                  when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                  when exists (
                      select 1 
                      from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                      where SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) or (SITEID is null and dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) is null)
                    ) then 1
                  else 0 
                end = 1
                and (
                  @SITEFILTERMODE = 0
                    or exists(
                      select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID 
                      from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
                      where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID)
                    )
                )
            );

          /* Get Constituent totals */
          select
            @RECOGNITIONTOTALAMOUNT_HOUSEHOLD = isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0)
          from 
          (select RR.AMOUNTINCURRENCY
            from #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS FILTERED
            inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID

            union all

            select RC.AMOUNTINCURRENCY
            from #TMP_DATA_RECOGNITIONSUMMARY_HOUSEHOLDRECOGNITIONIDS FILTERED
            inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID              
          ) RECOGNITION
      end
    end