Raiser's Edge back-end database structure for GL Distributions

Suranjana Das

Hi,

I work on the Raiser's Edge Backend data to pull them into an ODS (SQL Server database) that is used for Reporting purposes. I need to develop a process to load the GL Account Distribution related data into the ODS. The gifts that I need to report on includes non-posted gifts. The Debit and Credit account numbers for the GL Gift Types are available in the GL Distributions of the Funds.


I was looking at the Raiser's Edge backend tables/views and it seems the table GIFTGLDISTRIBUTION stores GL Distributions only for a posted gift. Does anybody have experience in pulling the GL Distributions for the not posted gifts from the Raiser's Edge backend tables? Appreciate your help.


Thanks

James Andrews

Suranjana Das:

Hi,

I work on the Raiser's Edge Backend data to pull them into an ODS (SQL Server database) that is used for Reporting purposes. I need to develop a process to load the GL Account Distribution related data into the ODS. The gifts that I need to report on includes non-posted gifts. The Debit and Credit account numbers for the GL Gift Types are available in the GL Distributions of the Funds.


I was looking at the Raiser's Edge backend tables/views and it seems the table GIFTGLDISTRIBUTION stores GL Distributions only for a posted gift. Does anybody have experience in pulling the GL Distributions for the not posted gifts from the Raiser's Edge backend tables? Appreciate your help.


Thanks

I've done some work with that, but honestly it's pretty ugly and doesn't entirely work, because it relies on both the gift type and subtype, and some of our gifts don't have subtypes.  It's a subquery in my FROM clause. Sorry about my aliasing, it makes sense to me in the context of this query. I'm gonna paste it as is and you can take what you want from it. 


from gift og

    left join (

        select distinct

            ogldd.gldebit_number as ogdebit

            ,ogldd.glproject_number as ogproject

            ,ote1.longdescription as glgiftsubtype

            ,ogldd.glcredit_number as ogcredit

            ,ofd.fundid

            ,ofd.giftsubtypeid

                                    

         from funddistribution ofd 

            inner join gldistribution ogld on ofd.gldistributionid = ogld.distribution_id

            inner join gldistributiondetail ogldd on ogld.distribution_id = ogldd.distribution_id

            inner join tableentries ote1 on ofd.giftsubtypeid = ote1.TABLEENTRIESID

            inner join tableentries ote2 on ofd.gifttypeid = ote2.tableentriesid

         ) as oggl on ogf.id = oggl.fundid and oggl.giftsubtypeid = og.giftsubtype

Suranjana Das

James Andrews:

Suranjana Das:

Hi,

I work on the Raiser's Edge Backend data to pull them into an ODS (SQL Server database) that is used for Reporting purposes. I need to develop a process to load the GL Account Distribution related data into the ODS. The gifts that I need to report on includes non-posted gifts. The Debit and Credit account numbers for the GL Gift Types are available in the GL Distributions of the Funds.


I was looking at the Raiser's Edge backend tables/views and it seems the table GIFTGLDISTRIBUTION stores GL Distributions only for a posted gift. Does anybody have experience in pulling the GL Distributions for the not posted gifts from the Raiser's Edge backend tables? Appreciate your help.


Thanks

I've done some work with that, but honestly it's pretty ugly and doesn't entirely work, because it relies on both the gift type and subtype, and some of our gifts don't have subtypes.  It's a subquery in my FROM clause. Sorry about my aliasing, it makes sense to me in the context of this query. I'm gonna paste it as is and you can take what you want from it. 


from gift og

    left join (

        select distinct

            ogldd.gldebit_number as ogdebit

            ,ogldd.glproject_number as ogproject

            ,ote1.longdescription as glgiftsubtype

            ,ogldd.glcredit_number as ogcredit

            ,ofd.fundid

            ,ofd.giftsubtypeid

                                    

         from funddistribution ofd 

            inner join gldistribution ogld on ofd.gldistributionid = ogld.distribution_id

            inner join gldistributiondetail ogldd on ogld.distribution_id = ogldd.distribution_id

            inner join tableentries ote1 on ofd.giftsubtypeid = ote1.TABLEENTRIESID

            inner join tableentries ote2 on ofd.gifttypeid = ote2.tableentriesid

         ) as oggl on ogf.id = oggl.fundid and oggl.giftsubtypeid = og.giftsubtype

 

Hi James, Thank you so much for providing the query. This is really helpful. I will try to modify this according to our requirements and hopefully I will have the code logic for obtaining the GL Distribution Accounts.


Thanks.