UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALNODESNAME_INCURRENCY

        CREATE function [dbo].[UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALNODESNAME_INCURRENCY]
        (
            @STARTDATE datetime, 
            @ENDDATE datetime,
            @CURRENCYID uniqueidentifier
        )
        returns @REVENUEINFO table
        (
            APPEALID uniqueidentifier NOT NULL,
            DESID uniqueidentifier NOT NULL,
            DESTOTALPLANNEDGIFT money NOT NULL,
            DESPLANNEDGIFTWRITTENOFF 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
                select
                    APPEALREVENUE.APPEALID APPEALID, 
                    DESIGNATION.ID DESID, 
                    coalesce(sum(APPEALREVENUE.REVENUEAMOUNTINCURRENCY),0) DESTOTALNEWCOMMITMENT, 
                    (
                        select 
                            coalesce(sum(APPEALREVENUEWRITEOFFSPLIT.WRITEOFFSPLITAMOUNTINCURRENCY),0) 
                        from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCYNODESNAMENOGIFTAID_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUEWRITEOFFSPLIT
                        where
                            (APPEALREVENUEWRITEOFFSPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                            (APPEALREVENUEWRITEOFFSPLIT.DATE <= @ENDDATE or @ENDDATE is null) and
                            (APPEALREVENUEWRITEOFFSPLIT.APPEALID=APPEALREVENUE.APPEALID and APPEALREVENUEWRITEOFFSPLIT.DESIGNATIONID=DESIGNATION.ID) and
                            (APPEALREVENUEWRITEOFFSPLIT.TRANSACTIONTYPECODE = 4)
                    ) DESPLANNEDGIFTWRITTENOFF
                from 
                    dbo.UFN_APPEALREVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUE
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on APPEALREVENUE.REVENUEID=FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.DESIGNATION on DESIGNATION.ID=REVENUESPLIT_EXT.DESIGNATIONID
                where 
                    (APPEALREVENUE.APPEALID is not null) and 
                    (APPEALREVENUE.TRANSACTIONTYPECODE = 4) and
                    ((APPEALREVENUE.DATE >= @STARTDATE) or @STARTDATE is null) and
                    ((APPEALREVENUE.DATE <= @ENDDATE)or @ENDDATE is null)
                group by APPEALREVENUE.APPEALID, DESIGNATION.ID;

            return;
        end