UFN_DESIGNATIONLEVEL_REVENUEPLEDGED

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED
            (
                @STARTDATE datetime, 
                @ENDDATE datetime,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )
            returns table
            as
            return(
                select distinct 
                    DL.ID DESIGNATIONLEVELID,
                    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
                            left join REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID=PAYSPLIT.ID
                            left join CAMPAIGN on CAMPAIGN.ID=REVENUESPLITCAMPAIGN.CAMPAIGNID
                        where 
                            PAYSPLIT.ID in (
                                select IP.PAYMENTID
                                from DBO.INSTALLMENTPAYMENT IP
                                    inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.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
                                    (    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
                        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)
                    ) 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
                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 (
                        @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
            )