UFN_RECURRINGGIFT_GETPASTDUEAMOUNTIGNOREPAYMENT_2

CREATE function dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTIGNOREPAYMENT_2(
    @REVENUEID uniqueidentifier,
    @PAYMENTID uniqueidentifier,
    @ASOFDATE date = null,
    @IGNORESTATUS bit = 0
)
returns money
with execute as caller
as begin
    declare @PASTDUE money;

    declare @RGSTATUS tinyint;

    select @RGSTATUS = STATUSCODE
    from dbo.REVENUESCHEDULE
    where ID = @REVENUEID;

    if @RGSTATUS in(0,1,5)
    begin
        if @ASOFDATE is null
            set @ASOFDATE = getdate();

        declare @INSTALLMENTS table (BALANCE money);

        insert into @INSTALLMENTS
        select dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(ID, @PAYMENTID)
        from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @REVENUEID
        and DATE <= @ASOFDATE and (@IGNORESTATUS = 1 or STATUSCODE = 1)
        and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(ID, @PAYMENTID) > 0;

        insert into @INSTALLMENTS
        select R.TRANSACTIONAMOUNT
        from REVENUE R
        cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I
        where R.ID = @REVENUEID
        and I.DATE <= @ASOFDATE
        and @IGNORESTATUS = 1;

        select @PASTDUE = isnull(sum(BALANCE),0)
        from @INSTALLMENTS;
    end
    else
        set @PASTDUE = 0;


    return @PASTDUE;
end