USP_DATALIST_CONSTITUENT_REVENUEHISTORY_2

CREATE procedure dbo.USP_DATALIST_CONSTITUENT_REVENUEHISTORY_2
(
    @CONSTITUENTID uniqueidentifier,
    @GROUPBY tinyint = 0,  -- 0=Commitment, 1=Transaction

    @TRANSACTIONTYPEOPTIONCODE smallint = -1,  -- -1=All, otherwise revenue.transactiontypecode

    @REVENUETYPEOPTIONCODE smallint = -1,  -- -1=All, otherwise revenuesplit.revenuetypecode

    @DATERANGECODE smallint = 4,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @INCLUDEGROUPMEMBERREVENUE bit = 0,
    @REVENUEFILTERID uniqueidentifier = null,
    @CURRENCYCODE tinyint = 2
)
as

    set nocount on;  

    declare @CURRENTDATE date;  
    set @CURRENTDATE = getdate();  

    declare @STARTDATE date;  
    declare @ENDDATE date;  

    declare @USERISADMIN bit;
    set @USERISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

    --Gift Aid is for UK only

    declare @PRODUCTISUK bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D')

  declare @MULTICURRENCYENABLED bit;
  set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
  if @MULTICURRENCYENABLED = 0 
    set @CURRENCYCODE = 1

    /*  
        0 = All  
        1 = last 30 days  
        2 = this month  
        3 = last month  
        4 = last 90 days  
        5 = this quarter  
        6 = last quarter  
        7 = last 12 months  
        8 = this year  
        9 = last year  
    */  

    select 
        @STARTDATE = case @DATERANGECODE  
            when 1 then dateadd(day,-30,@CURRENTDATE)  
            when 2 then dateadd(month,datediff(month,0,@CURRENTDATE),0)  
            when 3 then dateadd(month,datediff(month,0,@CURRENTDATE)-1,0)  
            when 4 then dateadd(day,-90,@CURRENTDATE)  
            when 5 then dateadd(month,datediff(month,0,@CURRENTDATE)-((datepart(month,@CURRENTDATE)-1)%3),0)  
            when 6 then dateadd(month,datediff(month,0,@CURRENTDATE)-((datepart(month,@CURRENTDATE)-1)%3)-3,0)  
            when 7 then dateadd(month,-12,@CURRENTDATE)  
            when 8 then dateadd(year,datediff(year,0,@CURRENTDATE),0)  
            when 9 then dateadd(year,datediff(year,0,@CURRENTDATE)-1,0)  
        end,  
        @ENDDATE = case @DATERANGECODE  
            when 1 then @CURRENTDATE  
            when 2 then dateadd(month,datediff(month,0,@CURRENTDATE)+1,0)-1  
            when 3 then dateadd(month,datediff(month,0,@CURRENTDATE),0)-1  
            when 4 then @CURRENTDATE  
            when 5 then dateadd(month,datediff(month,0,@CURRENTDATE)-((datepart(month,@CURRENTDATE)-1)%3)+3,0)-1  
            when 6 then dateadd(month,datediff(month,0,@CURRENTDATE)-((datepart(month,@CURRENTDATE)-1)%3),0)-1  
            when 7 then @CURRENTDATE  
            when 8 then dateadd(year,datediff(year,0,@CURRENTDATE)+1,0)-1  
            when 9 then dateadd(year,datediff(year,0,@CURRENTDATE),0)-1  
        end;  

    declare @CURRENCYID uniqueidentifier
    if @CURRENCYCODE = 1
      set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

    if @CURRENCYCODE = 3
    begin
      set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
      if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
        set @CURRENCYCODE = 1
    end

    declare @REVENUETOINCLUDE table  
    (
        ID uniqueidentifier,  
        CONSTITUENTID uniqueidentifier,  
        AMOUNT money,  
        TRANSACTIONTYPE nvarchar(50),  
        TRANSACTIONTYPECODE tinyint,  
        DATE datetime,  
        DATEADDED datetime,  
        SPLITCOUNT integer,  
        CONSTITUENT nvarchar(200),
        TRANSACTIONCURRENCYID uniqueidentifier,
        BASECURRENCYID uniqueidentifier,
        TRANSACTIONAMOUNT money,
        ORGANIZATIONAMOUNT money
    );


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


    create table #TMP_DATA_REVENUEHISTORY_REVENUEIDS
    (  
        ID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        DATEFROM datetime,
        DATETO datetime  
    );

    insert into #TMP_DATA_REVENUEHISTORY_REVENUEIDS  
    (
        ID,
        CONSTITUENTID,
        DATEFROM,
        DATETO
    )
    select
        [VALIDREVENUE].REVENUEID,
        CONSTITUENTSTOINCLUDE.CONSTITUENTID,
        CONSTITUENTSTOINCLUDE.DATEFROM,
        CONSTITUENTSTOINCLUDE.DATETO
    from
    (
        select  
            @CONSTITUENTID as CONSTITUENTID,
            null as DATEFROM,
            null as DATETO

        union all

        select
            GROUPMEMBER.MEMBERID as CONSTITUENTID,
            GROUPMEMBERDATERANGE.DATEFROM,
            GROUPMEMBERDATERANGE.DATETO
        from dbo.GROUPMEMBER
        left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
        where
            @INCLUDEGROUPMEMBERREVENUE = 1 and
            GROUPMEMBER.GROUPID = @CONSTITUENTID
    ) as CONSTITUENTSTOINCLUDE
    cross apply dbo.UFN_REVENUE_REVENUESVALIDFORFILTER(@REVENUEFILTERID, CONSTITUENTSTOINCLUDE.CONSTITUENTID, null, null) as [VALIDREVENUE]  

    -- Run the following query as dynamic SQL to avoid performance hits from site checks when we don't need them.

    declare @SQL nvarchar(max);

    set @SQL = N'
    select  
        REVENUE.ID,
        [VALIDREVENUE].CONSTITUENTID,
        REVENUE.AMOUNT,
        REVENUE.TRANSACTIONTYPE,
        REVENUE.TRANSACTIONTYPECODE,
        REVENUE.DATE,
        REVENUE.DATEADDED,
        (select count(*) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = REVENUE.ID) SPLITCOUNT,  
        case when @INCLUDEGROUPMEMBERREVENUE = 1 then (select NAME from dbo.CONSTITUENT where ID = REVENUE.CONSTITUENTID) end CONSTITUENT,
        REVENUE.TRANSACTIONCURRENCYID,
        REVENUE.BASECURRENCYID,
        REVENUE.TRANSACTIONAMOUNT,
        REVENUE.ORGANIZATIONAMOUNT
    from dbo.REVENUE 
    inner join #TMP_DATA_REVENUEHISTORY_REVENUEIDS  as [VALIDREVENUE] on REVENUE.ID = [VALIDREVENUE].ID  
    where
        ([VALIDREVENUE].DATEFROM is null or REVENUE.DATE >= [VALIDREVENUE].DATEFROM) and
        ([VALIDREVENUE].DATETO is null or REVENUE.DATE <= [VALIDREVENUE].DATETO)'

    if @USERISADMIN <> 1 or @SITEFILTERMODE <> 0
    begin
        set @SQL = @SQL + N'
        and exists   
        (  
            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB  
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES  
            where RSSUB.REVENUEID = REVENUE.ID'
    end 

    if @USERISADMIN <> 1
    begin
        set @SQL = @SQL + N'
            and exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null))'
    end 

    if @SITEFILTERMODE <> 0
    begin
        set @SQL = @SQL + N'
            and exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)'
    end
    if @USERISADMIN <> 1 or @SITEFILTERMODE <> 0
    begin
        set @SQL = @SQL + N')'
    end 

    insert into @REVENUETOINCLUDE (ID, CONSTITUENTID, AMOUNT, TRANSACTIONTYPE, TRANSACTIONTYPECODE, DATE, DATEADDED, SPLITCOUNT, CONSTITUENT, TRANSACTIONCURRENCYID, BASECURRENCYID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT)  
        exec sp_executesql @SQL, N'@INCLUDEGROUPMEMBERREVENUE bit, @CURRENTAPPUSERID uniqueidentifier, @SECURITYFEATUREID uniqueidentifier, @SECURITYFEATURETYPE tinyint, @SITEFILTERMODE tinyint, @SITESSELECTED xml',
        @INCLUDEGROUPMEMBERREVENUE=@INCLUDEGROUPMEMBERREVENUE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @SECURITYFEATUREID=@SECURITYFEATUREID, @SECURITYFEATURETYPE=@SECURITYFEATURETYPE, @SITEFILTERMODE=@SITEFILTERMODE, @SITESSELECTED=@SITESSELECTED


    select
        ID,
        PARENTID,
        RECORDID,
        TRANSACTIONTYPECODE,
        TYPE,
        REVENUETYPE,
        DATE,
        AMOUNT,
        GROSSAMOUNT,
        BALANCE,
        PASTDUE,
        PAYMENTMETHOD,
        DETAIL,
        CONSTITUENT,
        SITES,
        row_number() over (order by DATE desc, DATEADDED desc) as SEQUENCE,
        case charindex(' (',TYPE)
            when 0 then TYPE
            else substring(TYPE,1,charindex(' (',TYPE)-1)
        end GOTOTYPE,
        DATEADDED,
    DISPLAYCURRENCY
    from
    (
        -- transactions/commitments

        select
            cast(MYSITESREV_CTE.ID as nvarchar(36)) ID,
            null PARENTID,
            MYSITESREV_CTE.ID RECORDID,
            MYSITESREV_CTE.TRANSACTIONTYPECODE,
            -- In the transaction view, transactions w/ only one split will be displayed on a single line.

            -- In this case, show split information at this level as well.

            case
                when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and MYSITESREV_CTE.SPLITCOUNT = 1 then
                    (
                        select 
                            case REVENUESPLIT.APPLICATIONCODE
                                when 0 then REVENUESPLIT.APPLICATION  -- Donation, not Donation payment

                                else REVENUESPLIT.APPLICATION + ' ' + lower(MYSITESREV_CTE.TRANSACTIONTYPE)
                            end
                        from dbo.REVENUESPLIT  
                        where REVENUEID = MYSITESREV_CTE.ID
                    )
                else MYSITESREV_CTE.TRANSACTIONTYPE
            end TYPE,
            case
                when MYSITESREV_CTE.SPLITCOUNT = 1 then
                    (
                        select REVENUESPLIT.TYPE from dbo.REVENUESPLIT 
                        where REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
                    )
                when @GROUPBY = 0 then
                    isnull
                    (
                        (
                            select min(REVENUESPLIT.TYPE) from dbo.REVENUESPLIT 
                            where REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID 
                            group by REVENUESPLIT.REVENUEID 
                            having min(REVENUESPLIT.TYPECODE)=max(REVENUESPLIT.TYPECODE)
                        ),
                        '<Split>'
                    )  
            end REVENUETYPE,  
            MYSITESREV_CTE.DATE,  
            case @CURRENCYCODE
           when 0 then MYSITESREV_CTE.AMOUNT 
           when 1 then MYSITESREV_CTE.ORGANIZATIONAMOUNT 
           when 2 then MYSITESREV_CTE.TRANSACTIONAMOUNT
           when 3 then case @CURRENCYID
                       when MYSITESREV_CTE.TRANSACTIONCURRENCYID then MYSITESREV_CTE.TRANSACTIONAMOUNT
                       else dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                       end
           end  AMOUNT,
            case when @PRODUCTISUK = 1
                then case MYSITESREV_CTE.TRANSACTIONTYPECODE  
                    when 1 then case @CURRENCYCODE
                        when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(MYSITESREV_CTE.ID,0)
                        when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(MYSITESREV_CTE.ID,2)
                        when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(MYSITESREV_CTE.ID,1)
                        when 3 then case @CURRENCYID
                                    when MYSITESREV_CTE.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(MYSITESREV_CTE.ID,1)
                                    else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                                    end
                        end
                    else case @CURRENCYCODE
                        when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID, 1,0)
                        when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID, 1,2)
                        when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID, 1,1)
                        when 3 then case @CURRENCYID
                                    when MYSITESREV_CTE.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID,1,1)
                                    else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(MYSITESREV_CTE.ID, 1,@CURRENCYID)
                                    end
                        end
                    end
                else case @CURRENCYCODE
                    when 0 then MYSITESREV_CTE.AMOUNT 
                    when 1 then MYSITESREV_CTE.ORGANIZATIONAMOUNT 
                    when 2 then MYSITESREV_CTE.TRANSACTIONAMOUNT
                    when 3 then case @CURRENCYID
                                when MYSITESREV_CTE.TRANSACTIONCURRENCYID then MYSITESREV_CTE.TRANSACTIONAMOUNT
                                else dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                                end
                    end
            end as GROSSAMOUNT,  
            case MYSITESREV_CTE.TRANSACTIONTYPECODE  
                when 1 then case @CURRENCYCODE
                         when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                         when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                         else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                         end
                when 3 then case @CURRENCYCODE
                         when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                         when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                         else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                         end
                when 4 then case @CURRENCYCODE
                         when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                         when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                         else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                         end
                when 5 then
                (
                    select
                        ORDERBALANCE.BALANCE
                    from
                    (
                        select dbo.UFN_SALESORDER_GETAMOUNTDUE(SALESORDER.ID) as BALANCE
                        from dbo.SALESORDER
                        where SALESORDER.REVENUEID = MYSITESREV_CTE.ID
                    ) as ORDERBALANCE
                    where ORDERBALANCE.BALANCE <> 0.0
                )
                when 6 then case @CURRENCYCODE
                         when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                         when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                         else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                         end
                when 8 then case @CURRENCYCODE
                         when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                         when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                         else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                         end
                else null  
            end BALANCE,  
            case MYSITESREV_CTE.TRANSACTIONTYPECODE  
                when 1 then case @CURRENCYCODE
                    when 0 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                    when 2 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                    else dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                    end 
                when 2 then case @CURRENCYCODE
                    when 0 then dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(MYSITESREV_CTE.ID,@CURRENTDATE,MYSITESREV_CTE.BASECURRENCYID,0)
                    when 2 then dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(MYSITESREV_CTE.ID,@CURRENTDATE,MYSITESREV_CTE.TRANSACTIONCURRENCYID,0)
                    else dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(MYSITESREV_CTE.ID,@CURRENTDATE,@CURRENCYID,0)
                    end 
                when 3 then case @CURRENCYCODE
                    when 0 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                    when 2 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                    else dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                    end 
                when 6 then case @CURRENCYCODE
                    when 0 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                    when 2 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                    else dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                    end 
                when 8 then case @CURRENCYCODE
                    when 0 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.BASECURRENCYID)
                    when 2 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                    else dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
                    end 
                else null
            end PASTDUE,
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where 
                    REVENUEPAYMENTMETHOD.REVENUEID = MYSITESREV_CTE.ID and 
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 9
            ) PAYMENTMETHOD,
            case 
                when @GROUPBY = 0 or MYSITESREV_CTE.SPLITCOUNT = 1 then 
                    REVENUEHISTORYDETAIL.DETAIL
            end DETAIL,  
            MYSITESREV_CTE.CONSTITUENT,    
            SITELIST.SITES,  
            MYSITESREV_CTE.DATEADDED,
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
            end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        left join dbo.UFN_REVENUE_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUEHISTORYDETAIL on REVENUEHISTORYDETAIL.ID = MYSITESREV_CTE.ID
        left join dbo.UFN_REVENUE_BUILDSITELIST_BULK() as SITELIST on SITELIST.ID = MYSITESREV_CTE.ID
        where
            --Auction donations are queried below  

            MYSITESREV_CTE.TRANSACTIONTYPECODE <> 7 and 
            (
                @GROUPBY = 1 or  
                -- payments for the commitment view are queried below  

                MYSITESREV_CTE.TRANSACTIONTYPECODE <> 0
            ) and 
            (
                @DATERANGECODE = 0 or  
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE or  
                -- for commitment view, show commitment if a payment exists w/in the date range  

                (
                    @GROUPBY = 0 and  
                    (
                        exists
                        (
                            select top 1 'x' from dbo.INSTALLMENTSPLITPAYMENT  
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID  
                            inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID  
                            where 
                                INSTALLMENTSPLITPAYMENT.PLEDGEID = MYSITESREV_CTE.ID and 
                                REVENUE.DATE between @STARTDATE and @ENDDATE
                        ) or
                        exists
                        (
                            select top 1 'x' from dbo.RECURRINGGIFTACTIVITY
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID  
                            inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID  
                            where 
                                RECURRINGGIFTACTIVITY.SOURCEREVENUEID = MYSITESREV_CTE.ID and 
                                REVENUE.DATE between @STARTDATE and @ENDDATE
                        )
                    )
                )
            ) and 
            (
                @REVENUETYPEOPTIONCODE = -1 or  
                exists
                (
                    select top 1 'x' from dbo.REVENUESPLIT 
                    where 
                        REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID and 
                        REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
                )
            ) and 
            (
                @TRANSACTIONTYPEOPTIONCODE = -1 or  
                MYSITESREV_CTE.TRANSACTIONTYPECODE = @TRANSACTIONTYPEOPTIONCODE
            )

        union all  

        ----------------------------------------

        -- transaction splits  

        ----------------------------------------


        select 
            cast(REVENUESPLIT.ID as nvarchar(36)),  
            case when @GROUPBY = 1 then MYSITESREV_CTE.ID end,  
            MYSITESREV_CTE.ID,  
            MYSITESREV_CTE.TRANSACTIONTYPECODE,  
            case REVENUESPLIT.APPLICATIONCODE  
                when 0 then REVENUESPLIT.APPLICATION  
                else REVENUESPLIT.APPLICATION + ' ' + lower(MYSITESREV_CTE.TRANSACTIONTYPE)  
            end,  
            REVENUESPLIT.TYPE,  
            case when @GROUPBY = 0 then MYSITESREV_CTE.DATE end,  
            case @CURRENCYCODE
           when 0 then REVENUESPLIT.AMOUNT
           when 1 then REVENUESPLIT.ORGANIZATIONAMOUNT
           when 2 then REVENUESPLIT.TRANSACTIONAMOUNT
           when 3 then case @CURRENCYID
                       when REVENUESPLIT.TRANSACTIONCURRENCYID then REVENUESPLIT.TRANSACTIONAMOUNT
                       else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@CURRENCYID)
                       end
           end as AMOUNT,
            case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                   when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0) 
                   when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2) 
                   when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1) 
                   when 3 then case @CURRENCYID
                               when REVENUESPLIT.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1) 
                               else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID) 
                               end
                   end
                else case @CURRENCYCODE
                   when 0 then REVENUESPLIT.AMOUNT
                   when 1 then REVENUESPLIT.ORGANIZATIONAMOUNT
                   when 2 then REVENUESPLIT.TRANSACTIONAMOUNT
                   when 3 then case @CURRENCYID
                               when REVENUESPLIT.TRANSACTIONCURRENCYID then REVENUESPLIT.TRANSACTIONAMOUNT
                               else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@CURRENCYID)
                               end
                   end
            end as GROSSAMOUNT,
            null,  
            null,  
            case when @GROUPBY = 0 then (select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = MYSITESREV_CTE.ID) end,  
            REVENUESPLITHISTORYDETAIL.DETAIL,  
            MYSITESREV_CTE.CONSTITUENT,  
            SITELIST.SITES,  
            MYSITESREV_CTE.DATEADDED,
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID  
        left join dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUESPLITHISTORYDETAIL on REVENUESPLITHISTORYDETAIL.ID = REVENUESPLIT.ID
        left join dbo.UFN_REVENUESPLIT_BUILDSITELIST_BULK() as SITELIST on SITELIST.ID = REVENUESPLIT.ID
        where 
        (
            (
                @GROUPBY = 0 and  
                -- In the commitment view, include only unapplied payment splits.  

                -- Applied payment splits are queried below, and commitment splits are not included.  

                MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and  
                REVENUESPLIT.APPLICATIONCODE in (0,1,4,5,12)
            ) or  
            (
                @GROUPBY = 1 and  
                -- In the transaction view, transactions w/ only one split will be displayed using  

                -- only the revenue line, so exclude them here.  

                MYSITESREV_CTE.SPLITCOUNT > 1
            )
        ) and
        (
            @DATERANGECODE = 0 or 
            MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
        ) and 
        (
            @REVENUETYPEOPTIONCODE = -1 or 
            REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
        ) and 
        (
            @TRANSACTIONTYPEOPTIONCODE = -1 or  
            MYSITESREV_CTE.TRANSACTIONTYPECODE = @TRANSACTIONTYPEOPTIONCODE
        )

        union all

        ----------------------------------------

        -- pledge payments (commitment view)

        ----------------------------------------


        select 
            cast(MYSITESREV_CTE.ID as nvarchar(36)) + cast(INSTALLMENTSPLITPAYMENT.PLEDGEID as nvarchar(36)),  
            INSTALLMENTSPLITPAYMENT.PLEDGEID,
            MYSITESREV_CTE.ID,
            min(MYSITESREV_CTE.TRANSACTIONTYPECODE),
            min(REVENUESPLIT.APPLICATION + ' ' + lower(MYSITESREV_CTE.TRANSACTIONTYPE)),
            case
                when count(*)=1 then min(REVENUESPLIT.TYPE)
                when min(REVENUESPLIT.TYPE)=max(REVENUESPLIT.TYPE) then min(REVENUESPLIT.TYPE)
                else '<Split>'
            end,
            min(MYSITESREV_CTE.DATE),
      case @CURRENCYCODE
           when 0 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,MYSITESREV_CTE.BASECURRENCYID))
           when 2 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
           else sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,@CURRENCYID))
           end as AMOUNT,
           case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                   when 0 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0)) 
                   when 1 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2)) 
                   when 2 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1)) 
                   when 3 then case @CURRENCYID
                               when MYSITESREV_CTE.TRANSACTIONCURRENCYID then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1))
                               else sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID))
                               end
                   end
                else case @CURRENCYCODE
                   when 0 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,MYSITESREV_CTE.BASECURRENCYID))
                   when 2 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
                   else sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID,@CURRENCYID))
                end
            end as SPLITGROSSAMOUNT,
            null,
            null,
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where REVENUEPAYMENTMETHOD.REVENUEID = MYSITESREV_CTE.ID
            ),
            dbo.UDA_BUILDLIST
            (
                REVENUESPLITHISTORYDETAIL.DETAIL
            ),
            min(MYSITESREV_CTE.CONSTITUENT),  
            dbo.UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST(MYSITESREV_CTE.ID,INSTALLMENTSPLITPAYMENT.PLEDGEID,0),
            min(MYSITESREV_CTE.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID  
        left join dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUESPLITHISTORYDETAIL on REVENUESPLITHISTORYDETAIL.ID = REVENUESPLIT.ID
        where 
            @GROUPBY = 0 and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and 
            REVENUESPLIT.APPLICATIONCODE in(2,6,7,8,13) and  -- 9,10,11?

            (
                @DATERANGECODE = 0 or 
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
            ) and
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
            ) and
            (
                @TRANSACTIONTYPEOPTIONCODE = -1 or  
                MYSITESREV_CTE.TRANSACTIONTYPECODE = @TRANSACTIONTYPEOPTIONCODE
            )
        group by 
            INSTALLMENTSPLITPAYMENT.PLEDGEID,  
            MYSITESREV_CTE.ID,
      MYSITESREV_CTE.BASECURRENCYID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID

        union all  

        ----------------------------------------------

        -- recurring gift payments (commitment view)  

        ----------------------------------------------


        select 
            cast(MYSITESREV_CTE.ID as nvarchar(36)) + cast(RECURRINGGIFTACTIVITY.SOURCEREVENUEID as nvarchar(36)),  
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID,  
            MYSITESREV_CTE.ID,  
            min(MYSITESREV_CTE.TRANSACTIONTYPECODE),  
            min(REVENUESPLIT.APPLICATION + ' ' + lower(MYSITESREV_CTE.TRANSACTIONTYPE)),  
            case  
                when count(*)=1 then min(REVENUESPLIT.TYPE)  
                when min(REVENUESPLIT.TYPE)=max(REVENUESPLIT.TYPE) then min(REVENUESPLIT.TYPE)  
                else '<Split>'  
            end,  
            min(MYSITESREV_CTE.DATE),  
            case @CURRENCYCODE
           when 0 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.BASECURRENCYID))
           when 2 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
           else sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,@CURRENCYID))
           end as AMOUNT,
           case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                       when 0 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0)) 
                       when 1 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2)) 
                       when 2 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1)) 
                       when 3 then case @CURRENCYID
                                   when MYSITESREV_CTE.TRANSACTIONCURRENCYID then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1))
                                   else sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID))
                                   end
                       end
                else case @CURRENCYCODE
                   when 0 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.BASECURRENCYID))
                   when 2 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
                   else sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,@CURRENCYID))
                   end
            end as SPLITGROSSAMOUNT, 
            null,  
            null,  
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where REVENUEPAYMENTMETHOD.REVENUEID = MYSITESREV_CTE.ID
            ),
            dbo.UDA_BUILDLIST
            (
                REVENUESPLITHISTORYDETAIL.DETAIL
            ),
            min(MYSITESREV_CTE.CONSTITUENT),  
            dbo.UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST(MYSITESREV_CTE.ID,RECURRINGGIFTACTIVITY.SOURCEREVENUEID,1),  
            min(MYSITESREV_CTE.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = REVENUESPLIT.ID  
        left join dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUESPLITHISTORYDETAIL on REVENUESPLITHISTORYDETAIL.ID = REVENUESPLIT.ID
        where 
            @GROUPBY = 0 and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and 
            REVENUESPLIT.APPLICATIONCODE = 3 and 
            (
                @DATERANGECODE = 0 or 
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
            ) and
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
            ) and
            (
                @TRANSACTIONTYPEOPTIONCODE = -1 or  
                MYSITESREV_CTE.TRANSACTIONTYPECODE = @TRANSACTIONTYPEOPTIONCODE
            )
        group by 
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID,  
            MYSITESREV_CTE.ID,
      MYSITESREV_CTE.BASECURRENCYID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID

        union all  

        ----------------------------------------

        -- pledges from other constituents paid by current constituent (commitment view)  

        ----------------------------------------


        select 
            cast(REVENUE.ID as nvarchar(36)) ID,  
            null PARENTID,  
            REVENUE.ID RECORDID,  
            min(REVENUE.TRANSACTIONTYPECODE),  
            min(REVENUE.TRANSACTIONTYPE + ' (' + CONSTITUENT.NAME + ')'),  
            case  
                when count(*) = 1 then  
                    (
                        select top 1 REVENUESPLIT.TYPE from dbo.REVENUESPLIT 
                        where REVENUESPLIT.REVENUEID = REVENUE.ID
                    )
                else
                    isnull
                    (
                        (
                            select min(REVENUESPLIT.TYPE) from dbo.REVENUESPLIT 
                            where REVENUESPLIT.REVENUEID = REVENUE.ID 
                            group by REVENUESPLIT.REVENUEID 
                            having min(REVENUESPLIT.TYPECODE)=max(REVENUESPLIT.TYPECODE)
                        ),
                        '<Split>'
                    )  
            end,
            min(REVENUE.DATE),  
      case @CURRENCYCODE
           when 0 then min(REVENUE.AMOUNT) 
           when 1 then min(REVENUE.ORGANIZATIONAMOUNT)
           when 2 then min(REVENUE.TRANSACTIONAMOUNT)
           when 3 then case @CURRENCYID
                       when REVENUE.TRANSACTIONCURRENCYID then min(REVENUE.TRANSACTIONAMOUNT)
                       else min(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID))
                       end
           end  AMOUNT,
      case when @PRODUCTISUK = 1
            then case @CURRENCYCODE
                   when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUE.ID,0)
                   when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUE.ID,2)
                   when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUE.ID,1)
                   when 3 then case @CURRENCYID
                               when REVENUE.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT_2(REVENUE.ID,1)
                               else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
                               end 
                   end
            else case @CURRENCYCODE
                   when 0 then min(REVENUE.AMOUNT) 
                   when 1 then min(REVENUE.ORGANIZATIONAMOUNT)
                   when 2 then min(REVENUE.TRANSACTIONAMOUNT)
                   when 3 then case @CURRENCYID
                               when REVENUE.TRANSACTIONCURRENCYID then min(REVENUE.TRANSACTIONAMOUNT)
                               else min(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID))
                               end
                   end
      end as GROSSAMOUNT, 
      case @CURRENCYCODE
           when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID,MYSITESREV_CTE.BASECURRENCYID) 
           when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID) 
           else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID,@CURRENCYID) 
           end as BALANCE,
            case @CURRENCYCODE
                    when 0 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(REVENUE.ID,MYSITESREV_CTE.BASECURRENCYID)
                    when 2 then dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(REVENUE.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID)
                    else dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
                    end ,  
            (select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 9) PAYMENTMETHOD,  
            REVENUEHISTORYDETAIL.DETAIL,  
            null,  
            SITELIST.SITES,  
            min(REVENUE.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT [PAYMENTSPLIT] on [PAYMENTSPLIT].REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = [PAYMENTSPLIT].ID  
        inner join dbo.REVENUE on REVENUE.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID  
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID  
        left join dbo.UFN_REVENUE_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUEHISTORYDETAIL on REVENUEHISTORYDETAIL.ID = MYSITESREV_CTE.ID
        left join dbo.UFN_REVENUE_BUILDSITELIST_BULK() as SITELIST on SITELIST.ID = REVENUE.ID
        where 
            @GROUPBY = 0 and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and 
            [PAYMENTSPLIT].APPLICATIONCODE in (2,6,7,8,13) and 
            REVENUE.CONSTITUENTID <> @CONSTITUENTID and 
            case  
                when @INCLUDEGROUPMEMBERREVENUE = 0 then 1  
                -- don't double select rows already selected for other constituents in this group  

                else 
                    case 
                        when exists(select 'x' from @REVENUETOINCLUDE where ID = REVENUE.ID) then 0 
                        else 1 
                    end  
            end = 1
            and 
            (
                @DATERANGECODE = 0 or 
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
            ) and 
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                [PAYMENTSPLIT].TYPECODE = @REVENUETYPEOPTIONCODE
            )
            -- don't include pledges from other constituents when filtering to pledges  

            and @TRANSACTIONTYPEOPTIONCODE = -1  
        group by REVENUE.ID,REVENUEHISTORYDETAIL.DETAIL,MYSITESREV_CTE.BASECURRENCYID,MYSITESREV_CTE.TRANSACTIONCURRENCYID,REVENUE.TRANSACTIONCURRENCYID,SITELIST.SITES

        union all

        ----------------------------------------

        -- recurring gifts from other constituents paid by current constituent (commitment view)  

        ----------------------------------------


        select 
            cast(REVENUE.ID as nvarchar(36)) ID,  
            null PARENTID,  
            REVENUE.ID RECORDID,  
            min(REVENUE.TRANSACTIONTYPECODE),  
            min(REVENUE.TRANSACTIONTYPE + ' (' + CONSTITUENT.NAME + ')'),  
            case  
                when count(*) = 1 then  
                (
                    select top 1 REVENUESPLIT.TYPE from dbo.REVENUESPLIT 
                    where REVENUESPLIT.REVENUEID = REVENUE.ID
                )
                else  
                isnull
                (
                    (
                        select min(REVENUESPLIT.TYPE) from dbo.REVENUESPLIT 
                        where REVENUESPLIT.REVENUEID = REVENUE.ID 
                        group by REVENUESPLIT.REVENUEID 
                        having min(REVENUESPLIT.TYPECODE)=max(REVENUESPLIT.TYPECODE)
                    ),
                    '<Split>'
                )  
            end,  
            min(REVENUE.DATE),  
            case @CURRENCYCODE
           when 0 then min(REVENUE.AMOUNT) 
           when 1 then min(REVENUE.ORGANIZATIONAMOUNT)
           when 2 then min(REVENUE.TRANSACTIONAMOUNT)
           when 3 then case @CURRENCYID
                       when REVENUE.TRANSACTIONCURRENCYID then min(REVENUE.TRANSACTIONAMOUNT)
                       else min(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID))
                       end
           end  AMOUNT,
            case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                   when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID,1,0)
                   when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID,1,2)
                   when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID,1,1)
                   when 3 then case @CURRENCYID
                                    when REVENUE.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(REVENUE.ID,1,1)
                                    else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(REVENUE.ID,1,@CURRENCYID)
                                    end
                   end
                else case @CURRENCYCODE
                   when 0 then min(REVENUE.AMOUNT) 
                   when 1 then min(REVENUE.ORGANIZATIONAMOUNT)
                   when 2 then min(REVENUE.TRANSACTIONAMOUNT)
                   when 3 then case @CURRENCYID
                               when REVENUE.TRANSACTIONCURRENCYID then min(REVENUE.TRANSACTIONAMOUNT)
                               else min(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID))
                               end
                   end
            end as GROSSAMOUNT,  
            null,  
      case @CURRENCYCODE
           when 0 then dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(REVENUE.ID,null,REVENUE.BASECURRENCYID,0)
           when 2 then dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(REVENUE.ID,null,REVENUE.TRANSACTIONCURRENCYID,0)
           else dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(REVENUE.ID,null,@CURRENCYID,0)
      end as PASTDUE,
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where 
                    REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID and 
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 9
            ) PAYMENTMETHOD,  
            REVENUEHISTORYDETAIL.DETAIL,  
            null,  
            SITELIST.SITES,  
            min(REVENUE.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT [PAYMENTSPLIT] on [PAYMENTSPLIT].REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = [PAYMENTSPLIT].ID  
        inner join dbo.REVENUE on REVENUE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID  
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID  
        left join dbo.UFN_REVENUE_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUEHISTORYDETAIL on REVENUEHISTORYDETAIL.ID = MYSITESREV_CTE.ID
        left join dbo.UFN_REVENUE_BUILDSITELIST_BULK() as SITELIST on SITELIST.ID = REVENUE.ID
        where 
            @GROUPBY = 0 and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and 
            [PAYMENTSPLIT].APPLICATIONCODE = 3 and 
            REVENUE.CONSTITUENTID <> @CONSTITUENTID and 
            case  
                when @INCLUDEGROUPMEMBERREVENUE = 0 then 1  
                -- don't double select rows already selected for other constituents in this group  

                else 
                    case 
                        when exists(select 'x' from @REVENUETOINCLUDE where ID = REVENUE.ID) then 0 
                        else 1 
                    end
            end = 1  
            and 
            (
                @DATERANGECODE = 0 or 
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
            ) and 
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                [PAYMENTSPLIT].TYPECODE = @REVENUETYPEOPTIONCODE
            )
            -- don't include RGs from other constituents when filtering to RGs  

            and @TRANSACTIONTYPEOPTIONCODE = -1  
        group by REVENUE.ID,
             REVENUEHISTORYDETAIL.DETAIL,
             MYSITESREV_CTE.BASECURRENCYID,
             MYSITESREV_CTE.TRANSACTIONCURRENCYID,
             REVENUE.TRANSACTIONCURRENCYID,
             REVENUE.BASECURRENCYID,
             REVENUEHISTORYDETAIL.DETAIL,
             SITELIST.SITES

        union all  

        ----------------------------------------  

        -- pledge payments from other constituents to commitments for this constituent (commitment view)  

        ----------------------------------------


        select 
            cast(REVENUE.ID as nvarchar(36)) + cast(INSTALLMENTSPLITPAYMENT.PLEDGEID as nvarchar(36)),  
            INSTALLMENTSPLITPAYMENT.PLEDGEID,  
            REVENUE.ID,  
            min(REVENUE.TRANSACTIONTYPECODE),  
            min(REVENUESPLIT.APPLICATION + ' ' + lower(REVENUE.TRANSACTIONTYPE) + ' (' + CONSTITUENT.NAME + ')'),  
            case  
                when count(*)=1 then min(REVENUESPLIT.TYPE)  
                when min(REVENUESPLIT.TYPE)=max(REVENUESPLIT.TYPE) then min(REVENUESPLIT.TYPE)  
                else '<Split>'  
            end,  
            min(REVENUE.DATE),  
      case @CURRENCYCODE
           when 0 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,MYSITESREV_CTE.BASECURRENCYID))
           when 2 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
           else sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,@CURRENCYID))
           end as AMOUNT,
            case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                       when 0 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0))
                       when 1 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2))
                       when 2 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1))
                       when 3 then case @CURRENCYID
                                   when REVENUESPLIT.TRANSACTIONCURRENCYID then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID,1,1))
                                   else sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID))
                                   end 
                    end
                else case @CURRENCYCODE
                       when 0 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,MYSITESREV_CTE.BASECURRENCYID))
                       when 2 then sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
                       else sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.PLEDGEID,@CURRENCYID))
                       end
            end as SPLITGROSSAMOUNT,
            null,  
            null,  
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
            ),
            dbo.UDA_BUILDLIST
            (
                REVENUESPLITHISTORYDETAIL.DETAIL
            ),
            null,
            dbo.UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST(REVENUE.ID,INSTALLMENTSPLITPAYMENT.PLEDGEID,0),  
            min(REVENUE.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = MYSITESREV_CTE.ID  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID  
        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID  
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID  
        left join dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUESPLITHISTORYDETAIL on REVENUESPLITHISTORYDETAIL.ID = REVENUESPLIT.ID
        where 
            @GROUPBY = 0 and
            MYSITESREV_CTE.TRANSACTIONTYPECODE <> 0 and 
            REVENUE.CONSTITUENTID <> @CONSTITUENTID and 
            case
                when @INCLUDEGROUPMEMBERREVENUE = 0 then 1  
                -- don't double select rows already selected for other constituents in this group  

                else 
                    case 
                        when exists(select 'x' from @REVENUETOINCLUDE where ID = REVENUE.ID) then 0 
                        else 1 
                    end
            end = 1
            and 
            (
                @DATERANGECODE = 0 or 
                REVENUE.DATE between @STARTDATE and @ENDDATE
            ) and 
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
            )
            -- don't include payments from other constituents when filtering to payments  

            and @TRANSACTIONTYPEOPTIONCODE = -1  
        group by 
            INSTALLMENTSPLITPAYMENT.PLEDGEID,  
            REVENUE.ID,
      MYSITESREV_CTE.BASECURRENCYID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID,
      REVENUESPLIT.TRANSACTIONCURRENCYID

        union all  

        ----------------------------------------  

        -- recurring gift payments from other constituents to commitments for this constituent (commitment view)  

        ----------------------------------------


        select 
            cast(REVENUE.ID as nvarchar(36)) + cast(RECURRINGGIFTACTIVITY.SOURCEREVENUEID as nvarchar(36)),  
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID,  
            REVENUE.ID,  
            min(REVENUE.TRANSACTIONTYPECODE),  
            min(REVENUESPLIT.APPLICATION + ' ' + lower(REVENUE.TRANSACTIONTYPE) + ' (' + CONSTITUENT.NAME + ')'),  
            case  
                when count(*)=1 then min(REVENUESPLIT.TYPE)  
                when min(REVENUESPLIT.TYPE)=max(REVENUESPLIT.TYPE) then min(REVENUESPLIT.TYPE)  
                else '<Split>'  
            end,  
            min(REVENUE.DATE),  
            case @CURRENCYCODE
           when 0 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.BASECURRENCYID))
           when 2 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
           else sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,@CURRENCYID))
        end as AMOUNT,
            case when @PRODUCTISUK = 1
                then case @CURRENCYCODE
                        when 0 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0))
                        when 1 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2))
                        when 2 then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1))
                        when 3 then case @CURRENCYID
                                when REVENUESPLIT.TRANSACTIONCURRENCYID then sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID,1,1))
                                else sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID))
                                end
                    end
                else case @CURRENCYCODE
                        when 0 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.BASECURRENCYID))
                        when 2 then sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,MYSITESREV_CTE.TRANSACTIONCURRENCYID))
                        else sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RECURRINGGIFTACTIVITY.ID,@CURRENCYID))
                    end
            end as SPLITGROSSAMOUNT, 
            null,  
            null,  
            (
                select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                where REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
            ),
            dbo.UDA_BUILDLIST
            (
                REVENUESPLITHISTORYDETAIL.DETAIL
            ),
            null,  
            dbo.UFN_PAYMENTSPLITSAPPLIEDTOCOMMITMENT_BUILDSITELIST(REVENUE.ID,RECURRINGGIFTACTIVITY.SOURCEREVENUEID,1),  
            min(REVENUE.DATEADDED),
      case @CURRENCYCODE
           when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID  
        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID  
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID  
        left join dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUESPLITHISTORYDETAIL on REVENUESPLITHISTORYDETAIL.ID = REVENUESPLIT.ID
        where 
            @GROUPBY = 0 and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE <> 0 and 
            REVENUE.CONSTITUENTID <> @CONSTITUENTID and 
            case  
                when @INCLUDEGROUPMEMBERREVENUE = 0 then 1  
                -- don't double select rows already selected for other constituents in this group  

                else 
                    case 
                        when exists(select 'x' from @REVENUETOINCLUDE where ID = REVENUE.ID) then 0 
                        else 1 
                    end
            end = 1 and 
            (
                @DATERANGECODE = 0 or 
                REVENUE.DATE between @STARTDATE and @ENDDATE
            ) and 
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
            )  
            -- don't include payments from other constituents when filtering to payments  

            and @TRANSACTIONTYPEOPTIONCODE = -1  
        group by 
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID,  
            REVENUE.ID,
      MYSITESREV_CTE.BASECURRENCYID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID,
      REVENUESPLIT.TRANSACTIONCURRENCYID

        union all  

        ----------------------------------------  

        -- pledge writeoff transactions  

        ----------------------------------------


        select 
            cast(WRITEOFF.ID as nvarchar(36)),  
            case @GROUPBY  
                when 0 then WRITEOFF.REVENUEID  
            end,  
            WRITEOFF.REVENUEID,  
            -1,  
            case @GROUPBY  
                when 0 then 'Write-off'  
                when 1 then min(MYSITESREV_CTE.TRANSACTIONTYPE) + ' write-off'  
            end,  
            null,  
            min(WRITEOFF.DATE),  
      case @CURRENCYCODE
          when 0 then (select sum(AMOUNT) from dbo.WRITEOFFSPLIT where WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.id)
          when 1 then (select sum(ORGANIZATIONAMOUNT) from dbo.WRITEOFFSPLIT where WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.id)
          when 2 then (select sum(TRANSACTIONAMOUNT) from dbo.WRITEOFFSPLIT where WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.id)
          else sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID,@CURRENCYID))
          end as AMOUNT,
            null,  
            null,  
            null,  
            null,  
            case 
                when @GROUPBY = 0 or (select count(*) from dbo.WRITEOFFSPLIT where WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID) = 1 then 
                    REVENUEHISTORYDETAIL.DETAIL 
            end,  
            min(MYSITESREV_CTE.CONSTITUENT),  
            dbo.UFN_WRITEOFF_BUILDSITELIST(WRITEOFF.ID),  
            min(WRITEOFF.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.WRITEOFF on WRITEOFF.REVENUEID = MYSITESREV_CTE.ID  
    inner join dbo.WRITEOFFSPLIT on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
    left join dbo.UFN_REVENUE_CONSTITUENTREVENUEHISTORYDETAIL_BULK() as REVENUEHISTORYDETAIL on REVENUEHISTORYDETAIL.ID = MYSITESREV_CTE.ID
        where 
            (
                @DATERANGECODE = 0 or 
                WRITEOFF.DATE between @STARTDATE and @ENDDATE
            ) and 
            @REVENUETYPEOPTIONCODE = -1 and 
            @TRANSACTIONTYPEOPTIONCODE in (-1,90)

      group by WRITEOFF.ID,WRITEOFF.REVENUEID,MYSITESREV_CTE.ID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID,MYSITESREV_CTE.BASECURRENCYID,REVENUEHISTORYDETAIL.DETAIL
        union all

        ----------------------------------------  

        -- pledge writeoff splits (transaction view)

        ----------------------------------------


        select 
            cast(WRITEOFFSPLIT.ID as nvarchar(36)),  
            WRITEOFF.ID,  
            WRITEOFF.REVENUEID,  
            -1,  
            'Write-off',  
            null,  
            null,  
      case @CURRENCYCODE
            when 0 then WRITEOFFSPLIT.AMOUNT
            when 1 then WRITEOFFSPLIT.ORGANIZATIONAMOUNT
            when 2 then WRITEOFFSPLIT.TRANSACTIONAMOUNT
            when 3 then dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID,@CURRENCYID)
      end,
            null,  
            null,  
            null,  
            null,  
            DESIGNATION.NAME,  
            MYSITESREV_CTE.CONSTITUENT,  
            SITE.NAME,  
            WRITEOFF.DATEADDED,
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.WRITEOFF on WRITEOFF.REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.WRITEOFFSPLIT on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID  
        left join dbo.DESIGNATION on DESIGNATION.ID = WRITEOFFSPLIT.DESIGNATIONID  
        left join dbo.DESIGNATIONLEVEL on DESIGNATIONLEVEL.ID = coalesce(DESIGNATION.DESIGNATIONLEVEL5ID,DESIGNATION.DESIGNATIONLEVEL4ID,DESIGNATION.DESIGNATIONLEVEL3ID,DESIGNATION.DESIGNATIONLEVEL2ID,DESIGNATION.DESIGNATIONLEVEL1ID)  
        left join dbo.SITE on SITE.ID = DESIGNATIONLEVEL.SITEID  
        where 
            @GROUPBY = 1 and 
            (
                select count(*) from dbo.WRITEOFFSPLIT 
                where WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
            ) > 1 and 
            (
                @DATERANGECODE = 0 or 
                WRITEOFF.DATE between @STARTDATE and @ENDDATE
            ) and 
            @REVENUETYPEOPTIONCODE = -1 and 
            @TRANSACTIONTYPEOPTIONCODE in (-1,90)

        union all  

        ----------------------------------------  

        -- recurring gift writeoffs  

        ----------------------------------------


        select 
            cast(RECURRINGGIFTWRITEOFF.ID as nvarchar(36)),  
            case @GROUPBY when 0 then RECURRINGGIFTWRITEOFF.REVENUEID end,  
            RECURRINGGIFTWRITEOFF.REVENUEID,  
            -2,  
            case @GROUPBY  
                when 0 then 'Write-off'  
                when 1 then min(MYSITESREV_CTE.TRANSACTIONTYPE) + ' write-off'  
            end,  
            null,  
            min(RECURRINGGIFTWRITEOFF.DATE),  
      case @CURRENCYCODE
          when 0 then (select sum(AMOUNT) from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF where RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID)
          when 1 then (select sum(ORGANIZATIONAMOUNT) from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF where RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID)
          when 2 then (select sum(TRANSACTIONAMOUNT) from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF where RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID)
          when 3 then sum(dbo.UFN_RECURRINGGIFTINSTALLMENTWRITEOFF_GETAMOUNTINCURRENCY(RECURRINGGIFTINSTALLMENTWRITEOFF.ID,@CURRENCYID))
      end,
            null,  
            null,  
            null,  
            null,  
            null,  
            min(MYSITESREV_CTE.CONSTITUENT),  
            SITELIST.SITES,  
            min(RECURRINGGIFTWRITEOFF.DATEADDED),
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTWRITEOFF.REVENUEID = MYSITESREV_CTE.ID  
        inner join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID
        left join dbo.UFN_REVENUE_BUILDSITELIST_BULK() as SITELIST on SITELIST.ID = MYSITESREV_CTE.ID    
        where 
            (
                @DATERANGECODE = 0 or 
                RECURRINGGIFTWRITEOFF.DATE between @STARTDATE and @ENDDATE
            ) and 
            @REVENUETYPEOPTIONCODE = -1 and 
            @TRANSACTIONTYPEOPTIONCODE in(-1,90)
        group by RECURRINGGIFTWRITEOFF.ID,RECURRINGGIFTWRITEOFF.REVENUEID,MYSITESREV_CTE.ID,
      MYSITESREV_CTE.TRANSACTIONCURRENCYID,MYSITESREV_CTE.BASECURRENCYID, SITELIST.SITES
        union all 

        --Auction payments by this constituent   


        select 
            cast(REVENUESPLIT.ID as nvarchar(36)),  
            case when @GROUPBY = 1 then MYSITESREV_CTE.ID end,  
            MYSITESREV_CTE.ID,  
            MYSITESREV_CTE.TRANSACTIONTYPECODE,  
            MYSITESREV_CTE.TRANSACTIONTYPE,  
            REVENUESPLIT.TYPE,  
            case when @GROUPBY = 0 then MYSITESREV_CTE.DATE end,  
            case @CURRENCYCODE
           when 0 then REVENUESPLIT.AMOUNT
           when 1 then REVENUESPLIT.ORGANIZATIONAMOUNT
           when 2 then REVENUESPLIT.TRANSACTIONAMOUNT
           when 3 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@CURRENCYID) 
      end as AMOUNT,
            case @CURRENCYCODE
           when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,0) 
           when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,2) 
           when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1) 
           when 3 then case @CURRENCYID
                       when REVENUESPLIT.TRANSACTIONCURRENCYID then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUESPLIT.ID, 1,1) 
                       else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@CURRENCYID) 
                       end
      end as GROSSAMOUNT,
            null,  
            null,  
            case 
                when @GROUPBY = 0 then 
                (
                    select REVENUEPAYMENTMETHOD.PAYMENTMETHOD from dbo.REVENUEPAYMENTMETHOD 
                    where REVENUEPAYMENTMETHOD.REVENUEID = MYSITESREV_CTE.ID
                ) 
            end,  
            dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL(REVENUESPLIT.ID,REVENUESPLIT.APPLICATIONCODE,REVENUESPLIT.TYPECODE,REVENUESPLIT.DESIGNATIONID),  
            MYSITESREV_CTE.CONSTITUENT,  
            dbo.UFN_REVENUESPLIT_BUILDSITELIST(REVENUESPLIT.ID),  
            MYSITESREV_CTE.DATEADDED,
      case @CURRENCYCODE
            when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
            when 0 then MYSITESREV_CTE.BASECURRENCYID
            else @CURRENCYID
      end as DISPLAYCURRENCY
        from @REVENUETOINCLUDE MYSITESREV_CTE  
        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID  
        where   
            (
                @DATERANGECODE = 0 or 
                MYSITESREV_CTE.DATE between @STARTDATE and @ENDDATE
            ) and 
            MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 and 
            (
                @REVENUETYPEOPTIONCODE = -1 or 
                REVENUESPLIT.TYPECODE = @REVENUETYPEOPTIONCODE
            ) and 
            (
                @TRANSACTIONTYPEOPTIONCODE = -1 or  
                MYSITESREV_CTE.TRANSACTIONTYPECODE = @TRANSACTIONTYPEOPTIONCODE
            )

    ) as REVENUEHISTORY;