UFN_DESIGNATION_REVENUERECEIVEDTOTALINCURRENCY

        create function dbo.UFN_DESIGNATION_REVENUERECEIVEDTOTALINCURRENCY
        (
            @STARTDATE datetime = null, 
            @ENDDATE datetime = null, 
            @CURRENCYID uniqueidentifier = null,
            @DESIGNATIONID uniqueidentifier = null
        )
        returns money
        with execute as caller
        as begin
            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;

            select 
                @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

            if @CURRENCYID is null
                set @CURRENCYID = @ORGANIZATIONCURRENCYID;

            select
                @DECIMALDIGITS = DECIMALDIGITS,
                @ROUNDINGTYPECODE = ROUNDINGTYPECODE
            from
                dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID)

            declare @RECEIVEDTOTAL money = 0;

            declare @DL1ID uniqueidentifier;
            declare @DL2ID uniqueidentifier;
            declare @DL3ID uniqueidentifier;
            declare @DL4ID uniqueidentifier;
            declare @DL5ID uniqueidentifier;

            select @DL1ID = DESIGNATIONLEVEL1ID,
                    @DL2ID = DESIGNATIONLEVEL2ID,
                    @DL3ID = DESIGNATIONLEVEL3ID,
                    @DL4ID = DESIGNATIONLEVEL4ID,
                    @DL5ID = DESIGNATIONLEVEL5ID
            from dbo.DESIGNATION
            where ID = @DESIGNATIONID;

            with DESIGNATIONS_CTE as (
                select ID 
                from dbo.DESIGNATION D
                where D.DESIGNATIONLEVEL1ID = @DL1ID and
                        (D.DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                        (D.DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                        (D.DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                        (D.DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
            )
            select @RECEIVEDTOTAL =
                coalesce(sum(REVENUESPLIT.AMOUNTINCURRENCY), 0)
            from 
                dbo.DESIGNATION D
                inner join DESIGNATIONS_CTE D2 on D2.ID = D.ID
                inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.DESIGNATIONID = D2.ID
                inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
            where
                (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                REVENUE.TYPECODE = 0 and
                (REVENUESPLIT.APPLICATIONCODE in (0,3,6,7,10,13) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)) and
                (REVENUESPLIT.APPLICATIONCODE <> 10 or REVENUESPLIT.TYPECODE = 0)

            return @RECEIVEDTOTAL;
        end