USP_REMINDER_GETRAWDATA

        /*
        This proc replaces the scalar functions used in the data gathering portion of the pledge reminder process with set based queries for performance.
        Do not add scalar functions
        */
CREATE procedure dbo.USP_REMINDER_GETRAWDATA (@AsOfDate datetime)
with execute as owner
as
begin
set nocount on

create table #InstallmentBalances(ID uniqueidentifier, InstallmentID uniqueidentifier Primary Key, InstallmentDate datetime, Balance money, Sequence int)
insert into #InstallmentBalances(ID, InstallmentID, InstallmentDate, Balance, Sequence)
select FINANCIALTRANSACTION.ID, INSTALLMENT.ID, INSTALLMENT.DATE, 
INSTALLMENT.TRANSACTIONAMOUNT - isnull(V_Payment.PaymentAmount,0) - isnull(V_WriteOff.WriteOffAmount,0), INSTALLMENT.SEQUENCE
from #tempFTIDs inner join dbo.FINANCIALTRANSACTION on #tempFTIDs.ID = FINANCIALTRANSACTION.ID
inner join dbo.INSTALLMENT on FINANCIALTRANSACTION.ID = INSTALLMENT.REVENUEID
left join    
    (select INSTALLMENT.ID, sum(INSTALLMENTPAYMENT.AMOUNT) as PaymentAmount
    from #tempFTIDs inner join dbo.INSTALLMENT on #tempFTIDs.ID = INSTALLMENT.REVENUEID 
    inner join dbo.INSTALLMENTPAYMENT on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
    group by INSTALLMENT.ID) as V_Payment
on INSTALLMENT.ID = V_Payment.ID
left join    
    (select INSTALLMENT.ID, sum(INSTALLMENTWRITEOFF.AMOUNT) as WriteOffAmount
    from #tempFTIDs inner join dbo.INSTALLMENT on #tempFTIDs.ID = INSTALLMENT.REVENUEID 
    inner join dbo.INSTALLMENTWRITEOFF on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
    group by INSTALLMENT.ID) as V_WriteOff
on INSTALLMENT.ID = V_WriteOff.ID
where FINANCIALTRANSACTION.TYPECODE != 2

--replaces UFN_REMINDER_PAYMENTDUE

create table #PaymentDue (ID uniqueidentifier Primary Key, AmountDue money)
insert into #PaymentDue(ID, AmountDue)
select ID, sum(Balance)
from #InstallmentBalances
where InstallmentDate <= @ASOFDATE
group by ID

--replaces UFN_REMINDER_GETLASTREMINDERSENTDATE

create table #LastReminderDates (ID uniqueidentifier Primary Key, LastDate datetime, TransactionTypeCode tinyint)
insert into #LastReminderdates (ID, LastDate, TransactionTypeCode)
select #tempFTIDs.ID, max(PLEDGEREMINDERSENT.AMOUNTDUEDATE), FINANCIALTRANSACTION.TYPECODE
from #tempFTIDs inner join dbo.FINANCIALTRANSACTION on #tempFTIDs.ID = FINANCIALTRANSACTION.ID
left join dbo.PLEDGEREMINDERSENT on FINANCIALTRANSACTION.ID = PLEDGEREMINDERSENT.REVENUEID
where FINANCIALTRANSACTION.DELETEDON is null
group by #tempFTIDs.ID, FINANCIALTRANSACTION.TYPECODE

declare @FIRSTINSTALLMENTCODE tinyint;
select @FIRSTINSTALLMENTCODE = FIRSTINSTALLMENTCODE from dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT()

--replaces UFN_REMINDER_GETNEXTINSTALLMENTDATE

create table #NextInstallmentDates (
    ID uniqueidentifier primary key,
    LastDate datetime,
    NextInstallmentDate datetime
    )

insert into #NextInstallmentDates (
    ID,
    LastDate,
    NextInstallmentDate
    )
select
    #LastReminderDates.ID,
    LastDate,
    min(
        case #LastReminderDates.TransactionTypeCode
            when 2
                then REVENUESCHEDULE.NEXTTRANSACTIONDATE
            else INSTALLMENT.InstallmentDate
        end
    )
