UFN_DESIGNATION_PLEDGEDBYAPPEAL_INCURRENCY

            CREATE function dbo.UFN_DESIGNATION_PLEDGEDBYAPPEAL_INCURRENCY
            (@STARTDATE datetime, @ENDDATE datetime, @CURRENCYID uniqueidentifier = null)
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                DESID uniqueidentifier NOT NULL,
                DESNAME nvarchar(500) NOT NULL,
                DESTOTALPLEDGED money NOT NULL,
                DESPLEDGESPAID money NOT NULL,
                DESPLEDGESWRITTENOFF 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 ARS.APPEALID APPEALID, ARS.DESIGNATIONID DESID, ARS.DESIGNATIONNAME DESNAME, 
                        coalesce(sum(ARS.REVENUESPLITAMOUNTINCURRENCY),0) DESTOTALPLEDGED,
                        (select coalesce(sum(APPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY), 0) 
                            from 
                                dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUESPLIT
                            where 
                                APPEALREVENUESPLIT.REVENUESPLITID in (
                                    select ip.PAYMENTID
                                        from dbo.INSTALLMENTPAYMENT ip
                                            inner join dbo.REVENUE pledgerev on ip.PLEDGEID = pledgerev.ID
                                            inner join dbo.REVENUESPLIT pledgerevspl on pledgerev.ID = pledgerevspl.REVENUEID
                                        where (pledgerev.DATE >= @STARTDATE or @STARTDATE is null) and
                                            (pledgerev.DATE <= @ENDDATE or @ENDDATE is null) and 
                                            pledgerev.APPEALID=ARS.APPEALID and pledgerevspl.DESIGNATIONID=ARS.DESIGNATIONID) and
                                (APPEALREVENUESPLIT.APPEALID=ARS.APPEALID and APPEALREVENUESPLIT.DESIGNATIONID=ARS.DESIGNATIONID) and 
                                (APPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                                (APPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null)) DESPLEDGESPAID,
                        (select coalesce(sum(wos.WRITEOFFSPLITAMOUNTINCURRENCY),0) 
                            from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) wos
                            where
                                (wos.DATE >= @STARTDATE or @STARTDATE is null) and
                                (wos.DATE <= @ENDDATE or @ENDDATE is null) and
                                (wos.APPEALID=ARS.APPEALID and wos.DESIGNATIONID=ARS.DESIGNATIONID))DESPLEDGESWRITTENOFF
                        from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCY, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) ARS
                        where (ARS.APPEALID is not null) and 
                            (ARS.TRANSACTIONTYPECODE in (1,3,4,8)) and
                            ((ARS.DATE >= @STARTDATE) or @STARTDATE is null) and
                            ((ARS.DATE <= @ENDDATE)or @ENDDATE is null)
                        group by ARS.APPEALID, ARS.DESIGNATIONID, ARS.DESIGNATIONNAME;

                return;
            end