UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY

            CREATE function dbo.UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY
            (
                @STARTDATE datetime, 
                @ENDDATE datetime, 
                @CURRENCYID uniqueidentifier,
                @DESIGNATIONID uniqueidentifier
            )
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                TOTALRECEIVED money NOT NULL
            )
            as
            begin
                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                    @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

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

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

                insert @REVENUEINFO
                select 
                    D.ID,
                    isnull(sum(REVENUESPLIT.AMOUNTINCURRENCY), 0) - isnull(sum(REFUNDSPLIT.REFUNDTOTAL), 0)
                from 
                    dbo.DESIGNATION D
                    inner join dbo.DESIGNATION DESIGNATION2 on (D.DESIGNATIONLEVEL1ID = DESIGNATION2.DESIGNATIONLEVEL1ID
                        and (D.DESIGNATIONLEVEL2ID = DESIGNATION2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                        and (D.DESIGNATIONLEVEL3ID = DESIGNATION2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                        and (D.DESIGNATIONLEVEL4ID = DESIGNATION2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                        and (D.DESIGNATIONLEVEL5ID = DESIGNATION2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.DESIGNATIONID = DESIGNATION2.ID
                    left outer join dbo.UFN_CREDIT_GETSPLITREFUNDAMOUNT_BULK() REFUNDSPLIT
                        on REVENUESPLIT.ID = REFUNDSPLIT.SOURCELINEITEMID
                    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where
                    (cast(REVENUE.DATE as datetime) >= @STARTDATE or @STARTDATE is null) and
                    (cast(REVENUE.DATE as datetime) <= @ENDDATE or @ENDDATE is null) and
                    /*    
                        In the case of a sales order, use the order split applied to the gift to aid incorporating refunds.
                        Filter out any payments with application type 'Order' so as not to double count sales order splits. 
                    */    
                    REVENUE.TYPECODE in (0, 5) and REVENUE.DELETEDON is null and
                    (REVENUESPLIT.APPLICATIONCODE in (0,3,6,7,13) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)) and
                    (REVENUESPLIT.SPLITDELETEDON is null and REVENUESPLIT.REVENUEDELETEDON is null) and
                    (@DESIGNATIONID is null or @DESIGNATIONID = D.ID)    
                group by D.ID

            return

            end