Armanino Blog
Article

GL transaction export script

October 30, 2008

I often have the request to get all the journal entries for a particular period of time. Usually this request comes from auditors. You can export the GL detail report but formatting is usually an issue. You can export all the transactions from a smartlist (account transactions) but that usually takes a long time if you want the data for a long date range, say 6 months to a year.

Here is a script I've used in the past to export the data straight from SQL. It includes the user who posted the transaction and the actual date it was posted on. You will have to change the bolded red date and year for your particular needs. You will also need to add GL00100.ACTNUMBR_X to include however many account segments you have in your GL account.

When running the query in SQL have the output results set to file.

Note this is for open year transactions. If you want data from a historical year, find and replace the table from GL20000 to GL30000 and OPENYEAR to HSTYEAR.

Open Year Query


SELECT GL20000.JRNENTRY AS Journal_Entry_Number, GL20000.SOURCDOC AS Module, GL20000.TRXDATE AS Date,

GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2,

GL00100.ACTNUMBR_3 AS Account_Segment3, GL20000.OPENYEAR AS Year,

GL00100.ACTDESCR AS Acct_Description, GL20000.DEBITAMT AS Debit, GL20000.CRDTAMNT AS Credit,

GL20000.TIME1 AS Time, GL20000.USWHPSTD AS User_Who_posted

FROM GL20000 INNER JOIN GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX

WHERE (GL20000.OPENYEAR = 'XXX') AND

(GL20000.TRXDATE => '11 / 30 / 2008') AND (GL20000.TRXDATE <= '12 / 31 / 2009')

Historical Year Query


SELECT gl30000.JRNENTRY AS Journal_Entry_Number, gl30000.SOURCDOC AS Module, gl30000.TRXDATE AS Date,

GL00100.ACTNUMBR_1 AS Account_Segment1, GL00100.ACTNUMBR_2 AS Account_Segment2,

GL00100.ACTNUMBR_3 AS Account_Segment3, gl30000.HSTYEAR AS Year,

GL00100.ACTDESCR AS Acct_Description, gl30000.DEBITAMT AS Debit, gl30000.CRDTAMNT AS Credit,

gl30000.TIME1 AS Time, gl30000.USWHPSTD AS User_Who_posted

FROM gl30000 INNER JOIN

GL00100 ON gl30000.ACTINDX = GL00100.ACTINDX

WHERE (gl30000.HSTYEAR = 2004) AND (gl30000.TRXDATE => '01 / 01 / 2004') AND

(gl30000.TRXDATE <= '12 / 31 / 2004')

Happy Exporting.

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Resources
Related News & Insights
General Contractor Trends to Consider in 2022 Webinar
Webinar
Hear from experts how you can better manage your subcontracts — and more.

December 16, 2021 | 10:00 AM - 11:00 AM PT
Women in Nonprofit Leadership Panel
Webinar
Aspiring nonprofit leaders should hear the insightful stories of these women industry leaders to empower their journeys.

December 14, 2021 | 10:00 AM - 11:00 AM PT
The Evolution of Gaming Companies
Webinar
From seed-funding to acquisition, position your gaming company for success.

December 9, 2021 | 01:00 PM - 02:00 PM PT