UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYAPPEAL

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYAPPEAL
            (
                @STARTDATE datetime, 
                @ENDDATE datetime, 
                @APPEALOPTION tinyint,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )
            returns table
            as
            return(
                select distinct 
                    DL.ID DESIGNATIONLEVELID,
                    case @APPEALOPTION
                        when 0 then APPEAL.ID
                        when 1 then ABU.BUSINESSUNITCODEID
                        when 2 then APPEAL.APPEALREPORT1CODEID
                        end as APPEALID,
                    sum(PLEDGESPLIT.AMOUNT) TOTALPLEDGED,
                    (
                        select coalesce(sum(PAYSPLIT.AMOUNT), 0) 
                        from dbo.REVENUESPLIT PAYSPLIT 
                            inner join dbo.REVENUE PAY on PAYSPLIT.REVENUEID = PAY.ID
                            inner join dbo.DESIGNATION DP on PAYSPLIT.DESIGNATIONID = DP.ID
                        where 
                            PAYSPLIT.ID in (
                                SELECT IP.PAYMENTID
                                FROM DBO.INSTALLMENTPAYMENT IP
                                    inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.ID
                                    inner join dbo.APPEAL PLEDGEAPPEAL on PLEDGE.APPEALID = PLEDGEAPPEAL.ID
                                    left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID= PLEDGEAPPEAL.ID
                                    inner join dbo.REVENUESPLIT PLEDGESPLIT on PLEDGE.ID = PLEDGESPLIT.REVENUEID
                                    inner join dbo.DESIGNATION DPL on PLEDGESPLIT.DESIGNATIONID = DPL.ID
                                WHERE (PLEDGE.DATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                    (PLEDGE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and 
                                    ((@APPEALOPTION = 1 and ABU.BUSINESSUNITCODEID is not null) or @APPEALOPTION <> 1) and
                                    ((@APPEALOPTION = 2 and PLEDGEAPPEAL.APPEALREPORT1CODEID is not null) or @APPEALOPTION <> 2) and
                                    (DPL.DESIGNATIONLEVEL1ID = DL.ID or
                                    DPL.DESIGNATIONLEVEL2ID = DL.ID or
                                    DPL.DESIGNATIONLEVEL3ID = DL.ID or             
                                    DPL.DESIGNATIONLEVEL4ID = DL.ID or
                                    DPL.DESIGNATIONLEVEL5ID = DL.ID) and
                                    (
                                        @CAMPAIGNHIERARCHYPATH is null or
                                        exists (
                                            select 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID
                                            from 
                                                dbo.REVENUESPLITCAMPAIGN
                                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                            where 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID = PLEDGESPLIT.ID and
                                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                        )
                                    )
                            ) and
                            (DP.DESIGNATIONLEVEL1ID = DL.ID or
                            DP.DESIGNATIONLEVEL2ID = DL.ID or
                            DP.DESIGNATIONLEVEL3ID = DL.ID or             
                            DP.DESIGNATIONLEVEL4ID = DL.ID or
                            DP.DESIGNATIONLEVEL5ID = DL.ID) AND
                            (PAY.DATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
                            (PAY.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
                            (
                                @CAMPAIGNHIERARCHYPATH is null or
                                exists (
                                    select 
                                        REVENUESPLITCAMPAIGN.REVENUESPLITID
                                    from 
                                        dbo.REVENUESPLITCAMPAIGN
                                        inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                    where 
                                        REVENUESPLITCAMPAIGN.REVENUESPLITID = PAYSPLIT.ID and
                                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                )
                            )
                    ) TOTALPAID,
                    (
                        select coalesce(sum(WOS.AMOUNT), 0) 
                        from dbo.WRITEOFFSPLIT WOS
                            inner join dbo.DESIGNATION WOSD on WOS.DESIGNATIONID = WOSD.ID
                            inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                            inner join dbo.REVENUE WOREV on (WO.REVENUEID = WOREV.ID AND WOREV.APPEALID = APPEAL.ID)
                            inner join dbo.APPEAL WOAPPEAL on (WOAPPEAL.ID = APPEAL.ID AND WOAPPEAL.ID = WOREV.APPEALID)
                            left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID= WOAPPEAL.ID
                        where 
                            (WOSD.DESIGNATIONLEVEL1ID = DL.ID or
                            WOSD.DESIGNATIONLEVEL2ID = DL.ID or
                            WOSD.DESIGNATIONLEVEL3ID = DL.ID or             
                            WOSD.DESIGNATIONLEVEL4ID = DL.ID or
                            WOSD.DESIGNATIONLEVEL5ID = DL.ID) and
                            (WOREV.DATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                            (WOREV.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                            WOREV.TRANSACTIONTYPECODE in (1,3,4,6) and
                            ((@APPEALOPTION = 1 and ABU.BUSINESSUNITCODEID is not null) or @APPEALOPTION <> 1) and
                            ((@APPEALOPTION = 2 and WOAPPEAL.APPEALREPORT1CODEID is not null) or @APPEALOPTION <> 2)
                    ) TOTALWRITEOFFS
                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 PLEDGESPLIT on D.ID = PLEDGESPLIT.DESIGNATIONID
                    inner join dbo.REVENUE PLEDGE on PLEDGESPLIT.REVENUEID = PLEDGE.ID
                    inner join dbo.APPEAL on PLEDGE.APPEALID = APPEAL.ID
                    left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID= APPEAL.ID
                where (PLEDGE.DATE >=  dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
                    (PLEDGE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) and
                    (PLEDGE.TRANSACTIONTYPECODE in (1,3,4,6)) 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 = PLEDGESPLIT.ID and
                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                        )
                    )
                group by DL.ID, APPEAL.ID, ABU.BUSINESSUNITCODEID, APPEAL.APPEALREPORT1CODEID

            )