UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYAPPEAL

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYAPPEAL
            (
                @STARTDATE datetime,
                @ENDDATE datetime,
                @APPEALOPTION tinyint,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )            
            returns table
            as
            return(
                    select SUBSEL.DESIGNATIONLEVELID,
                        case @APPEALOPTION
                            when 0 then APPEAL.ID
                            when 1 then ABU.BUSINESSUNITCODEID
                            when 2 then APPEAL.APPEALREPORT1CODEID
                        end as APPEALID,
                        count(distinct R.CONSTITUENTID) NUMDONORS,
                        count(distinct SUBSEL.REVENUEID) NUMGIFTS,
                        max(SUBSEL.SPLITSTODESIGNATIONSUM) MAXGIFT,
                        sum(SUBSEL.SPLITSTODESIGNATIONSUM) TOTALGIFT                
                    from 
                        (       
                            select DL.ID,
                                DL.ID DESIGNATIONLEVELID,
                                R.ID REVENUEID,
                                sum(RS.AMOUNT) - coalesce((select sum(WOS.AMOUNT) from dbo.WRITEOFF WO inner join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID where WO.REVENUEID = R.ID), 0) SPLITSTODESIGNATIONSUM
                            from dbo.DESIGNATIONLEVEL DL
                                inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                                    D.DESIGNATIONLEVEL2ID = DL.ID or
                                    D.DESIGNATIONLEVEL3ID = DL.ID or             
                                    D.DESIGNATIONLEVEL4ID = DL.ID or
                                    D.DESIGNATIONLEVEL5ID = DL.ID)
                                inner join dbo.REVENUESPLIT RS on D.ID = RS.DESIGNATIONID
                                inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                                inner join dbo.APPEAL on R.APPEALID = APPEAL.ID
                        left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID= APPEAL.ID
                                left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
                                left join dbo.REVENUE IREV on IREV.ID = ISP.PLEDGEID
                            where (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                    (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                    (dbo.UFN_REVENUE_HASDESIGNATION_PLANNEDGIFTLIKEPLEDGE(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1 or
                                    (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 7 and IREV.TRANSACTIONTYPECODE is null)) and
                                    ((@APPEALOPTION = 1 and ABU.BUSINESSUNITCODEID is not null) or @APPEALOPTION <> 1) and
                                    ((@APPEALOPTION = 2 and APPEAL.APPEALREPORT1CODEID is not null) or @APPEALOPTION <> 2) and
                                    (
                                        @CAMPAIGNHIERARCHYPATH is null or
                                        exists (
                                            select 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID
                                            from 
                                                dbo.REVENUESPLITCAMPAIGN
                                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                            where 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID and
                                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                        )
                                    )
                            group by R.ID, DL.ID, APPEAL.ID, ABU.BUSINESSUNITCODEID, APPEAL.APPEALREPORT1CODEID
                            ) SUBSEL inner join dbo.REVENUE R on R.ID = SUBSEL.REVENUEID
                            inner join dbo.APPEAL on R.APPEALID = APPEAL.ID
                            left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID= APPEAL.ID
                    group by SUBSEL.DESIGNATIONLEVELID, APPEAL.ID, ABU.BUSINESSUNITCODEID, APPEAL.APPEALREPORT1CODEID

            )