UFN_DESIGNATION_GETPLANNEDGIFTREVENUEINCURRENCY

        CREATE function dbo.UFN_DESIGNATION_GETPLANNEDGIFTREVENUEINCURRENCY
        (
            @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;
            set @RESULT = 0;

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

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

            if @ROLLUPTOTAL = 0
                begin
                select @RESULT = 
                    coalesce(sum(AMOUNTINCURRENCY), 0)
                from 
                    ( select
                            case 
                                when @CURRENCYID = @ORGANIZATIONCURRENCYID
                                    then REVENUESPLIT.ORGANIZATIONAMOUNT
                                when @CURRENCYID = REVENUESPLIT.BASECURRENCYID
                                    then REVENUESPLIT.AMOUNT
                                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)
                            end as AMOUNTINCURRENCY
                        from dbo.REVENUESPLIT
                        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                        where
                            (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                            (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                            REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID and
                            REVENUE.TRANSACTIONTYPECODE = 4 ) RS
                end
            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
                    where DESIGNATIONLEVEL1ID = @DL1ID and
                        (DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                        (DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                        (DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                        (DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
                    )

                select @RESULT = 
                    coalesce(sum(AMOUNTINCURRENCY), 0)
                from 
                    ( select
                            case 
                                when @CURRENCYID = @ORGANIZATIONCURRENCYID
                                    then REVENUESPLIT.ORGANIZATIONAMOUNT
                                when @CURRENCYID = REVENUESPLIT.BASECURRENCYID
                                    then REVENUESPLIT.AMOUNT
                                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)
                            end as AMOUNTINCURRENCY
                        from dbo.REVENUESPLIT 
                        inner join DESIGNATIONS_CTE on REVENUESPLIT.DESIGNATIONID = DESIGNATIONS_CTE.ID
                        inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID    
                        where
                            (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                            (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                            REVENUE.TRANSACTIONTYPECODE = 4) RS
                end

            return @RESULT;
        end