UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY

            create function dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY
            (
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CURRENCYID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null
            )
            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.CURRENCY
                where
                    ID = @CURRENCYID

                insert @REVENUEINFO
                select
                    D.ID,
                    coalesce(sum(RDS.AMOUNTINCURRENCY), 0)
                from
                    dbo.DESIGNATION D
                    inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                        and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                        and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                        and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                        and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RDS on D2.ID = RDS.DESIGNATIONID
                    inner join dbo.REVENUE R on RDS.REVENUEID = R.ID
                where 
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                    D.ID = @DESIGNATIONID and
                    R.TRANSACTIONTYPECODE = 0 and
                    RDS.APPLICATIONCODE in (0,1,3,6,7,10,13) and
                    (RDS.APPLICATIONCODE <> 10 or RDS.TYPECODE = 0)                
                group by D.ID, D.DESIGNATIONLEVEL1ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL5ID
                return
            end