UFN_DESIGNATION_GETTOTALRECEIVED_INCURRENCY

            CREATE function dbo.UFN_DESIGNATION_GETTOTALRECEIVED_INCURRENCY
            (
                @DESIGNATIONID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @ROLLUPTOTAL bit = 0,
                @CURRENCYID uniqueidentifier = null
            ) 
            returns money
            with execute as caller
            as begin
                declare @RESULT money;

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(); 

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

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

                if @ROLLUPTOTAL = 0
                    select @RESULT =
                        coalesce(sum(AMOUNTINCURRENCY), 0)
                        from
                        ( select
                            case 
                                when @CURRENCYID = @ORGANIZATIONCURRENCYID
                                    then RDS.ORGANIZATIONAMOUNT
                                when @CURRENCYID = RDS.BASECURRENCYID
                                    then RDS.AMOUNT
                                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID)
                            end as AMOUNTINCURRENCY
                            from dbo.REVENUESPLIT RDS
                            inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                            where
                                (R.DATE >= @STARTDATE or @STARTDATE is null) and
                                (R.DATE <= @ENDDATE or @ENDDATE is null) and
                                RDS.DESIGNATIONID = @DESIGNATIONID and
                                R.TRANSACTIONTYPECODE = 0 and
                                         RDS.APPLICATIONCODE in (0,1,3,6,7,10,13) and
                                (RDS.APPLICATIONCODE <> 10 or RDS.TYPECODE = 0)) RS
                else
                    begin
                    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 DESIGNATIONLEVEL1ID = @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 @RESULT = 
                        coalesce(sum(AMOUNTINCURRENCY),0)
                        from ( 
                            select
                                case 
                                    when @CURRENCYID = @ORGANIZATIONCURRENCYID
                                        then RDS.ORGANIZATIONAMOUNT
                                    when @CURRENCYID = RDS.BASECURRENCYID
                                        then RDS.AMOUNT
                                    else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID)
                                end as AMOUNTINCURRENCY
                                from dbo.REVENUESPLIT RDS
                                inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                                inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                                where
                                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                                    R.TRANSACTIONTYPECODE = 0 and
                                             RDS.APPLICATIONCODE in (0,1,3,6,7,10,13) and
                                    (RDS.APPLICATIONCODE <> 10 or RDS.TYPECODE = 0))RS

                    end
                return @RESULT;
            end