UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS

CREATE function dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(
  @RECURRINGGIFTID uniqueidentifier,
  @NEXTINSTALLMENTDUE money,
  @PASTDUE money,
  @NEXTINSTALLMENTDATE date,
  @EFFECTIVEDATE datetime,
  @IGNOREPAYMENTID uniqueidentifier
)
returns table
as return
with BASE as (
  select OVERPAYMENTCODE,
         INSTALLMENTUNDERPAYMENTCODE,
         FIRSTINSTALLMENTCODE,
         APPLYTOPASTINSTALLMENTS,
         PASTBALANCEUNDERPAYMENTCODE,
         case when OVERPAYMENTCODE > 0 then
           case APPLYTOPASTINSTALLMENTS when 0 then @NEXTINSTALLMENTDUE else isnull(nullif(@PASTDUE,0),@NEXTINSTALLMENTDUE) end
         end as ACTIONABOVEAMOUNT,
         case when (INSTALLMENTUNDERPAYMENTCODE = 1 and @NEXTINSTALLMENTDATE <= cast(@EFFECTIVEDATE as date)) or INSTALLMENTUNDERPAYMENTCODE = 2 then
           case APPLYTOPASTINSTALLMENTS
             when 0 then
               case when PASTBALANCEUNDERPAYMENTCODE = 0 then @NEXTINSTALLMENTDUE
                   when @PASTDUE > @NEXTINSTALLMENTDUE then -1  -- underpayment action will happen no matter what

               end
             when 1 then
               case PASTBALANCEUNDERPAYMENTCODE
                 when 0 then -2   -- multiple amounts possible, create list below

                 else @PASTDUE
               end
           end
         end as ACTIONBELOWAMOUNT,
         ISRGPAYMENTHANDLINGBATCH
  from dbo.UFN_RECURRINGGIFTSETTING(case when @IGNOREPAYMENTID is null then null else @EFFECTIVEDATE end)
)
select ACTIONABOVEAMOUNT,
       OVERPAYMENTCODE,
       case
         when ACTIONBELOWAMOUNT = -2 then
           (
             -- get the balances of the individual installments

             -- SEQ will indicate the order money will be applied to the installments according to the rule

             select sum(i.BALANCE) as AMOUNT
             from (
               select b.BALANCE as BALANCE,
                      row_number() over(order by case when FIRSTINSTALLMENTCODE = 0 then i.DATE end,
                                                 case when FIRSTINSTALLMENTCODE = 1 then i.DATE end desc) SEQ
               from dbo.V_RECURRINGGIFTINSTALLMENT i
               cross apply (select case when @IGNOREPAYMENTID is null then i.BALANCE else dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(i.ID, @IGNOREPAYMENTID) end BALANCE) as b
               where i.REVENUEID = @RECURRINGGIFTID
               and i.DATE <= cast(@EFFECTIVEDATE as date)
               and b.BALANCE > 0
             ) i
             -- join to NUMBERS table in order to add in the balances of all previous installments when determining the amount

             -- required to pay off any given installment (we don't want $5, $5, $5 in the XML, we want $5, $10, $15)

             -- In the $5/$5/$5 example, we will match rows for NUM 1, 2, and 3.  The row for NUM 1 will include only the first

             -- installment, summing to $5.  The row for NUM 2 will include the first two, summing to $10.  Etc.

             cross apply dbo.NUMBERS n
             where n.NUM > 0
             and i.SEQ <= n.NUM
             group by n.NUM having n.NUM <= max(i.SEQ)
             order by n.NUM
             for xml raw('ITEM'),type,elements,root('ACTIONBELOWAMOUNTS'),BINARY BASE64
           )
         when ACTIONBELOWAMOUNT is not null then
           (
             select ACTIONBELOWAMOUNT as AMOUNT
             for xml raw('ITEM'),type,elements,root('ACTIONBELOWAMOUNTS'),BINARY BASE64
           )
       end as ACTIONBELOWAMOUNTS,
       INSTALLMENTUNDERPAYMENTCODE,
       APPLYTOPASTINSTALLMENTS,
       PASTBALANCEUNDERPAYMENTCODE,
       ISRGPAYMENTHANDLINGBATCH
from BASE