UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEAL

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

                insert into @REVENUEINFO

                    select 
                        r.APPEALID APPEALID, 
                        d.ID DESID, 
                        d.NAME DESNAME, 
                        coalesce(sum(r.AMOUNT),0) DESTOTALNEWCOMMITMENT, 
                        (
                            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
                                                          (pledgerevspl.APPLICATIONCODE = 6) 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)
                        ) DESPLANNEDGIFTPAID,
                        (
                            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) and
                                (worev.TRANSACTIONTYPECODE = 4)
                        ) DESPLANNEDGIFTWRITTENOFF
                        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 = 4) 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