UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL_INCURRENCY_2

        CREATE function [dbo].[UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL_INCURRENCY_2]
        (@STARTDATE datetime, @ENDDATE datetime, @CURRENCYID uniqueidentifier)
        returns @REVENUEINFO table
        (
            DESIGNATIONID uniqueidentifier NOT NULL,
            APPEALID uniqueidentifier,
            NUMDONORS int NOT NULL,
            NUMGIFTS int NOT NULL,
            MAXGIFT money NOT NULL
        )
        as
        begin
            select 
                @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

            declare @ORGANIZATIONCURRENCY uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @CURRENCYCODE tinyint;
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;

            if @CURRENCYID is null
                set @CURRENCYCODE = 0; -- Use the appeal's base currency

            else if @CURRENCYID = @ORGANIZATIONCURRENCY
                set @CURRENCYCODE = 1; -- Use the organization currency

            else
                set @CURRENCYCODE = 3; -- Use the currency specified


            if @CURRENCYCODE = 3
                select
                    @DECIMALDIGITS = DECIMALDIGITS,
                    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID)

            insert into @REVENUEINFO (DESIGNATIONID,    APPEALID, NUMDONORS, NUMGIFTS, MAXGIFT)
            select
                SUBSEL.DESIGNATIONID,
                SUBSEL.REVENUEAPPEALID,
                count(distinct R.CONSTITUENTID),
                count(distinct SUBSEL.REVENUEID),
                max(SUBSEL.SPLITSTODESIGNATIONSUM)
            from
                (
                    select
                        D.ID DESIGNATIONID,
                        APPEALREVENUESPLIT.APPEALID REVENUEAPPEALID,
                        APPEALREVENUESPLIT.REVENUEID,
                        (isnull(sum(APPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY - APPEALREVENUESPLIT.WRITEOFFSPLITAMOUNTINCURRENCY), 0)) SPLITSTODESIGNATIONSUM
                    from dbo.DESIGNATION D
                        inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                            and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                            and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                            and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                            and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                        inner join dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCYNODESNAMENOGIFTAID_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUESPLIT on APPEALREVENUESPLIT.DESIGNATIONID = D2.ID
                    where (APPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                            (APPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null) and
                            (
                                --Donation, recurring gift payment, planned gift payment, matching gift payment, donor challenge payment

                                (APPEALREVENUESPLIT.TRANSACTIONTYPECODE = 0 and (APPEALREVENUESPLIT.APPLICATIONCODE in (0,3,6,7,13) or (APPEALREVENUESPLIT.APPLICATIONCODE = 1 and APPEALREVENUESPLIT.TYPECODE = 0)))
                                or
                                --pledges or grants or auction donations

                                (APPEALREVENUESPLIT.TRANSACTIONTYPECODE in (1,6,7))
                            )
                    group by APPEALREVENUESPLIT.REVENUEID, D.ID, APPEALREVENUESPLIT.APPEALID
                ) SUBSEL
                inner join dbo.FINANCIALTRANSACTION R on R.ID = SUBSEL.REVENUEID
            where R.TYPECODE < 10
                and R.DELETEDON is null
            group by SUBSEL.DESIGNATIONID, SUBSEL.REVENUEAPPEALID;

            return;
        end