Rose Business Solutions Dynamics GP

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.

2 Responses to “GL transaction export script”