USP_REPORT_ACTIVITYSUMMARY

      create procedure dbo.USP_REPORT_ACTIVITYSUMMARY
        (
            @STARTDATE datetime = null, 
            @ENDDATE datetime = null,
            @REVENUETRANSACTIONQUERY uniqueidentifier = null,
            @CONSTITUENTID uniqueidentifier = null,
            @REPORTUSERID nvarchar(128) = null,
            @CURRENCYCODE tinyint = null, -- (null, 1) = Organization, 0 = Base, 2 = Transaction

            @ALTREPORTUSERID nvarchar(128) = null
        )
        with execute as owner
        as
        set nocount on;
        set transaction isolation level read uncommitted;

        declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
        declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

        declare @CURRENTAPPUSERID uniqueidentifier;
        declare @ISADMIN bit;
        declare @APPUSER_IN_NONRACROLE bit;
        declare @APPUSER_IN_NOSECGROUPROLE 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);

        declare @SQLTOEXEC nvarchar(max);

        declare @DBOBJECTNAME nvarchar(128);
        declare @DBOBJECTTYPE smallint;

        if @REVENUETRANSACTIONQUERY is not null begin
            if not exists(select ID from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
            select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
            if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
            else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
        end

        declare @ORGANIZATIONCURRENCYID as uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        declare @ORIGINCODE tinyint;
        select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

        declare @CURRENCYID uniqueidentifier;
        declare @DECIMALDIGITS tinyint;
        declare @ROUNDINGTYPECODE tinyint;
        if @CURRENCYCODE not in (0,2)
            select
                @CURRENCYID = CURRENCY.ID,
                @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
            from dbo.CURRENCY where CURRENCY.ID = @ORGANIZATIONCURRENCYID;

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

          create table #POTENTIALREVENUE
          (
              REVPARENTID uniqueidentifier,
              PAYMENTDATE datetime,
              REVENUEID uniqueidentifier,
              REVENUEAMOUNT money,
              REVENUETRANSACTIONAMOUNT money,
              TOTALREVENUESPLITORGANIZATIONAMOUNT money,
              TOTALREVENUESPLITTRANSACTIONAMOUNT money,
              TOTALREVENUESPLITAMOUNT money,
              TRANSACTIONCURRENCYID uniqueidentifier,
              BASECURRENCYID uniqueidentifier
          )

          declare @POPULATENOPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
              insert into #POTENTIALREVENUE
              (
                  REVPARENTID,
                  PAYMENTDATE,
                  REVENUEID,
                  REVENUEAMOUNT,
                  REVENUETRANSACTIONAMOUNT,
                  TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  TOTALREVENUESPLITAMOUNT,
                  TRANSACTIONCURRENCYID,
                  BASECURRENCYID
              )
              select
                  REVPARENT.ID as REVPARENTID,
                  null PAYMENTDATE,
                  null REVENUEID,
                  null as REVENUEAMOUNT,
                  null as REVENUETRANSACTIONAMOUNT,
                  null as TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  null as TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  null as TOTALREVENUESPLITAMOUNT,
                  null as TRANSACTIONCURRENCYID,
                  null as BASECURRENCYID
              from dbo.FINANCIALTRANSACTION REVPARENT ' +
              case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end + 
              '
              left outer join RECURRINGGIFTACTIVITY RGA on RGA.SOURCEREVENUEID = REVPARENT.ID  
              left outer join INSTALLMENTPAYMENT IP on IP.PLEDGEID = REVPARENT.ID  
              left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on   
              (  
                  REVENUESPLIT.ID = coalesce(RGA.PAYMENTREVENUEID,IP.PAYMENTID)  
                  and  
                  ( -- Only rev splits within date range  

                  exists(select   
                      R.ID   
                  from   
                      dbo.FINANCIALTRANSACTIONLINEITEM as RS  
                  inner join   
                      dbo.FINANCIALTRANSACTION AS R on R.ID = RS.FINANCIALTRANSACTIONID  
                  where  
                      R.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and RS.DELETEDON is null and RS.TYPECODE != 1 and RS.ID = REVENUESPLIT.ID 
                      '  + 
                      case when @STARTDATEEARLIEST is not null then ' and cast(R.DATE as datetime) >= @STARTDATEEARLIEST ' else '' end +
                      case when @ENDDATELATEST is not null then ' and cast(R.DATE as datetime) <= @ENDDATELATEST ' else '' end + 
                      '
                  )  
                  )  
              )  
              left outer join dbo.FINANCIALTRANSACTION REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
              where REVPARENT.TYPECODE in (1, 2, 3, 6, 8,15) and REVPARENT.DELETEDON is null and REVENUE.ID is null ' +
              case when @STARTDATEEARLIEST is not null then ' and (cast(REVPARENT.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
              case when @ENDDATELATEST is not null then ' and (cast(REVPARENT.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
              case when @CONSTITUENTID is not null then ' and (REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end;

          exec sp_executesql @POPULATENOPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
              @STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;

          declare @POPULATERECURRINGGIFTPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
              insert into #POTENTIALREVENUE
              (
                  REVPARENTID,
                  PAYMENTDATE,
                  REVENUEID,
                  REVENUEAMOUNT,
                  REVENUETRANSACTIONAMOUNT,
                  TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  TOTALREVENUESPLITAMOUNT,
                  TRANSACTIONCURRENCYID,
                  BASECURRENCYID
              )
              select
                  REVPARENT.ID as REVPARENTID,
                  cast(REVENUE.DATE as datetime) PAYMENTDATE,
                  REVENUE.ID as REVENUEID,
                  REVENUE.BASEAMOUNT as REVENUEAMOUNT,
                  REVENUE.TRANSACTIONAMOUNT as REVENUETRANSACTIONAMOUNT,
                  Sum(REVENUESPLIT.ORGAMOUNT) as TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  Sum(REVENUESPLIT.TRANSACTIONAMOUNT) as TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  Sum(REVENUESPLIT.BASEAMOUNT) as TOTALREVENUESPLITAMOUNT,
                  REVENUE.TRANSACTIONCURRENCYID,
                  REVENUE_EXT.NONPOSTABLEBASECURRENCYID
              from dbo.FINANCIALTRANSACTION REVENUE
              inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
              inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
              inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
              inner join RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = REVENUESPLIT.ID
              inner join FINANCIALTRANSACTION as REVPARENT with (nolock) on REVPARENT.ID = RGA.SOURCEREVENUEID ' +
              case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end + 
              '
              where REVPARENT.TYPECODE in (1, 2, 3, 6, 8, 15) and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUESPLIT.DELETEDON is null and REVPARENT.DELETEDON is null 
                  and not (REVENUESPLIT_EXT.TYPECODE = 2 and REVENUESPLIT_EXT.APPLICATIONCODE = 3) ' +
              case when @STARTDATEEARLIEST is not null then ' and (cast(REVENUE.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
              case when @ENDDATELATEST is not null then ' and (cast(REVENUE.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
              case when @CONSTITUENTID is not null then ' and (REVENUE.CONSTITUENTID = @CONSTITUENTID or REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end + 
              'group by REVENUE.ID, REVPARENT.ID, REVENUE.BASEAMOUNT, REVENUE.TRANSACTIONAMOUNT, REVENUE.DATE, REVENUE.TRANSACTIONCURRENCYID, REVENUE_EXT.NONPOSTABLEBASECURRENCYID';

          exec sp_executesql @POPULATERECURRINGGIFTPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
              @STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;

          declare @POPULATEPLEDGEPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
              insert into #POTENTIALREVENUE
              (
                  REVPARENTID,
                  PAYMENTDATE,
                  REVENUEID,
                  REVENUEAMOUNT,
                  REVENUETRANSACTIONAMOUNT,
                  TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  TOTALREVENUESPLITAMOUNT,
                  TRANSACTIONCURRENCYID,
                  BASECURRENCYID
              )
              select
                  REVPARENT.ID as REVPARENTID,
                  cast(REVENUE.DATE as datetime) PAYMENTDATE,
                  REVENUE.ID as REVENUEID,
                  REVENUE.BASEAMOUNT as REVENUEAMOUNT,
                  REVENUE.TRANSACTIONAMOUNT as REVENUETRANSACTIONAMOUNT,
                  Sum(REVENUESPLIT.ORGAMOUNT) as TOTALREVENUESPLITORGANIZATIONAMOUNT,
                  Sum(REVENUESPLIT.TRANSACTIONAMOUNT) as TOTALREVENUESPLITTRANSACTIONAMOUNT,
                  Sum(REVENUESPLIT.BASEAMOUNT) as TOTALREVENUESPLITAMOUNT,
                  REVENUE.TRANSACTIONCURRENCYID,
                  V.BASECURRENCYID
              from dbo.FINANCIALTRANSACTION REVENUE
              inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
              inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
              inner join INSTALLMENTPAYMENT IP on IP.PAYMENTID = REVENUESPLIT.ID  
              inner join FINANCIALTRANSACTION as REVPARENT with (nolock) on REVPARENT.ID = IP.PLEDGEID ' +
              case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end + 
              '
              where REVPARENT.TYPECODE in (1, 2, 3, 6, 8, 15) and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUESPLIT.DELETEDON is null and REVPARENT.DELETEDON is null ' +
              case when @STARTDATEEARLIEST is not null then ' and (cast(REVENUE.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
              case when @ENDDATELATEST is not null then ' and (cast(REVENUE.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
              case when @CONSTITUENTID is not null then ' and (REVENUE.CONSTITUENTID = @CONSTITUENTID or REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end + 
              'group by REVENUE.ID, REVPARENT.ID, REVENUE.BASEAMOUNT, REVENUE.TRANSACTIONAMOUNT, REVENUE.DATE, REVENUE.TRANSACTIONCURRENCYID, V.BASECURRENCYID';

          exec sp_executesql @POPULATEPLEDGEPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
              @STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;

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

          create table #REVPARENT
          (
              ID uniqueidentifier,
              BALANCEINCURRENCY money,
              TRANSACTIONBALANCE money,
              CURRENCYID uniqueidentifier,
              TRANSACTIONCURRENCYID uniqueidentifier,
              BASECURRENCYID uniqueidentifier,
              DATE datetime,
              TRANSACTIONTYPECODE int,
              TRANSACTIONTYPE nvarchar(100) collate database_default,
              APPEALID uniqueidentifier,
              CONSTITUENTID uniqueidentifier,
              AMOUNT money,
              TRANSACTIONAMOUNT money,
              ORGANIZATIONAMOUNT money,
              AMOUNTINCURRENCY money,
              INTERMEDIATEBALANCE money
          );

          declare @POPULATEREVPARENTTABLESQL as nvarchar(max) = '
              insert into #REVPARENT
              (
                  ID,
                  BALANCEINCURRENCY,
                  TRANSACTIONBALANCE,
                  CURRENCYID,
                  TRANSACTIONCURRENCYID,
                  BASECURRENCYID,
                  DATE,
                  TRANSACTIONTYPECODE,
                  TRANSACTIONTYPE,
                  APPEALID,
                  CONSTITUENTID,
                  AMOUNT,
                  TRANSACTIONAMOUNT,
                  ORGANIZATIONAMOUNT,
                  AMOUNTINCURRENCY,
                  INTERMEDIATEBALANCE
              )
              select 
                  REVPARENT.ID,
                  REVPARENT.BALANCEINCURRENCY,
                  REVPARENT.TRANSACTIONBALANCE,
                  REVPARENT.CURRENCYID,
                  REVPARENT.TRANSACTIONCURRENCYID,
                  REVPARENT.BASECURRENCYID,
                  REVPARENT.DATE,
                  REVPARENT.TRANSACTIONTYPECODE,
                  REVPARENT.TRANSACTIONTYPE,
                  REVPARENT.APPEALID,
                  REVPARENT.CONSTITUENTID,
                  REVPARENT.AMOUNT,
                  REVPARENT.TRANSACTIONAMOUNT,
                  REVPARENT.ORGANIZATIONAMOUNT,
                  REVPARENT.AMOUNTINCURRENCY,
                  REVPARENT.INTERMEDIATEBALANCE
              from
              #PotentialRevenue POTENTIALREVENUE
              outer apply
              dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3
              (
                  @CURRENCYID,
                  @ORGANIZATIONCURRENCYID,
                  @DECIMALDIGITS,
                  @ROUNDINGTYPECODE,
                  null,
                  @ENDDATE,
                  @ORIGINCODE,
                  @CURRENCYCODE,
                  POTENTIALREVENUE.REVPARENTID
              ) as REVPARENT
              group by REVPARENT.ID,
                  REVPARENT.BALANCEINCURRENCY,
                  REVPARENT.TRANSACTIONBALANCE,
                  REVPARENT.CURRENCYID,
                  REVPARENT.TRANSACTIONCURRENCYID,
                  REVPARENT.BASECURRENCYID,
                  REVPARENT.DATE,
                  REVPARENT.TRANSACTIONTYPECODE,
                  REVPARENT.TRANSACTIONTYPE,
                  REVPARENT.APPEALID,
                  REVPARENT.CONSTITUENTID,
                  REVPARENT.AMOUNT,
                  REVPARENT.TRANSACTIONAMOUNT,
                  REVPARENT.ORGANIZATIONAMOUNT,
                  REVPARENT.AMOUNTINCURRENCY,
                  REVPARENT.INTERMEDIATEBALANCE '

          exec sp_executesql @POPULATEREVPARENTTABLESQL, N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ENDDATE datetime, @ORIGINCODE tinyint, @CURRENCYCODE tinyint',
              @CURRENCYID=@CURRENCYID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @ENDDATE=@ENDDATE, @ORIGINCODE=@ORIGINCODE, @CURRENCYCODE=@CURRENCYCODE;

        set @SQLTOEXEC = 
            'declare @REVENUETOTALSBYCURRENCY as table
            (
                PLEDGEBALANCE money,
                RECURRINGBALANCE money,
                MATCHBALANCE money,
                GRANTAWARDBALANCE money,
                DONORCHALLENGEBALANCE money,
                INSTALLMENTBALANCE money,
                CURRENCYID uniqueidentifier,
                PLEDGECOUNT int,
                RECURRINGCOUNT int, 
                MATCHCOUNT int, 
                GRANTAWARDCOUNT int, 
                DONORCHALLENGECOUNT int,
                INSTALLMENTCOUNT int
            )' + char(13)

        set @SQLTOEXEC = @SQLTOEXEC + 
            'insert into @REVENUETOTALSBYCURRENCY(PLEDGEBALANCE, RECURRINGBALANCE, MATCHBALANCE, GRANTAWARDBALANCE, DONORCHALLENGEBALANCE, INSTALLMENTBALANCE, CURRENCYID, PLEDGECOUNT, RECURRINGCOUNT, MATCHCOUNT, GRANTAWARDCOUNT, DONORCHALLENGECOUNT, INSTALLMENTCOUNT)
            select sum(PLEDGEBALANCE) PLEDGEBALANCE, sum(RECURRINGBALANCE) RECURRINGBALANCE, sum(MATCHBALANCE) MATCHBALANCE, sum(GRANTAWARDBALANCE) GRANTAWARDBALANCE, sum(DONORCHALLENGEBALANCE) DONORCHALLENGEBALANCE, sum(INSTALLMENTBALANCE) INSTALLMENTBALANCE, CURRENCYID, sum(ISPLEDGE) PLEDGECOUNT, sum(ISRECURRING) RECURRINGCOUNT, sum(ISMATCH) MATCHCOUNT, sum(ISGRANTAWARD) GRANTAWARDCOUNT, sum(ISDONORCHALLENGE) DONORCHALLENGECOUNT, sum(ISINSTALLMENT) INSTALLMENTCOUNT
            from (
                select distinct
                    REVPARENT.ID, 
                    case when REVPARENT.TRANSACTIONTYPECODE = 1
                        then REVPARENT.BALANCEINCURRENCY
                        else 0.00 end PLEDGEBALANCE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 2
                        then dbo.UFN_RECURRINGGIFT_GETBALANCEASOFINCURRENCY(REVPARENT.ID, @ENDDATE, ' + case @CURRENCYCODE when 0 then 'REVPARENT.BASECURRENCYID' when 2 then 'REVPARENT.TRANSACTIONCURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ')
                        else 0.00 end RECURRINGBALANCE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 3
                        then REVPARENT.BALANCEINCURRENCY
                        else 0.00 end MATCHBALANCE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 6
                        then REVPARENT.BALANCEINCURRENCY
                        else 0.00 end GRANTAWARDBALANCE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 8
                        then REVPARENT.BALANCEINCURRENCY
                        else 0.00 end DONORCHALLENGEBALANCE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 15
                        then REVPARENT.BALANCEINCURRENCY
                        else 0.00 end INSTALLMENTBALANCE,
                    ' + case @CURRENCYCODE when 0 then 'REVPARENT.BASECURRENCYID' when 2 then 'REVPARENT.TRANSACTIONCURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ' CURRENCYID,
                    case when REVPARENT.TRANSACTIONTYPECODE = 1
                        then 1
                        else 0 end ISPLEDGE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 2
                        then 1
                        else 0 end ISRECURRING,
                    case when REVPARENT.TRANSACTIONTYPECODE = 3
                        then 1
                        else 0 end ISMATCH,
                    case when REVPARENT.TRANSACTIONTYPECODE = 6
                        then 1
                        else 0 end ISGRANTAWARD,
                    case when REVPARENT.TRANSACTIONTYPECODE = 8
                        then 1
                        else 0 end ISDONORCHALLENGE,
                    case when REVPARENT.TRANSACTIONTYPECODE = 15
                        then 1
                        else 0 end ISINSTALLMENT

                  from #PotentialRevenue POTENTIALREVENUE
                inner join #REVPARENT REVPARENT on POTENTIALREVENUE.REVPARENTID = REVPARENT.ID 
                left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVPARENT.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1' + nchar(13);

        if @REVENUETRANSACTIONQUERY is not null
            set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' + nchar(13);

        set @SQLTOEXEC = @SQLTOEXEC + 
            'where (not (REVPARENT.AMOUNT = 0 and REVPARENT.TRANSACTIONAMOUNT > 0))
                and (POTENTIALREVENUE.REVENUEID is null or (not (POTENTIALREVENUE.REVENUEAMOUNT = 0 and POTENTIALREVENUE.REVENUETRANSACTIONAMOUNT > 0))) '
                + case when @ISADMIN = 0 and @APPUSER_IN_NONRACROLE = 0 then 'and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVPARENT.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1) ' else '' end
                  + 'and exists
                    (
                        select HASPERMISSION
                        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                        cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''C99B9784-809E-489E-B46D-715543ECA82F'', REVSITES.SITEID)
                    )'

        set @SQLTOEXEC = @SQLTOEXEC + 
            'union  
            select distinct
                REVPARENT.ID, 
                case when REVPARENT.TRANSACTIONTYPECODE = 1
                    then REVPARENT.BALANCEINCURRENCY
                    else 0.00 end PLEDGEBALANCE,
                0.00 as RECURRINGBALANCE,
                0.00 as MATCHBALANCE,
                case when REVPARENT.TRANSACTIONTYPECODE = 6
                    then REVPARENT.BALANCEINCURRENCY
                    else 0.00 end GRANTAWARDBALANCE,
                0.00 as DONORCHALLENGEBALANCE,
                0.00 as INSTALLMENTBALANCE,
                ' + case @CURRENCYCODE when 0 then 'coalesce(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, REVPARENT.BASECURRENCYID)' when 2 then 'REVPARENT.TRANSACTIONCURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ' CURRENCYID,
                case when REVPARENT.TRANSACTIONTYPECODE = 1
                    then 1
                    else 0 end ISPLEDGE,
                0 ISRECURRING,
                0 ISMATCH,
                case when REVPARENT.TRANSACTIONTYPECODE = 6
                    then 1
                    else 0 end ISGRANTAWARD,
                0 ISDONORCHALLENGE,
                0 ISINSTALLMENT
            from dbo.REVENUE_EXT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVENUE_EXT.ID
                inner join dbo.REVENUESPLIT_EXT REVPARENTSPLIT on LI.ID = REVPARENTSPLIT.ID
                inner join DESIGNATION on REVPARENTSPLIT.DESIGNATIONID = DESIGNATION.ID
                inner join INSTALLMENT on INSTALLMENT.REVENUEID = REVENUE_EXT.ID
                inner join INSTALLMENTWRITEOFF on INSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
                inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID and WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                outer apply dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3 (@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @STARTDATE, @ENDDATE, @ORIGINCODE, @CURRENCYCODE, REVENUE_EXT.ID) as REVPARENT' + nchar(13);

        if @REVENUETRANSACTIONQUERY is not null
            set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVENUE_EXT.[ID] = SELECTION.[ID]' + nchar(13);

        set @SQLTOEXEC = @SQLTOEXEC + 
            'where LI.DELETEDON is null and LI.TYPECODE != 1 and
                REVPARENT.TRANSACTIONTYPECODE in (1, 2, 3, 6, 8,15) '
                + case when @STARTDATE is not null then 'and (cast(WRITEOFF.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end
                + case when @ENDDATE is not null then 'and (cast(WRITEOFF.DATE as datetime) <= @ENDDATELATEST) ' else '' end
                + case when @CONSTITUENTID is not null then 'and (REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end
                + case when @ISADMIN = 0 and @APPUSER_IN_NONRACROLE = 0 then 'and (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, REVPARENT.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1) ' else '' end
                + 'and exists (select 1 from dbo.REVENUESPLIT where REVENUEID = REVPARENT.ID 
                    and exists
                    (
                        select HASPERMISSION
                        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                        cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''C99B9784-809E-489E-B46D-715543ECA82F'', REVSITES.SITEID)
                    ))
                and (not (REVPARENT.AMOUNT = 0 and REVPARENT.TRANSACTIONAMOUNT > 0))
            and exists (select 1 from dbo.CONSTITUENT with (nolock) where CONSTITUENT.ID = REVPARENT.CONSTITUENTID)
            ) TOTALS
            group by CURRENCYID
            ' + '

            declare @APPUSERCURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)

            declare @PLEDGEBALANCE money;
            declare @PLEDGEISOCURRENCYCODE nvarchar(3);
            declare @PLEDGECURRENCYSYMBOL nvarchar(5);
            declare @PLEDGESYMBOLDISPLAYSETTINGCODE tinyint;
            declare @PLEDGEDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where PLEDGECOUNT > 0) = 1
                select 
                    @PLEDGEBALANCE = REVENUETOTALSBYCURRENCY.PLEDGEBALANCE,
                    @PLEDGEISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @PLEDGECURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @PLEDGESYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @PLEDGEDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where PLEDGECOUNT > 0

            declare @RECURRINGBALANCE money;
            declare @RECURRINGISOCURRENCYCODE nvarchar(3);
            declare @RECURRINGCURRENCYSYMBOL nvarchar(5);
            declare @RECURRINGSYMBOLDISPLAYSETTINGCODE tinyint;
            declare @RECURRINGDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where RECURRINGCOUNT > 0) = 1
                select 
                    @RECURRINGBALANCE = REVENUETOTALSBYCURRENCY.RECURRINGBALANCE,
                    @RECURRINGISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @RECURRINGCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @RECURRINGSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @RECURRINGDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where RECURRINGCOUNT > 0

            ' + '

            declare @MATCHBALANCE money;
            declare @MATCHISOCURRENCYCODE nvarchar(3);
            declare @MATCHCURRENCYSYMBOL nvarchar(5);
            declare @MATCHSYMBOLDISPLAYSETTINGCODE tinyint;
            declare @MATCHDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where MATCHCOUNT > 0) = 1
                select 
                    @MATCHBALANCE = REVENUETOTALSBYCURRENCY.MATCHBALANCE,
                    @MATCHISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @MATCHCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @MATCHSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @MATCHDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where MATCHCOUNT > 0

            ' + '

            declare @GRANTAWARDBALANCE money;
            declare @GRANTAWARDISOCURRENCYCODE nvarchar(3);
            declare @GRANTAWARDCURRENCYSYMBOL nvarchar(5);
            declare @GRANTAWARDSYMBOLDISPLAYSETTINGCODE tinyint;
            declare @GRANTAWARDDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where GRANTAWARDCOUNT > 0) = 1
                select 
                    @GRANTAWARDBALANCE = REVENUETOTALSBYCURRENCY.GRANTAWARDBALANCE,
                    @GRANTAWARDISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @GRANTAWARDCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @GRANTAWARDSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @GRANTAWARDDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where GRANTAWARDCOUNT > 0

            ' + '

            declare @DONORCHALLENGEBALANCE money;
            declare @DONORCHALLENGEISOCURRENCYCODE nvarchar(3);
            declare @DONORCHALLENGECURRENCYSYMBOL nvarchar(5);
            declare @DONORCHALLENGESYMBOLDISPLAYSETTINGCODE tinyint;
            declare @DONORCHALLENGEDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where DONORCHALLENGECOUNT > 0) = 1
                select 
                    @DONORCHALLENGEBALANCE = REVENUETOTALSBYCURRENCY.DONORCHALLENGEBALANCE,
                    @DONORCHALLENGEISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @DONORCHALLENGECURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @DONORCHALLENGESYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @DONORCHALLENGEDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where DONORCHALLENGECOUNT > 0

            ' + '

            declare @INSTALLMENTBALANCE money;
            declare @INSTALLMENTISOCURRENCYCODE nvarchar(3);
            declare @INSTALLMENTCURRENCYSYMBOL nvarchar(5);
            declare @INSTALLMENTSYMBOLDISPLAYSETTINGCODE tinyint;
            declare @INSTALLMENTDECIMALDIGITS tinyint;

            if (select count(*) from @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY where INSTALLMENTCOUNT > 0) = 1
                select 
                    @INSTALLMENTBALANCE = REVENUETOTALSBYCURRENCY.INSTALLMENTBALANCE,
                    @INSTALLMENTISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
                    @INSTALLMENTCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    @INSTALLMENTSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    @INSTALLMENTDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
                from 
                    @REVENUETOTALSBYCURRENCY REVENUETOTALSBYCURRENCY
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUETOTALSBYCURRENCY.CURRENCYID) CURRENCYPROPERTIES
                where INSTALLMENTCOUNT > 0

            ' + '

            select
                @PLEDGEBALANCE PLEDGEBALANCE,
                @RECURRINGBALANCE RECURRINGBALANCE,
                @MATCHBALANCE MATCHBALANCE,
                @GRANTAWARDBALANCE GRANTAWARDBALANCE,
                @DONORCHALLENGEBALANCE DONORCHALLENGEBALANCE,
                @INSTALLMENTBALANCE INSTALLMENTBALANCE,
                @PLEDGEISOCURRENCYCODE PLEDGEISOCURRENCYCODE,
                @PLEDGECURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
                @PLEDGESYMBOLDISPLAYSETTINGCODE PLEDGESYMBOLDISPLAYSETTINGCODE,
                @PLEDGEDECIMALDIGITS PLEDGEDECIMALDIGITS,
                @RECURRINGISOCURRENCYCODE RECURRINGISOCURRENCYCODE,
                @RECURRINGCURRENCYSYMBOL RECURRINGCURRENCYSYMBOL,
                @RECURRINGSYMBOLDISPLAYSETTINGCODE RECURRINGSYMBOLDISPLAYSETTINGCODE,
                @RECURRINGDECIMALDIGITS RECURRINGDECIMALDIGITS,
                @MATCHISOCURRENCYCODE MATCHISOCURRENCYCODE,
                @MATCHCURRENCYSYMBOL MATCHCURRENCYSYMBOL,
                @MATCHSYMBOLDISPLAYSETTINGCODE MATCHSYMBOLDISPLAYSETTINGCODE,
                @MATCHDECIMALDIGITS MATCHDECIMALDIGITS,
                @GRANTAWARDISOCURRENCYCODE GRANTAWARDISOCURRENCYCODE,
                @GRANTAWARDCURRENCYSYMBOL GRANTAWARDCURRENCYSYMBOL,
                @GRANTAWARDSYMBOLDISPLAYSETTINGCODE GRANTAWARDSYMBOLDISPLAYSETTINGCODE,
                @GRANTAWARDDECIMALDIGITS GRANTAWARDDECIMALDIGITS,
                @DONORCHALLENGEISOCURRENCYCODE DONORCHALLENGEISOCURRENCYCODE,
                @DONORCHALLENGECURRENCYSYMBOL DONORCHALLENGECURRENCYSYMBOL,
                @DONORCHALLENGESYMBOLDISPLAYSETTINGCODE DONORCHALLENGESYMBOLDISPLAYSETTINGCODE,
                @DONORCHALLENGEDECIMALDIGITS DONORCHALLENGEDECIMALDIGITS,
                @INSTALLMENTISOCURRENCYCODE INSTALLMENTISOCURRENCYCODE,
                @INSTALLMENTCURRENCYSYMBOL INSTALLMENTCURRENCYSYMBOL,
                @INSTALLMENTSYMBOLDISPLAYSETTINGCODE INSTALLMENTSYMBOLDISPLAYSETTINGCODE,
                @INSTALLMENTDECIMALDIGITS INSTALLMENTDECIMALDIGITS'

        exec sp_executesql @SQLTOEXEC, 
            N'@STARTDATE datetime, @ENDDATE datetime, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @ORGANIZATIONCURRENCYID as uniqueidentifier, @ORIGINCODE tinyint, @CURRENCYCODE tinyint, @CURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @STARTDATEEARLIEST datetime, @ENDDATELATEST datetime',
            @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID,
            @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @ORIGINCODE=@ORIGINCODE, @CURRENCYCODE=@CURRENCYCODE, @CURRENCYID=@CURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @STARTDATEEARLIEST=@STARTDATEEARLIEST, @ENDDATELATEST=@ENDDATELATEST;