UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYAPPEAL

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUERECEIVED_BYAPPEAL
                (    
                    @STARTDATE datetime, 
                    @ENDDATE datetime, 
                    @APPEALOPTION tinyint,
                    @CAMPAIGNHIERARCHYPATH hierarchyid = null
                )
                returns table
                as
                return(
                    select DL.ID DESIGNATIONLEVELID,
                        case @APPEALOPTION
                            when 0 then APPEAL.ID
                            when 1 then ABU.BUSINESSUNITCODEID
                            when 2 then APPEAL.APPEALREPORT1CODEID
                            end as APPEALID,
                        sum(RS.AMOUNT) TOTALRECEIVED
                    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
                    where (R.TRANSACTIONTYPECODE = 0) AND 
                        (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) 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 DL.ID, APPEAL.ID, ABU.BUSINESSUNITCODEID, APPEAL.APPEALREPORT1CODEID

                )