UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALINCURRENCY

            CREATE function dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALINCURRENCY
            (
                @STARTDATE datetime, 
                @ENDDATE datetime,
                @CURRENCYID uniqueidentifier
            )
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                DESID uniqueidentifier NOT NULL,
                DESNAME nvarchar(500) NOT NULL,
                DESTOTALPLANNEDGIFT money NOT NULL,
                DESPLANNEDGIFTPAID 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, 
                        DESIGNATION.NAME DESNAME, 
                        coalesce(sum(APPEALREVENUE.REVENUEAMOUNTINCURRENCY),0) DESTOTALNEWCOMMITMENT, 
                        (
                            select 
                                coalesce(sum(PAYAPPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY), 0) 
                            from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) PAYAPPEALREVENUESPLIT
                            where PAYAPPEALREVENUESPLIT.REVENUESPLITID in (
                                                        select IP.PAYMENTID
                            from dbo.INSTALLMENTPAYMENT IP
                                                            inner join dbo.FINANCIALTRANSACTION PLEDGEREV on IP.PLEDGEID = PLEDGEREV.ID
                                                            inner join dbo.REVENUE_EXT on PLEDGEREV.ID = REVENUE_EXT.ID
                                                            inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGEREVSPL on PLEDGEREV.ID = PLEDGEREVSPL.FINANCIALTRANSACTIONID
                                                            inner join dbo.REVENUESPLIT_EXT on PLEDGEREVSPL.ID = REVENUESPLIT_EXT.ID
                                                        where
                                                            (PLEDGEREV.DATE >= @STARTDATE or @STARTDATE is null) and
                                                            (PLEDGEREV.DATE <= @ENDDATE or @ENDDATE is null) and
                                                            (REVENUESPLIT_EXT.APPLICATIONCODE = 6) and 
                                                            (REVENUE_EXT.APPEALID=APPEALREVENUE.APPEALID and REVENUESPLIT_EXT.DESIGNATIONID=DESIGNATION.ID)
                              and PLEDGEREV.DELETEDON is null
                                                        )
                                and
                                (PAYAPPEALREVENUESPLIT.APPEALID=APPEALREVENUE.APPEALID and PAYAPPEALREVENUESPLIT.DESIGNATIONID=DESIGNATION.ID) and 
                                (PAYAPPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                                (PAYAPPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null)
                        ) DESPLANNEDGIFTPAID,
                        (
                            select 
                                coalesce(sum(APPEALREVENUEWRITEOFFSPLIT.WRITEOFFSPLITAMOUNTINCURRENCY),0) 
                            from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_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, DESIGNATION.NAME;

                return;
            end