I'm working on pulling together some information for our annual audit. One of the pieces requested is what our CFO is referring to as an "Audit Report" -- effectively she wants to see every transaction that touched campaigns/funds and who in the system processed that transaction. I swear that there is a standard report that would give me this information, but I can't for the life of me remember what it is. Any thoughts/suggestions?
If you have SSMS, give this code I quickly scratched together a try (some tables may be different.) This code joins the contribution tables so if Finance wants ticketing as well, you will have to switch up to include the T_TICKET_HISTORY table.
I believe this will contain all of the information that your Finance department is looking for.
select cc.customer_no ,cu.lname ,c.description as campaign ,f.description as fund ,tr.transaction_no ,tr.batch_no ,tr.created_by ,tt.description as trn_type ,convert(varchar(10),tr.trn_dt,101) ,tr.trn_amtfrom lv_contcredit as cc (nolock) --you may also use T_CONTRIBUTION join t_transaction as tr (nolock) on cc.ref_no=tr.ref_no join t_campaign as c (nolock) on cc.campaign_no=c.campaign_no join t_fund as f (nolock) on cc.fund_no=f.fund_no join T_CUSTOMER as cu (nolock) on cc.customer_no=cu.customer_no join TR_TRANSACTION_TYPE as tt (nolock) on tr.trn_type=tt.idwhere tr.trn_dt between '2018-07-01' and '2019-06-30' --whatever your fiscal year dates are.
Mark! Thank you so much for this -- this got me almost exactly what I needed, and making the tweaks Finance needed was easy enough. They've only asked me for Contributions, but pulling in Ticket History shouldn't be hard if they change their mind.