UFN_VENDOR_CURRENTBALACE

create function dbo.[UFN_VENDOR_CURRENTBALACE]
(
    @ID uniqueidentifier,
    @DATE date = null
)
returns money
as begin
    declare @CURRENTBALANCE money;
    declare @INVOICETYPE integer = 101;
    declare @CREDITMEMOTYPE integer = 102;

    select @CURRENTBALANCE = 
        COALESCE(SUM(
            CASE WHEN F.TYPECODE = @INVOICETYPE -- Invoices

                THEN COALESCE(I.BALANCE,0) 
            ELSE (-1 * COALESCE(CM.BALANCE,0)) -- Credit memos

            END ),0)
    from dbo.FINANCIALTRANSACTION F
    left outer join dbo.INVOICE I ON I.ID = F.ID and ((@DATE is null) or (I.DATEDUE < @DATE))
    left outer join dbo.CREDITMEMO CM ON CM.ID = F.ID
    where F.CONSTITUENTID = @ID and 
        (F.TYPECODE = @INVOICETYPE or F.TYPECODE = @CREDITMEMOTYPE)

    if ((not (@DATE is null)) and (@CURRENTBALANCE < 0))
    begin
        set @CURRENTBALANCE = 0
    end
    return @CURRENTBALANCE
end