UFN_PLEDGE_CALCULATERECEIPTAMOUNT

create function dbo.UFN_PLEDGE_CALCULATERECEIPTAMOUNT
(
    @PLEDGEID uniqueidentifier,
    @AMOUNT money,
  @OVERPAYMENTAPPLICATIONTYPECODE tinyint = null
)
returns money
as
begin

  if @OVERPAYMENTAPPLICATIONTYPECODE is null
    set @OVERPAYMENTAPPLICATIONTYPECODE = coalesce(dbo.UFN_PLEDGE_GETDEFAULTOVERPAYMENTAPPLICATIONCODE(), 255);
  if not @OVERPAYMENTAPPLICATIONTYPECODE in (0, 1, 255)
    set @OVERPAYMENTAPPLICATIONTYPECODE = 1;

   --Overpayment option to pay pledge balance, so pay first installment as usual, then pay from last installment forward

  if @OVERPAYMENTAPPLICATIONTYPECODE = 0
  begin
      --Find the minimum valid sequence number to be paid

      declare @MINSEQUENCE integer;
      select @MINSEQUENCE = min(INSTALLMENT.SEQUENCE) 
          from dbo.INSTALLMENTSPLIT
          inner join dbo.INSTALLMENT
              on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
          where
                        (
              dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) > 0 or
                          (
                              -- WI 191314: $0 pledges can have $0 installments that need to be included

                              -- so the link between the pledge and payment isn't broken

                              INSTALLMENT.AMOUNT = 0 and
                              @AMOUNT = 0
                          )
            )
                        and INSTALLMENT.REVENUEID = @PLEDGEID;

      --Find a sequence number above valid installments for this pledge

      declare @PAYFIRSTSEQUENCE integer;
      select @PAYFIRSTSEQUENCE = (MAX(SEQUENCE) + 1) from [dbo].[UFN_PLEDGE_GETINSTALLMENTS](@PLEDGEID)

      declare INSTALLMENTCURSOR cursor local fast_forward for
          select
              INSTALLMENT.ID, 
              INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
              dbo.UFN_INSTALLMENT_GETINSTALLMENTAMOUNTAPPLIED(INSTALLMENT.ID) as APPLIED,
              dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) [BALANCE],
              INSTALLMENT.TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT,
              case
                  when INSTALLMENT.TRANSACTIONRECEIPTAMOUNT > dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)
                      then INSTALLMENT.TRANSACTIONRECEIPTAMOUNT - dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)
                  else 0
              end as RECEIPTAPPLIED
          from
              dbo.INSTALLMENT
          where
              (
                  dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) > 0 or
                  (
                      -- WI 191314: $0 pledges can have $0 installments that need to be included

                      -- so the link between the pledge and payment isn't broken

                      INSTALLMENT.AMOUNT = 0 and
                      @AMOUNT = 0
                  )
              )
              and INSTALLMENT.REVENUEID = @PLEDGEID
          order by 
              case
                  --mark the first installment to pay with the highest sequence

                  when SEQUENCE = @MINSEQUENCE then @PAYFIRSTSEQUENCE
                  else SEQUENCE 
              end desc;
   end
  --No overpayment option or overpay to next installments, so pay installments in order

  else
  begin
      declare INSTALLMENTCURSOR cursor local fast_forward for
          select 
              INSTALLMENT.ID, 
              INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
              dbo.UFN_INSTALLMENT_GETINSTALLMENTAMOUNTAPPLIED(INSTALLMENT.ID) as APPLIED,
              dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) [BALANCE],
              INSTALLMENT.TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT,
              case
                  when INSTALLMENT.TRANSACTIONRECEIPTAMOUNT > dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)
                      then INSTALLMENT.TRANSACTIONRECEIPTAMOUNT - dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)
                  else 0
              end as RECEIPTAPPLIED
          from
              dbo.INSTALLMENT
          where
              (
                  dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0 or
                  (
                      -- WI 191314: $0 pledges can have $0 installments that need to be included

                      -- so the link between the pledge and payment isn't broken

                      INSTALLMENT.AMOUNT = 0 and
                      @AMOUNT = 0
                  )
              )
              and INSTALLMENT.REVENUEID = @PLEDGEID
          order by
              SEQUENCE asc;
  end


  declare @INSTALLMENTID uniqueidentifier;
  declare @INSTALLMENTAMOUNT money;
  declare @INSTALLMENTAPPLIED money;
  declare @INSTALLMENTBALANCE money;
  declare @INSTALLMENTRECEIPTAMOUNT money;
  declare @INSTALLMENTRECEIPTAPPLIED money;

  declare @ROLLINGAMOUNT money = @AMOUNT;
  declare @INSTALLMENTAMOUNTTOPAY money = 0;
  declare @ROLLINGRECEIPTAMOUNT money = 0;

  open INSTALLMENTCURSOR;
  fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @INSTALLMENTAMOUNT, @INSTALLMENTAPPLIED, @INSTALLMENTBALANCE, @INSTALLMENTRECEIPTAMOUNT, @INSTALLMENTRECEIPTAPPLIED;
  while @@FETCH_STATUS = 0
  begin

    if @ROLLINGAMOUNT <= 0
      break;

    select
      @INSTALLMENTAMOUNTTOPAY = min(AMOUNT)
    from
      (values (@INSTALLMENTBALANCE), (@ROLLINGAMOUNT)) as ALLAMOUNTS(AMOUNT) ;

    if @INSTALLMENTAMOUNTTOPAY < @INSTALLMENTBALANCE
    begin
      declare @TOTALAMOUNTAPPLIED money = @INSTALLMENTAPPLIED + @INSTALLMENTAMOUNTTOPAY;
      declare @BENEFITAMOUNT money = @INSTALLMENTAMOUNT - @INSTALLMENTRECEIPTAMOUNT;

      select
        @ROLLINGRECEIPTAMOUNT = @ROLLINGRECEIPTAMOUNT + max(AMOUNT)
      from
        (values (@TOTALAMOUNTAPPLIED - @BENEFITAMOUNT - @INSTALLMENTRECEIPTAPPLIED), (0)) as ALLAMOUNTS(AMOUNT);
    end
    else
    begin
      set @ROLLINGRECEIPTAMOUNT = @ROLLINGRECEIPTAMOUNT + (@INSTALLMENTRECEIPTAMOUNT - @INSTALLMENTRECEIPTAPPLIED);
    end

    set @ROLLINGAMOUNT = @ROLLINGAMOUNT - @INSTALLMENTAMOUNTTOPAY;

    fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @INSTALLMENTAMOUNT, @INSTALLMENTAPPLIED, @INSTALLMENTBALANCE, @INSTALLMENTRECEIPTAMOUNT, @INSTALLMENTRECEIPTAPPLIED;
  end

  close INSTALLMENTCURSOR;
  deallocate INSTALLMENTCURSOR;

  return @ROLLINGRECEIPTAMOUNT;

end