from
    #LastReminderDates
    left join dbo.REVENUESCHEDULE
        on #LastReminderDates.TransactionTypeCode = 2
        and REVENUESCHEDULE.ID = #LastReminderDates.ID
    left join #InstallmentBalances INSTALLMENT
        on #LastReminderDates.ID = INSTALLMENT.ID
        and (
            INSTALLMENT.InstallmentDate > #LastReminderDates.LastDate
            or #LastReminderDates.LastDate is null
            )
        and INSTALLMENT.InstallmentDate <= @ASOFDATE
        and #LastReminderDates.TransactionTypeCode != 2
        and INSTALLMENT.Balance > 0
group by
    #LastReminderDates.ID,
    LastDate

--UFN_PLEDGE_GETPASTDUEAMOUNT_BYSENTDATE

create table #PledgePastDueAmounts (ID uniqueidentifier Primary Key, PastDueAmount money)
insert into #PledgePastDueAmounts (ID, PastDueAmount)
select #InstallmentBalances.ID, isnull(sum(BALANCE),0)
from #InstallmentBalances inner join #LastReminderDates on #InstallmentBalances.ID = #LastReminderDates.ID
--WI65721 - If pledge reminders were sent out with a date later than the asOfDate, the just use asOfDate.

where convert(date, #InstallmentBalances.InstallmentDate) < isnull(case when @ASOFDATE < convert(date, #LastReminderDates.LastDate) then @ASOFDATE
                                                            else convert(date, #LastReminderDates.LastDate) end, @AsOfDate)
group by #InstallmentBalances.ID

--dbo.UFN_REMINDER_GETAMOUNTPAID

create table #AmountsPaid (ID uniqueidentifier Primary Key, AmountPaid money)
insert into #AmountsPaid (ID, AmountPaid)
select #LastReminderDates.ID, isnull(sum(INSTALLMENTSPLITPAYMENT.AMOUNT),0)
from #LastReminderDates inner join dbo.INSTALLMENTSPLITPAYMENT on #LastReminderDates.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTION.DATE >= isnull(#LastReminderDates.LastDate,FINANCIALTRANSACTION.DATE)
and FINANCIALTRANSACTION.DATE < @ASOFDATE
and #LastReminderDates.TransactionTypeCode != 2
group by #LastReminderDates.ID

--This one needs tested more

--UFN_REMINDER_GETPAIDTHROUGHDATE

create table #PaidThroughDates (ID uniqueidentifier Primary Key, PaidThroughDate datetime)
insert into #PaidThroughDates (ID, PaidThroughDate)
select ID, case when Sequence = 1 then null else InstallmentDate end
from
(select ID, InstallmentDate, Sequence, row_number() over (Partition by ID order by case Balance when 0 then 0 else SEQUENCE end, InstallmentDate desc) as rownum
from #InstallmentBalances) V1
where rownum = 1

--UFN_REMINDER_LASTPAYMENTDATE

create table #LastPaymentDates (ID uniqueidentifier Primary Key, LastPaymentDate datetime)
insert into #LastPaymentDates(ID, LastPaymentDate)
select #LastReminderDates.ID, max(FINANCIALTRANSACTION.DATE)
from #LastReminderDates inner join dbo.INSTALLMENTSPLITPAYMENT on #LastReminderDates.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTION.DATE >= isnull(#LastReminderDates.LastDate, FINANCIALTRANSACTION.DATE)
and FINANCIALTRANSACTION.DATE < @ASOFDATE
group by #LastReminderDates.ID

select #NextInstallmentDates.ID, #PaymentDue.AmountDue, LastDate, NextInstallmentDate, PastDueAmount, AmountPaid, PaidThroughDate, LastPaymentDate 
from #NextInstallmentDates
    left join #PaymentDue on #NextInstallmentDates.ID = #PaymentDue.ID
    left join #PledgePastDueAmounts on #PaymentDue.ID = #PledgePastDueAmounts.ID
    left join #AmountsPaid on #PaymentDue.ID = #AmountsPaid.ID
    left join #PaidThroughDates on #PaymentDue.ID = #PaidThroughDates.ID
    left join #LastPaymentDates on #PaymentDue.ID = #LastPaymentDates.ID
end