Payments from ref_no -- recursive CTE on T_TRANSACTION?

Nick,

I have a report that shows all payments by a chosen solicitor (for either contributions or orders) that I would be happy to share with you if you are interested in it, but it is pretty basic.  It does not sound like it would be what you are looking for as it just references all the payments currently associated with the performance/contribution, but maybe you could tweak it to fit your needs?

Let me know.

John

Yeah, I am specifically trying to cover cases when someone uses the same transaction to adjust two different contributions, since that transaction would not have a payment associated with it, and could involve moving money between the two contributions. Are you doing anything in your report to cover that case?

Yeah, I am specifically trying to cover cases when someone uses the same transaction to adjust two different contributions, since that transaction would not have a payment associated with it, and could involve moving money between the two contributions. Are you doing anything in your report to cover that case?

My report does not, unless an on account payment method is in use; it does track on account payments.

From time to time we have an issue with some staff members using the wrong payment methods for orders, and because we sell all the tickets for the consortium, unfortunately the staff members DO need to have access to those payment methods for other purposes, so we just needed a simple way to track the payments made by a given staff member so I wrote this up for that purpose.

Best of luck.

John

cases when someone uses the same transaction to adjust two different contributions, since that transaction would not have a payment associated

And I assume in those cases you want to include a dummy "payment method" to add to the other payments?

I spent a lot of time digging into this stuff for a couple of projects recently, but then had to dump most of it from my head to make space for the next set of projects.  I have a dim recollection that I deduced at the end of the day that these "direct transfers" were all reliably reflected in the contribution totals in T_CONTRIBUTION, and I didn't have to do any further digging.

This probably isn't helpful, but my experience, especially with contributions, is that there are a million things you can do with transactions on a contribution, and typically most of them you are not supposed to (based on local development business practice rules), and so often these really tricky multi-payment-transaction-contribution sets are vanishingly rare and represent a process mistake anyway, and whoever you're doing the reporting for would happily see them tossed in a bucket at the end for them to ignore.

The goal here is to list all of the payments that could have had an impact on a contribution ref_no, and for the sum of those those payments to always be equal or greater than the value of the contribution. (This report is for non-tessitura folk that work elsewhere at my org.) So on-account is fine since that's a payment that has a value, but otherwise I do want to know if it was a check or card or whatnot that was involved, even if part of that payment went to a different contribution, or to a ticket, etc.

Anyway, it took some figuring out, but I did the recursive CTE thing. Here it is if you want to check it out: https://bitbucket.org/snippets/TN_WebShare/bng5nG

> The goal here is to list all of the payments that could have had an impact on a contribution ref_no

I think I'd start with the code the client uses when you open a Contribution Detail window -> Transactions tab (e.g. please see below, via profiler trace).

All of the trns that apply to the contrib, and from there I think everything we'd need to get to any relevant payments is present.

SELECT T_TRANSACTION.trn_dt, TR_TRANSACTION_TYPE.description, T_TRANSACTION.trn_amt, T_TRANSACTION.posted_status, T_TRANSACTION.batch_no, T_TRANSACTION.transaction_no, T_TRANSACTION.source_no, T_CAMPAIGN.description, T_APPEAL.description, TR_MEDIA_TYPE.description, TX_APPEAL_MEDIA_TYPE.source_name, T_TRANSACTION.sequence_no, T_TRANSACTION.create_loc, T_TRANSACTION.created_by ,
h.description , post_no = CASE WHEN T_BATCH.status = 'P' and T_BATCH.posted_dt is not null THEN T_BATCH.post_no ELSE 0 END FROM T_TRANSACTION JOIN TR_TRANSACTION_TYPE ON T_TRANSACTION.trn_type = TR_TRANSACTION_TYPE.id JOIN T_CAMPAIGN ON T_CAMPAIGN.campaign_no = T_TRANSACTION.campaign_no JOIN TX_APPEAL_MEDIA_TYPE ON TX_APPEAL_MEDIA_TYPE.appeal_no = t_transaction.appeal_no and TX_APPEAL_MEDIA_TYPE.source_no = T_transaction.source_no JOIN T_APPEAL ON T_appeal.appeal_no = tx_appeal_media_type.appeal_no JOIN TR_MEDIA_TYPE ON TR_MEDIA_TYPE.id = T_TRANSACTION.media_type LEFT OUTER JOIN tr_transact_adj_reason h ON t_transaction.adj_reason = h.id LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
WHERE ( T_TRANSACTION.ref_no = 251409 )