UFN_DESIGNATION_PLEDGEDBYAPPEAL

            CREATE function dbo.UFN_DESIGNATION_PLEDGEDBYAPPEAL
            (@STARTDATE datetime, @ENDDATE datetime)
            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);

                insert into @REVENUEINFO

                    select r.APPEALID APPEALID, d.ID DESID, d.NAME DESNAME, 
                        coalesce(sum(r.AMOUNT),0) DESTOTALPLEDGED, 
                        (select coalesce(sum(payrevspl.AMOUNT), 0) 
                             from dbo.REVENUESPLIT payrevspl 
                                inner join dbo.REVENUE payrev on payrevspl.REVENUEID = payrev.ID
                             where 
                                payrevspl.ID 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=r.APPEALID and pledgerevspl.DESIGNATIONID=rs.DESIGNATIONID) and
                                (payrev.APPEALID=r.APPEALID and payrevspl.DESIGNATIONID=rs.DESIGNATIONID) and 
                                (payrev.DATE >= @STARTDATE or @STARTDATE is null) and
                                (payrev.DATE <= @ENDDATE or @ENDDATE is null)) DESPLEDGESPAID,
                        (select coalesce(sum(wos.AMOUNT),0) 
                            from dbo.WRITEOFFSPLIT wos
                                inner join dbo.WRITEOFF wo on wos.WRITEOFFID = wo.ID
                                inner join dbo.REVENUE worev on wo.REVENUEID = worev.ID
                            where
                                (worev.DATE >= @STARTDATE or @STARTDATE is null) and
                                (worev.DATE <= @ENDDATE or @ENDDATE is null) and
                                (worev.APPEALID=r.APPEALID and wos.DESIGNATIONID=rs.DESIGNATIONID))DESPLEDGESWRITTENOFF
                        from dbo.REVENUE r
                            inner join dbo.REVENUESPLIT rs on r.ID=rs.REVENUEID
                            inner join dbo.DESIGNATION d on d.ID=rs.DESIGNATIONID
                        where (r.APPEALID is not null) and 
                            (r.TRANSACTIONTYPECODE in (1,3,4,8)) and
                            ((r.DATE >= @STARTDATE) or @STARTDATE is null) and
                            ((r.DATE <= @ENDDATE)or @ENDDATE is null)
                        group by r.APPEALID, d.ID, rs.DESIGNATIONID, d.NAME;

                return;


            end