Payments and Schedules

Hi Jan,

T_SCHEDULE has a transaction_no field, but I bet you have already discovered that it is not going to be useful because it appears to only have recorded the first transaction and none of the follow up payments.  This means there isn't a link between the schedule and the payments, so I think we will have to link the schedule to the contribution and then also link the payments to the contribution.

Joining both the schedule and the payments to the contribution in the same select statement will cause a multiplication of rows returned.  Maybe that is what you want, or maybe you have a way to deal with that.  What I would do instead of trying to get everything all at once is to grab each piece individually--each piece being the contribution info, the schedule info, and the payment info--and then I would union them together in a manner that would let me group them by distinct contribution and information type.

For example, here is one way to do it using Common Table Expressions for the three segments.

--------------------------------------------------------------------------------------

;WITH contributions AS
(	Select
	a.customer_no
	,a.ref_no
	,a.cont_dt
	,a.cont_amt
From
	dbo.VS_CONTRIBUTION a
Where
	a.ref_no in (3772177)
)
, contributions_schedule AS
(	Select
	a.customer_no
	,a.ref_no
	,b.due_dt
	,b.amt_due
From
	contributions a
	JOIN dbo.T_SCHEDULE b on a.ref_no = b.ref_no
)
, contributions_payments AS
(	Select
	a.customer_no
	,a.ref_no
	,d.pmt_dt
	,d.pmt_amt
From
	contributions a
	JOIN dbo.T_TRANSACTION c on a.ref_no = c.ref_no
	JOIN dbo.T_PAYMENT d on c.transaction_no = d.transaction_no and c.sequence_no = d.sequence_no
)
Select
	a.customer_no
	,a.ref_no
	,[grouping] = 'Contribution info'
	,[date]		= a.cont_dt
	,[amount]	= a.cont_amt
From
	contributions a
UNION
Select
	a.customer_no
	,a.ref_no
	,[grouping] = 'Schedule info'
	,[date]		= a.due_dt
	,[amount]	= a.amt_due
From
	contributions_schedule a
UNION
Select
	a.customer_no
	,a.ref_no
	,[grouping] = 'Payment info'
	,[date]		= a.pmt_dt
	,[amount]	= a.pmt_amt
From
	contributions_payments a

-------------------------------------------------------------------------

This would produce results that look like this:

----------------------------------------------------------------------

customer_no ref_no      grouping          date                    amount
----------- ----------- ----------------- ----------------------- ---------------------
37587       3772177     Contribution info 2015-10-20 00:00:00.000 500.00
37587       3772177     Payment info      2015-10-21 10:39:55.000 50.00
37587       3772177     Payment info      2015-11-16 13:18:41.930 50.00
37587       3772177     Payment info      2015-12-15 09:20:15.860 50.00
37587       3772177     Payment info      2016-01-15 09:20:16.720 50.00
37587       3772177     Payment info      2016-02-16 04:18:09.927 50.00
37587       3772177     Schedule info     2015-10-21 00:00:00.000 50.00
37587       3772177     Schedule info     2015-11-15 00:00:00.000 50.00
37587       3772177     Schedule info     2015-12-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-01-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-02-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-03-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-04-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-05-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-06-15 00:00:00.000 50.00
37587       3772177     Schedule info     2016-07-15 00:00:00.000 50.00

(16 row(s) affected)

-------------------------------------------------------------------------

This contribution has a schedule with 10 installments.  There have been 5 payments made.  I added one row for the contribution info and added all together we get 16 rows.

If I had tried to select the data in one statement while joining schedule to contribution and then joining payments to contribution I would have multiplied the returns.  Five payments times ten schedule installments would have given us a total of 50 rows.

I'm guessing the return of 16 rows is closer to what you are looking for.  This way when you format your presentation layer in either InfoMaker or SSRS you can group first by customer_no/ref_no and then under that group by data segment (what I called "grouping" in the example) to show more clearly what is scheduled and what is paid and then add in subtotals if desired.

Good luck.  This report is more difficult than it appears on the surface.

Jared

T_SCHEDULE has a transaction_no field, but I bet you have already discovered that it is not going to be useful because it appears to only have recorded the first transaction and none of the follow up payments.  This means there isn't a link between the schedule and the payments, so I think we will have to link the schedule to the contribution and then also link the payments to the contribution.

Just looking around to see if there was anything I was missing here.  Is there really no connection between schedules and the actual payments?  How does a schedule get updated if a larger payment is made?  This is going to be a big nuisance for a data sharing project I'm currently working on.

The schedule doesn't get updated.  Unless, of course, a user goes in and deliberately makes a change, which they always can.

The billing utility simply makes a comparison of how much has been paid on the contribution (or order) and the amount that should have been paid by that date by summing up the amounts on the schedule that have installment dates which have passed.  The dollar amount of the difference, if greater than zero, is identified as the current amount due and is then processed by the billing utility.

Transactions are processed against the contribution (or order) and not the schedule.  This is vital for the finance records having a credit and debit side with GL numbers.  The payment method is on one side and the fund (or ticket or fee) is on the other side.

The purpose of the billing schedule is to create a statement of where the value of the amount paid should be by a certain time, and the billing utility is used to bring that amount up to date.

Jared

Wouldn't something like the following work?

SELECT DISTINCT T_CONTRIBUTION.cont_dt, T_SCHEDULE.sch_no, T_SCHEDULE.due_dt, T_PAYMENT.pmt_dt

FROM T_TRANSACTION INNER JOIN
T_SCHEDULE ON T_TRANSACTION.ref_no = T_SCHEDULE.ref_no INNER JOIN
T_PAYMENT ON T_TRANSACTION.transaction_no = T_PAYMENT.transaction_no AND T_TRANSACTION.sequence_no = T_PAYMENT.sequence_no INNER JOIN
T_CONTRIBUTION ON T_TRANSACTION.ref_no = T_CONTRIBUTION.ref_no