USP_DATALIST_PAYMENTAPPLICATIONSIGNOREPAYMENT

        CREATE procedure dbo.USP_DATALIST_PAYMENTAPPLICATIONSIGNOREPAYMENT(
          @PAYMENTID uniqueidentifier = null
        )
        as 
        begin
          set nocount on

          declare @APPLICATION as table(
            ID uniqueidentifier, 
            APPLICATIONCODE tinyint,
            TRANSACTIONCURRENCYID uniqueidentifier
          )

          insert into @APPLICATION
          select distinct
            APPLICATIONID, 
            APPLICATIONCODE,
            TRANSACTIONCURRENCYID
          from dbo.UFN_REVENUE_GETAPPLICATIONS(@PAYMENTID)

          declare @ADJUSTEDAPPLICATION as table(
            ID uniqueidentifier, 
            APPLICATIONCODE tinyint,
            NEXTINSTALLMENTDATE datetime, 
            NEXTINSTALLMENTDUE money, 
            BALANCE money, 
            PASTDUEAMOUNT money, 
            TOTALPAID money,
            TRANSACTIONCURRENCYID uniqueidentifier,
            RGACTIONABOVEAMOUNT money,
            RGACTIONBELOWAMOUNTS xml
          );

          --Event (1)

          insert into @ADJUSTEDAPPLICATION(
            ID, APPLICATIONCODE, BALANCE, TRANSACTIONCURRENCYID
          )
          select
            APPLICATION.ID,
            APPLICATION.APPLICATIONCODE,
            dbo.UFN_EVENTREGISTRANT_GETBALANCEIGNOREPAYMENT(REGISTRANT.ID, @PAYMENTID),
            EVENT.BASECURRENCYID
          from @APPLICATION APPLICATION
            inner join dbo.REGISTRANT on REGISTRANT.ID = APPLICATION.ID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
          where APPLICATION.APPLICATIONCODE = 1

          --Pledge (2), Grant Award (8)

          insert into @ADJUSTEDAPPLICATION(
            ID, APPLICATIONCODE, NEXTINSTALLMENTDATE, NEXTINSTALLMENTDUE, BALANCE, TRANSACTIONCURRENCYID
          )
          select
            APPLICATION.ID,
            APPLICATION.APPLICATIONCODE,
            INSTALLMENT.DATE,
            dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(INSTALLMENT.ID, @PAYMENTID),
            dbo.UFN_PLEDGE_GETBALANCEIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID),
            APPLICATION.TRANSACTIONCURRENCYID
          from @APPLICATION APPLICATION
            left join dbo.INSTALLMENT on INSTALLMENT.REVENUEID = APPLICATION.ID
          where APPLICATION.APPLICATIONCODE in (2, 8)
            and INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENTIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID);

          --Recurring Gift (3)

          with BASE as (
            select
              APPLICATION.ID,
              APPLICATION.APPLICATIONCODE,
              RECURRINGGIFTINSTALLMENT.DATE,
              dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(RECURRINGGIFTINSTALLMENT.ID, @PAYMENTID) NEXTINSTALLMENTDUE,
              dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTIGNOREPAYMENT_2(APPLICATION.ID, @PAYMENTID, null, 1) PASTDUEAMOUNT,
              dbo.UFN_RECURRINGGIFT_GETTOTALPAIDIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID) TOTALPAID,
              APPLICATION.TRANSACTIONCURRENCYID
            from @APPLICATION APPLICATION
            inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.REVENUEID = APPLICATION.ID
            where APPLICATION.APPLICATIONCODE = 3
              and RECURRINGGIFTINSTALLMENT.ID = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID, null)
          )
          insert into @ADJUSTEDAPPLICATION(
            ID, APPLICATIONCODE, NEXTINSTALLMENTDATE, NEXTINSTALLMENTDUE, PASTDUEAMOUNT, TOTALPAID, TRANSACTIONCURRENCYID, RGACTIONABOVEAMOUNT, RGACTIONBELOWAMOUNTS
          )
          select
            B.ID,
            B.APPLICATIONCODE,
            B.DATE,
            B.NEXTINSTALLMENTDUE,
            case when B.PASTDUEAMOUNT = B.NEXTINSTALLMENTDUE or  -- don't show if the same as the next installment amount

                      (U.APPLYTOPASTINSTALLMENTS = 0 and
                (U.PASTBALANCEUNDERPAYMENTCODE = 0 or     -- don't show if applying that amount wouldn't result in paying off the past balance

                        U.OVERPAYMENTCODE = 2))                  -- or that amount would not all be applied to the RG

                   then 0 else B.PASTDUEAMOUNT end,
            B.TOTALPAID,
            B.TRANSACTIONCURRENCYID,
            U.ACTIONABOVEAMOUNT,
            U.ACTIONBELOWAMOUNTS
          from BASE B
          cross apply dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(B.ID,B.NEXTINSTALLMENTDUE,B.PASTDUEAMOUNT,B.DATE,getdate(),@PAYMENTID) U;

          --Planned gift (6), Matching gift claim (7), Donor challenge (13)

          insert into @ADJUSTEDAPPLICATION(
            ID, APPLICATIONCODE, BALANCE, TOTALPAID, TRANSACTIONCURRENCYID
          )
          select
            APPLICATION.ID,
            APPLICATION.APPLICATIONCODE,
            dbo.UFN_PLEDGE_GETBALANCEIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID),
            dbo.UFN_PLEDGE_GETAMOUNTPAIDIGNOREPAYMENT(APPLICATION.ID, @PAYMENTID),
            APPLICATION.TRANSACTIONCURRENCYID
          from @APPLICATION APPLICATION
          where APPLICATION.APPLICATIONCODE in (6, 7, 13)






          select 
            ID, 
            APPLICATIONCODE, 
            NEXTINSTALLMENTDATE, 
            coalesce(NEXTINSTALLMENTDUE,0), 
            coalesce(BALANCE,0), 
            coalesce(PASTDUEAMOUNT,0), 
            coalesce(TOTALPAID,0), 
            TRANSACTIONCURRENCYID,
            RGACTIONABOVEAMOUNT,
            RGACTIONBELOWAMOUNTS
          from @ADJUSTEDAPPLICATION
          order by APPLICATIONCODE;
        end