UFN_REVENUESOLICITOR_GETSUM_INCURRENCY

create function dbo.UFN_REVENUESOLICITOR_GETSUM_INCURRENCY
(
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @CURRENCYID uniqueidentifier
)
returns money
with execute as caller
as begin
    declare @SUM money

    select 
        @SUM = SUM(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID, @CURRENCYID))
    from
        dbo.REVENUESOLICITOR 
        join dbo.REVENUESPLIT REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID 
        join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    where
        (REVENUESOLICITOR.CONSTITUENTID = @CONSTITUENTID)
        and
        (
            --Count Pledges, MG Claims, Planned Gift

            REVENUE.TRANSACTIONTYPECODE in (1, 3, 4)
            or
            (
                --Payments to all except above

                REVENUE.TRANSACTIONTYPECODE = 0
                and REVENUESPLIT.APPLICATIONCODE not in 
                    (2, --Pledge payment

                     6, -- Planned Gift Payment

                     7 -- Matching Gift Payment

                    )
            )
        )
        and
        (
            (@STARTDATE is null and @ENDDATE is null)
            or
            (@STARTDATE is null and REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
            or
            (REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and @ENDDATE is null)
            or
            (REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
        )


    if @SUM is null
        set @SUM = Cast (0 As money);
    Return @SUM
end