UFN_DESIGNATION_NEWCOMMITMENTREVENUEBYAPPEAL

            create function dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEBYAPPEAL
            (
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) 
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                DESID uniqueidentifier NOT NULL,
                DESNAME nvarchar(500) NOT NULL,
                DESTOTALNEWCOMMITMENT money NOT NULL,
                DESNEWCOMMITMENTPAID money NOT NULL,
                DESNEWCOMMITMENTWRITTENOFF 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 
                                                        pledgerev.APPEALID=r.APPEALID and pledgerevspl.DESIGNATIONID=rs.DESIGNATIONID and
                                                        pledgerev.TRANSACTIONTYPECODE in (1,6)
                                                ) 
                                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)
                        ) DESNEWCOMMITMENTPAID,
                        (
                            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 in (1,6)
                        ) DESNEWCOMMITMENTWRITTENOFF
                        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,6)) 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