UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2

create function dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTINCURRENCY_2(
    @REVENUEID uniqueidentifier,
    @ASOFDATE date = null,
    @CURRENCYID uniqueidentifier,
    @IGNORESTATUS bit = 0
  )
  returns money
  with execute as caller
  as begin
    declare @PASTDUE money;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @CURRENTDATE datetime = getdate();

    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_GETINSTALLMENTBALANCE(ID)
        from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @REVENUEID
        and DATE < @ASOFDATE
        and (@IGNORESTATUS = 1 or STATUSCODE = 1)
        and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) > 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;

        select @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID from dbo.RECURRINGGIFTINSTALLMENT where  REVENUEID = @REVENUEID; 
        set @CURRENCYID = coalesce(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY());
        if @CURRENCYID is not null and @TRANSACTIONCURRENCYID is not null and @CURRENCYID <> @TRANSACTIONCURRENCYID
        begin
            declare @CURRENCYEXCHANGERATEID uniqueidentifier;
            set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@TRANSACTIONCURRENCYID, @CURRENCYID, @CURRENTDATE, 1, null);
            if (@CURRENCYEXCHANGERATEID is not null)
                set @PASTDUE = dbo.UFN_CURRENCY_CONVERT(@PASTDUE, @CURRENCYEXCHANGERATEID)
            else    
                set @PASTDUE = dbo.UFN_CURRENCY_CONVERTINVERSE(@PASTDUE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, @TRANSACTIONCURRENCYID, @CURRENTDATE, 1, null))
        end

    end
    else
        set @PASTDUE = 0;

    return @PASTDUE;
   end