UFN_DESIGNATION_GETNEWCOMMITMENTREVENUEINCURRENCY

    CREATE function dbo.UFN_DESIGNATION_GETNEWCOMMITMENTREVENUEINCURRENCY
    (
        @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);

        if @ROLLUPTOTAL = 0
            begin
            select @RESULT = 
                coalesce(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID), 0) -
                (
                    coalesce(( --Subtract Writeoffs of the above pledges

                    select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID, @CURRENCYID))
                    from dbo.WRITEOFFSPLIT WOS
                    inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                    inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                    where 
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        WOS.DESIGNATIONID = @DESIGNATIONID and
                        R.TRANSACTIONTYPECODE in (1,6)
                ), 0)) 

            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 in (1,6)
            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 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(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID, @CURRENCYID), 0) -
                (
                    coalesce(( --Subtract Writeoffs of the above pledges

                        select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID, @CURRENCYID))
                        from dbo.WRITEOFFSPLIT WOS
                        inner join DESIGNATIONS_CTE on WOS.DESIGNATIONID = DESIGNATIONS_CTE.ID
                        inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                        inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                        where 
                            (R.DATE >= @STARTDATE or @STARTDATE is null) and
                            (R.DATE <= @ENDDATE or @ENDDATE is null) and
                            R.TRANSACTIONTYPECODE in (1,6)
                    ), 0)
                ) 
            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 in (1,6)
            end
        return @RESULT;
    end