UFN_MEMBER_CHANNELPLEDGEDBYAPPEAL

            CREATE function dbo.UFN_MEMBER_CHANNELPLEDGEDBYAPPEAL
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @REVENUEINFO table
            (
                APPEALID uniqueidentifier NOT NULL,
                CHANNELID uniqueidentifier NOT NULL,
                TOTALPLEDGED money NOT NULL,
                PLEDGESPAID money NOT NULL,
                PLEDGESWRITTENOFF money NOT NULL
            )
            as
            begin

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert into @REVENUEINFO

                select coalesce(PLEDGED.APPEALID,PAID.APPEALID, WRITEOFF.APPEALID) APPEALID,
                        coalesce(PLEDGED.CHANNELID,PAID.CHANNELID, WRITEOFF.CHANNELID) CHANNELID,
                        coalesce(TOTALPLEDGED, 0) TOTALPLEDGED,
                        coalesce(PLEDGESPAID, 0) PLEDGESPAID,
                        coalesce(PLEDGESWRITTENOFF, 0) PLEDGESWRITTENOFF
                from                
                    (select r.APPEALID, coalesce(r.CHANNELCODEID, '00000000-0000-0000-0000-000000000000') CHANNELID, 
                        coalesce(sum(r.AMOUNT),0) TOTALPLEDGED
                        from dbo.REVENUE r
                            inner join dbo.REVENUESPLIT rs on r.ID=rs.REVENUEID
                            inner join dbo.APPEAL a on a.ID = r.APPEALID
                            inner join dbo.MEMBER on r.CONSTITUENTID = MEMBER.CONSTITUENTID
                            inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                        where (r.APPEALID is not null) and r.TRANSACTIONTYPECODE=1 and 
                            ((r.DATE >= @STARTDATE) or @STARTDATE is null) and
                            ((r.DATE <= @ENDDATE)or @ENDDATE is null) and
                            (MEMBER.MEMBERSHIPID = MEMBERSHIP.ID) and
                            (A.MEMBERSHIPPROGRAMID is not null)
                        group by r.CHANNELCODEID, R.APPEALID) PLEDGED

                    full outer join 

                    (select payrev.APPEALID, coalesce(payrev.CHANNELCODEID, '00000000-0000-0000-0000-000000000000') CHANNELID, coalesce(sum(payrevspl.AMOUNT), 0) PLEDGESPAID
                         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
                                        inner join dbo.APPEAL a on a.ID = payrev.APPEALID
                                        inner join dbo.MEMBER on PLEDGErev.CONSTITUENTID = MEMBER.CONSTITUENTID
                                        inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                                    where (pledgerev.DATE >= @STARTDATE or @STARTDATE is null) and
                                        (MEMBER.MEMBERSHIPID = MEMBERSHIP.ID) and 
                                        (pledgerev.DATE <= @ENDDATE or @ENDDATE is null) and 
                                        pledgerev.APPEALID=payrev.APPEALID) and
                            (payrev.APPEALID=payrev.APPEALID) and 
                            (payrev.DATE >= @STARTDATE or @STARTDATE is null) and
                            (payrev.DATE <= @ENDDATE or @ENDDATE is null)
                        group by payrev.CHANNELCODEID, payrev.APPEALID ) PAID 

                    on PLEDGED.APPEALID = PAID.APPEALID and PLEDGED.CHANNELID = PAID.CHANNELID

                    full outer join 

                    (select worev.APPEALID, coalesce(worev.CHANNELCODEID, '00000000-0000-0000-0000-000000000000') CHANNELID, coalesce(sum(wos.AMOUNT),0) PLEDGESWRITTENOFF
                        from dbo.WRITEOFFSPLIT wos
                            inner join dbo.WRITEOFF wo on wos.WRITEOFFID = wo.ID
                            inner join dbo.REVENUE worev on wo.REVENUEID = worev.ID
                            inner join dbo.APPEAL a on a.ID = worev.APPEALID
                     inner join dbo.MEMBER on worev.CONSTITUENTID = MEMBER.CONSTITUENTID
                        inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPPROGRAMID = A.MEMBERSHIPPROGRAMID
                        where
                            (worev.DATE >= @STARTDATE or @STARTDATE is null) and
                            (worev.DATE <= @ENDDATE or @ENDDATE is null) and
                            (worev.APPEALID=worev.APPEALID)and
                            MEMBER.MEMBERSHIPID = MEMBERSHIP.id
                        group by worev.CHANNELCODEID, worev.APPEALID) WRITEOFF

                    on PLEDGED.APPEALID = WRITEOFF.APPEALID and PLEDGED.CHANNELID = WRITEOFF.CHANNELID

                return;


            end