I am building a custom report using SQL to better report on the finances around our contributions. Is there an easy identifier between T_CONTRIBUTION and gl_account_no other than using the T_FUND and then using a CASE statement to select the correct GL Code from there based on other factors?
Michael Flaherty-Wilcox I feel the need to jump in here with one important point that I think is a functionality gap in Tessitura that results from the ability to backdate postings. I submitted this as an enhancement request on ticket 76707 just over a year ago. TL;DR: We need to add a "wall clock" posting timestamp column to T_BATCH in order to be able to trace transactions to GLs.
The analysis is as follows:
---
T_GL_ACCOUNT_HIST stores a record of all GL configurations at every point in time. In theory, this could be useful for determining where a specific transaction was posted to by tracing from the transaction to the batch it occurred in, and then joining on T_GL_ACCOUNT_HIST using the batch's posted date (because the batch post date is the point at which a transaction is locked to a GL). However, if a batch posting is backdated, the posted_dt becomes an arbitrary user selection, and can no longer be used to trace the GL configuration at the time of posting. If an additional column (perhaps posted_timestamp?) was added to T_BATCH that stored a datetime value of the actual wall clock time that the batch was posted, this could be used to join on T_GL_ACCOUNT_HIST to definitively trace every transaction to the GL that it affected. For an example business case, I have previously needed to direct a batch of payments to different GLs from the standard GL for that payment method. So, I would change the GL on the payment method, do the posting (with a backdate), and then change the GL back. These GL changes appear in T_GL_ACCOUNT_HIST just seconds or minutes apart from each other. If the posting was timestamped, I could go back and reconstruct these payment GLs very easily, but since the posting was backdated, the posted_dt has nothing to do with the relevant start_dt and end_dt in T_GL_ACCOUNT_HIST.
There are currently a few canned reports in the system that attempt to do the impossible, which is to reconstruct which GL a transaction should be associated with based on the T_GL_ACCOUNT_HIST table. These are the Transactions by Posting report and the Performance Transaction Detail report.
In an environment where backdated postings are routine, it is very easy to get into a situation where these reports will NOT reconcile with something like the GL Summary Report. This can be frustrating because these reports are some of the only tools for digging into the detail behind the GL Summary report. The causing issue is that trying to match up a transaction to a GL number based on T_GL_ACCOUNT_HIST actually gives you no guarantees that the transaction was posted to the GL that was configured at the time the transaction was made.
Only the real time that the batch was posted determines when a transaction will be locked to a GL in T_GL_ACCOUNT_HIST. If it was not possible to backdate a posting, then the existing schema would work fine, but since backdating is possible, it is required to save the real time the batch was posted if the actual GL for a transaction is to be determined.
This behavior is noted in the documentation for the Transactions by Posting report as follows:
Note: If you have changed the GL on transactions in a batch and then backdate the post to a date prior to the GL change, the Transactions by Posting and the Performance Transaction Detail reports will reflect the old GL for transactions posted in the batch while the GL Summary report will reflect the changed GL for transactions posted in the batch.
And it's true that correcting for this just isn't possible without a schema change. But I suspect that it hasn't been recognized that fixing the schema would be as easy as adding a single column to T_BATCH, and that that would have such an outsized impact on our ability to do financial reporting.
Hi Nick! Thanks for this suggestion, both in your original ticket and here in the forums. I passed along the feedback on this thread to the Product and Development teams, and they are reviewing it for inclusion in a post-v16 release.