USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARYBREAKDOWN

      CREATE procedure dbo.USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARYBREAKDOWN
      (
        @CONSTITUENTID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier,
        @SITEFILTERMODE tinyint = 0,
        /*DanielCo [12/19/08] Dashboards don't support parameters that cannot 
          be cast as strings like this collection so an error will occur if
          user selects more sites than fits here (~73)*/
        @SITESSELECTED nvarchar(max) = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @CURRENCYCODE tinyint = 1,
        @DATEFILTER tinyint, 
        @RECOGNITIONFILTERID uniqueidentifier = null,
        @BREAKDOWNBY tinyint = 0,
        @CAMPAIGNFILTERMODE tinyint = 0,
        @CAMPAIGNSSELECTED nvarchar(max) = null
      ) 
      with EXECUTE as OWNER
      as
        set nocount on;

        declare
        @STARTDATE datetime,
        @ENDDATE datetime,
        @ISGROUP bit = 0,
        @HOUSEHOLDID uniqueidentifier = null,
        @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
        @CURRENCYDECIMALDIGITS tinyint = 0,
        @CURRENCYSYMBOL nvarchar(5) = null,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
        @CURRENCYROUNDINGTYPECODE tinyint = 0,
        @CONSTITID uniqueidentifier,
        @DATEFROM datetime,
        @DATETO datetime;

        exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;


        /*Get Household ID*/
        select top(1) @HOUSEHOLDID = GM.GROUPID
        from dbo.GROUPMEMBER as GM
        left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
        where GM.MEMBERID = @CONSTITUENTID
        and GD.GROUPTYPECODE = 0
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1

        /* see if this is a household */
        if @HOUSEHOLDID is null
          select
            @ISGROUP = 1
          from 
            dbo.GROUPDATA 
          where 
            GROUPDATA.ID = @CONSTITUENTID;

        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        /* Get currency info */
        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;

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


        create table #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_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_RECOGNITIONSUMMARYBREAKDOWN_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 ' + 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 pledges

                        (FINANCIALTRANSACTION.TYPECODE = 1 and REVENUESPLIT_EXT.APPLICATIONCODE = 0)
                        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 = @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_RECOGNITIONSUMMARYBREAKDOWN_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_RECOGNITIONSUMMARYBREAKDOWN_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)
                  )
              )
          );   

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


        create table #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_HOUSEHOLDRECOGNITIONIDS
        (  
          ID uniqueidentifier
        );


        if @ISGROUP = 1 or @HOUSEHOLDID is not null
        begin
          --Get the revenue to include

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

            insert into #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_HOUSEHOLDRECOGNITIONIDS(ID) 
                 select FILTERED.ID
                        from #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_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_RECOGNITIONSUMMARYBREAKDOWN_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 pledges

                           (FINANCIALTRANSACTION.TYPECODE = 1 and REVENUESPLIT_EXT.APPLICATIONCODE = 0)
                           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 = @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_RECOGNITIONSUMMARYBREAKDOWN_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_RECOGNITIONSUMMARYBREAKDOWN_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)
                    )
                )
            );
        end;

        declare @COMBINEDRECOGNITIONS table
        (
          ID uniqueidentifier,
          EFFECTIVEDATE datetime,
          AMOUNTINCURRENCY money
        )
        insert into @COMBINEDRECOGNITIONS
        select
          RR.ID,
          EFFECTIVEDATE,
          AMOUNTINCURRENCY
        from 
          #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
          inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID              

        insert into @COMBINEDRECOGNITIONS           
        select 
          RC.ID,
          EFFECTIVEDATE,
          AMOUNTINCURRENCY
        from 
          #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_CONSTITRECOGNITIONIDS FILTERED
          inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID

        --HOUSEHOLD combine table    

        declare @HOUSEHOLDRECOGNITIONS table
        (
          ID uniqueidentifier,
          EFFECTIVEDATE datetime,
          AMOUNTINCURRENCY money
        )
        insert into @HOUSEHOLDRECOGNITIONS
        select
          RR.ID,
          EFFECTIVEDATE,
          AMOUNTINCURRENCY
        from 
          #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_HOUSEHOLDRECOGNITIONIDS FILTERED
          inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RR on FILTERED.ID = RR.ID              

        insert into @HOUSEHOLDRECOGNITIONS           
        select 
          RC.ID,
          EFFECTIVEDATE,
          AMOUNTINCURRENCY
        from 
          #TMP_DATA_RECOGNITIONSUMMARYBREAKDOWN_HOUSEHOLDRECOGNITIONIDS FILTERED
          inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) RC on FILTERED.ID = RC.ID            

        select 
          coalesce(CONSTIT.YEAR, HH.YEAR) as YEAR,
          CONSTIT.TOTALNUMBER as RECOGNITIONTOTALNUMBER,
          CONSTIT.TOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
          HH.TOTALAMOUNT as RECOGNITIONTOTALAMOUNT_HOUSEHOLD,
          coalesce(CONSTIT.CURRENCYISOCURRENCYCODE, HH.CURRENCYISOCURRENCYCODE) as CURRENCYISOCURRENCYCODE,
          coalesce(CONSTIT.CURRENCYDECIMALDIGITS, HH.CURRENCYDECIMALDIGITS) as CURRENCYDECIMALDIGITS,
          coalesce(CONSTIT.CURRENCYSYMBOL, HH.CURRENCYSYMBOL) as CURRENCYSYMBOL,
          coalesce(CONSTIT.CURRENCYSYMBOLDISPLAYSETTINGCODE, HH.CURRENCYSYMBOLDISPLAYSETTINGCODE) as CURRENCYSYMBOLDISPLAYSETTINGCODE
        from 
          (
            select 
              Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) else RECOGNITION.EFFECTIVEDATE end) as YEAR,
              count(distinct RECOGNITION.ID) as TOTALNUMBER,
              isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0) as TOTALAMOUNT,
              @CURRENCYISOCURRENCYCODE CURRENCYISOCURRENCYCODE,
              @CURRENCYDECIMALDIGITS CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL CURRENCYSYMBOL,
          @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE
            from
              @COMBINEDRECOGNITIONS RECOGNITION
            group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) else RECOGNITION.EFFECTIVEDATE end)
          ) CONSTIT
          full join
          (
            select 
              Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) else RECOGNITION.EFFECTIVEDATE end) as YEAR,
              isnull(sum(RECOGNITION.AMOUNTINCURRENCY), 0) as TOTALAMOUNT,
              @CURRENCYISOCURRENCYCODE CURRENCYISOCURRENCYCODE,
              @CURRENCYDECIMALDIGITS CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL CURRENCYSYMBOL,
              @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE
            from 
              @HOUSEHOLDRECOGNITIONS RECOGNITION
            group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(RECOGNITION.EFFECTIVEDATE) else RECOGNITION.EFFECTIVEDATE end)
          ) HH
          on CONSTIT.YEAR = HH.YEAR
          order by YEAR;

        return 0;