• Replies 17 replies
  • Subscribers 964 subscribers
  • Views 268 views
  • Users 0 members are here
Related

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

Robert Brannen
$organization

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?

Parents
  • $organization

    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.

Reply
  • $organization

    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.

Children
No Data