How to connect a contribution to its proper GL code in SQL?

Hi Robert,

I may be wrong (someone feel free to correct me!) but I believe the Fund associated with the Contribution is the only link for GLs.

Martin

Yep the contribution GLs are fund based ... there can be 5 per GL (future, current, restricted etc)

Ah, that's what I was afraid of. So basically, I just need to figure out the "rules" (when a contribution should be assigned each one) and employ that logic in the SQL code itself so it assigns the correct GL code to each contribution.

Martin Keen (Past Member) said:

I believe the Fund associated with the Contribution is the only link for GLs.

Yes. Can't we get to the exact GL via T_TRANSACTION -> Fund + pmt method? 

So essentially, the payment method would be the deciding factor as to what GL the contribution should be assigned? Excuse me as I only know the SQL part of this, but not the accounting side, so wasn't sure how those 5 codes get assigned. 

Robert Brannen said:

So essentially, the payment method would be the deciding factor as to what GL the contribution should be assigned?

Sorry, I was incorrect above (never try to post from memory :-) Taking a look at T_TRANSACTION again I see it appears that Fund + trn_type is what I was thinking of. Several of the lowest ID, "required" transaction types in TR_TRANSACTION_TYPE correspond directly to the various Fund GLs.

Hi Robert,

As others have pointed out, contributions and their associated payments (as well as all other money moving around Tessitura) draw their GLs through the T_TRANSACTION table during the batch posting process. The actual GL activity is aggregated into T_GL_POSTING_HISTORY. It is one of the most "advanced" areas of the database to explore and report on. So, I caution that custom GL reporting can be fairly complex and comes with some risk, primarily because a single contribution can go through many transactions in its lifetime. The help topics on GL Account Tracking for Contributions, GL Accounts and Funds, GL Accounts for Standard Payment Methods, and others in that section explain these movements that you would need to account for.

We at the Network would strongly recommend creating custom financial reporting in the Finance Cube of the Analytics business intelligence tool. That data cube is based on the T_TRANSACTION table and handles the logic to connect transactions to their GLs and contributions. You can isolate contributions from the other types of transactions if desired. Many aspects of the contributions, constituents, batches and postings are brought into the cube, so detailed reporting is definitely possible.

Best,

-Michael Flaherty-Wilcox

Director of Practice Management, Tessitura Network 

And it depends whether or not you are looking for the GL reporting on the payment or GL reporting on the contribution.  E.g. a $500 contribution, when posted, will post a +$500 to the payment method GL and a -$500 to the contribution GL.  (Unless I flipped them.)

For the contribution side of things, I wrote a view for our organization that links Funds and Appeals to make reporting easier since Funds are assigned to Campaigns with start and end dates.

Michael Flaherty-Wilcox said:

As others have pointed out, contributions and their associated payments (as well as all other money moving around Tessitura) draw their GLs through the T_TRANSACTION table during the batch posting process. The actual GL activity is aggregated into T_GL_POSTING_HISTORY. It is one of the most "advanced" areas of the database to explore and report on.

Yes, "advanced" as in "frustrating"., i.e. T_GL_POSTING_HISTORY contains the summary data only, and the classic "Reprint Posting Report" queries that. Our Finance department repeatedly asked "what is the detail for this line?", and much custom reporting ensued, mostly years ago.

Michael Flaherty-Wilcox said:

Many aspects of the contributions, constituents, batches and postings are brought into the cube, so detailed reporting is definitely possible.

Kind of sad that we have to pull data that is in the impresario database, out of that database, in order to report on it. *sigh*

Hi Michael,

You know I would have sworn that I tried Analytics early on--it was the first place I went--and it didn't work. But now that I go back, it appears to be working for the most part. At least for transactions that are contributions. However, for tickets purchased, it does not display the GL Code (though it does show the transaction amount, performance name, etc. Do you know why that is?

Glad to hear! GL codes are derived during the posting process, so it's most likely that you are seeing transactions that haven't been posted in Tessitura yet. You can confirm this by looking at the Post ID or Post Date fields.

-Michael 

I understand what you mean. For what it's worth to others reading this thread, our usual response to your finance department's question is that you can run the Transactions by Posting Report for a given posting, and as the name implies, it will show you the transactions grouped under each GL in the posting. But sometimes this report doesn't contain fields that everyone needs. 

-Michael 

I see, so indeed they are definitely more recent transactions that are having this issue. Some of them don't have post numbers or dates which aligns with what you're saying, but some of them do. It is however exclusive to transactions processed in the last three or four days.

Ah OK. We have released a good number of defect fixes for the Finance cube in the last few service packs, so it's possible you are seeing an issue that would be resolved by applying those, but there could be something else at play here. I'm going to open a ticket on your behalf to see how we can resolve this. 

You have to be careful there because at the time of posting Tessitura creates an offsetting GL record to certain contribution transaction types. Ideally summing the records in t_Payment and  T_Transaction should net out to 0 but unfortunately they don't. which requires some creativity in report generation if you want your reports to match what goes up the the accounting system.

I wound up in creating my own function to get to the base entries and what accounts are needed for the offset:

    ,case   
        when trn_type in( 1,4,61,40) then dbo.LFN_MBA_Get_GL (40,T.fund_no,b.posted_dt)
        when trn_type in(2, 5,3,6,7,9) then dbo.LFN_MBA_Get_GL (42,T.fund_no,b.posted_dt)
        when trn_type in( 8) then dbo.LFN_MBA_Get_GL (43,T.fund_no,b.posted_dt)
        when trn_type in( 10) then dbo.LFN_MBA_Get_GL (41,T.fund_no,b.posted_dt)
        when trn_type in( 31,32,33) then dbo.LFN_MBA_Get_GL (30,pmap_no,b.posted_dt)
        when trn_type in( 51,52,53) then dbo.LFN_MBA_Get_GL (20,T.fee_no ,b.posted_dt)
        end as GLPrimary
    ,case   
        when trn_type in( 2,5,10) then dbo.LFN_MBA_Get_GL (40,T.fund_no,b.posted_dt)
        when trn_type in( 7) then dbo.LFN_MBA_Get_GL (44,T.fund_no,b.posted_dt)
        when trn_type in( 8) then dbo.LFN_MBA_Get_GL (42,T.fund_no,b.posted_dt)
        when trn_type in( 9) then dbo.LFN_MBA_Get_GL (43,T.fund_no,b.posted_dt)
        when trn_type in( 11) then dbo.LFN_MBA_Get_GL (41,T.fund_no,b.posted_dt)
    else ''
        end as GLOffset

Later on I do a second pass through the sub set of data creating entries using the value form the GLOffset column for the transaction types that need an offset entry created.