UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY

CREATE function dbo.UFN_PLEDGE_GETPASTDUEAMOUNTINCURRENCY
(
  @PLEDGEID uniqueidentifier,
  @CURRENCYID uniqueidentifier
) 
returns money
with execute as caller
as
begin
  declare @RESULT money;
  declare @DUEDATE datetime;
  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @CURRENTDATE datetime = getdate();

  set @DUEDATE = dbo.UFN_DATE_GETEARLIESTTIME(GetDate());

  select @RESULT = 
    isnull(
      (select
         sum(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID))
       from dbo.INSTALLMENT
       where INSTALLMENT.REVENUEID = @PLEDGEID
       --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

       and dateadd(ms, -003, dateadd(d, 1, cast(cast(INSTALLMENT.DATE as date) as datetime))) < @DUEDATE),
      0);

  select
    @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
  from dbo.INSTALLMENT
  where INSTALLMENT.REVENUEID = @PLEDGEID;

  set @CURRENCYID = isnull(@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 @RESULT = dbo.UFN_CURRENCY_CONVERT(@RESULT, @CURRENCYEXCHANGERATEID);
    else    
      set @RESULT = dbo.UFN_CURRENCY_CONVERTINVERSE(@RESULT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, @TRANSACTIONCURRENCYID, @CURRENTDATE, 1, null));
  end

  return @RESULT;
end