UFN_DESIGNATIONLEVEL_GETREVENUETOTALSINCURRENCY

            create function dbo.UFN_DESIGNATIONLEVEL_GETREVENUETOTALSINCURRENCY
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null
            )
            returns table
            as
            return(
                with DESIGNATIONS_CTE as (
                    select
                        ID,
                        coalesce(@CURRENCYID,D.BASECURRENCYID) as SELECTEDCURRENCYID
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                        D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                        D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                        D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                        D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )
                    select
                        coalesce(sum(DATA.TOTALREVENUE), 0) as TOTALREVENUE,
                        count(distinct DATA.CONSTITUENTID) as NUMDONORS,
                        count(distinct DATA.REVENUEID) as NUMGIFTS,
                        CURRENCYID
                    from
                        (
                            select
                                coalesce(
                                    (sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,D.SELECTEDCURRENCYID)) 
                                    - 
                                        coalesce(
                                            (select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID,D.SELECTEDCURRENCYID)) 
                                            from dbo.WRITEOFF WO 
                                            inner join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID
                                            inner join DESIGNATIONS_CTE D on WOS.DESIGNATIONID = D.ID
                                            where WO.REVENUEID = R.ID), 0
                                        )
                                    ),0
                                ) TOTALREVENUE,
                                R.CONSTITUENTID,
                                R.ID as REVENUEID,
                                D.SELECTEDCURRENCYID as CURRENCYID
                            from
                                dbo.REVENUESPLIT RS
                                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
                                inner join DESIGNATIONS_CTE D on RS.DESIGNATIONID = D.ID
                            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(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1
                            group by
                                R.CONSTITUENTID, R.ID, D.SELECTEDCURRENCYID
                        ) as DATA
                        group by
                            CURRENCYID
            );