Querying GL JE?

Greetings,

I’ve received an audit request for all JEs from a given time period. Anyone have any recommendations on how to best query these out of the GL or possible have a DME they can share.

Thanks

Find more posts tagged with

Infor Lawson Financials Group - Discussion

Comments

amacknig

we give our auditors either a GL147 report, or a GL240 report.

unknown

[View:/cfs-file/__key/communityserver-discussions-components-files/59/AJEsForAuditors.txt:320:240]

Should be pretty basic query from mostly GLTRANS. Check with the auditors about exactly what JEs they want. Sometimes they only want the "manual" JEs - so you may be able to exclude AP, PR, IC, etc. Our auditors change their mind every couple of years about what they want. They also request the entire year so we wrote a simple Crystal report. I'm attaching here - you just need to change the file extension to .rpt and then update the datasource. It looks like they may not have requested this the last few years...

unknown

We have a similar request every year. We use SQL Server, so we have two Transact-SQL scripts. The first is the specific script, run once per company; the second is a summary that I use for a check against the individual extracts.

----------- FIRST SCRIPT ---------------------------------------------------

-- Script to create GL Data by company for Auditor - Jan to Sep 2017

USE LAWSON_PROD

GO

DECLARE @varCOMPANY int

DECLARE @varYEAR INT

SET @varYEAR = 2017

SET @varCOMPANY = 100 ---- 100, 200, 300, 400, 999 ---- Change company for each individual extract

SELECT GLT.SYSTEM, GLT.JE_TYPE, GLT.JE_SEQUENCE, GLT.CONTROL_GROUP, GLT.LINE_NBR, GLT.COMPANY, GLT.ACCT_UNIT, GLT.ACCOUNT,

GDT.ACCOUNT_DESC, GLT.UPDATE_DATE, GLT.ACCT_PERIOD, GLT.OPERATOR, GLT.TRAN_AMOUNT, GLT.DESCRIPTION,

GLT.POSTING_DATE, GLT.FISCAL_YEAR

FROM GLTRANS GLT, GLCHARTDTL GDT

WHERE (GLT.COMPANY in ( @varCOMPANY ) ) -- 100, 200, 300, 400, 999

AND (GLT.STATUS = 9) AND (GLT.TRAN_AMOUNT <> 0) AND (GLT.FISCAL_YEAR = @varYEAR)

AND (GLT.ACCT_PERIOD) < 10

-- AND (GLT.POSTING_DATE < '2017-10-01 00:00:00.000' and GLT.POSTING_DATE >= '2017-01-01 00:00:00.000' )

and GDT.ACCOUNT = GLT.ACCOUNT AND GDT.CHART_NAME = 'CHARTNAME'

AND GLT.ACCT_PERIOD <> 99

order by GLT.COMPANY, GLT.ACCT_PERIOD, GLT.SYSTEM, GLT.JE_TYPE, GLT.JE_SEQUENCE,

GLT.CONTROL_GROUP, GLT.LINE_NBR, GLT.POSTING_DATE

------------ SECOND SCRIPT - SUMMARY ----------------------------------------

-- CONTROL TOTALS:

-- Script to create GL Data Control Totals of companies for Auditor - Jan-Sept 2017

USE LAWSON_PROD

GO

DECLARE @varCOMPANY int

DECLARE @varYEAR INT

SET @varYEAR = 2017

SELECT SUM(GLT.TRAN_AMOUNT) 'Amt $', COUNT(*) 'Record Counts', GLT.COMPANY

FROM GLTRANS GLT, GLCHARTDTL GDT

WHERE (GLT.COMPANY in ( 100, 200, 300, 400, 999 ) ) AND (GLT.STATUS = 9)

AND (GLT.TRAN_AMOUNT > 0) AND (GLT.FISCAL_YEAR = @varYEAR)

and GLT.ACCT_PERIOD < 10

-- AND (GLT.POSTING_DATE < '2017-10-01 00:00:00.000' and GLT.POSTING_DATE >= '2017-01-01 00:00:00.000' )

AND GDT.ACCOUNT = GLT.ACCOUNT and GDT.CHART_NAME = 'CHARTNAME' and ACCT_PERIOD <> 99

Group by GLT.COMPANY order by GLT.COMPANY

----------------------------------- NOTES -----------------------------------------------

-> This is for Transact-SQL only, for SQL Server.

-> Adjust dates and/or periods as needed. Our fiscal year is the calendar year; your usage may differ.

-> GDT.CHART_NAME must be changed from 'CHARTNAME' to your chart of accounts name.

-> Each script contains a list of companies to be queried for. Update as you need to match your accounting.

-> Control Totals: Run twice for debits and credits. First for "GLT.TRAN_AMOUNT>0" (Debits) and the second run for "GLT.TRAN_AMOUNT<0" (Credits). These had better be equal!

-> A potential problem exists that the data is not clean. As shown here, the date parameters are suppressed (double-dash is the line comment indicator) and the GLT_ACCT_PERIOD is used. To check, the lines can be flipped: Comment out the Accounting Period line and un-comment the Posting Date line to use it. I find that it's fastest to do this with the Control Totals script, because if all is well here, I only have to run the company-by-company script once. This means that you have to run the Control Totals for four runs: Debits and credits for period-selected, then for date-selected. It's worth is for the peace of mind.

kiara-sonza

Mary Beth,

Can you forward me the Crystal report

stafol@childrens.com

Thank you so much,

Stacie

unknown

Stacie:

There's a link in my post above. You have to change the file extension from .txt to .rpt, and then change the datasource to match yours. The report is formatted for export (doesn't look pretty). Our files were in excess of 1 million lines and the add-ins didn't run as smoothly as Crystal.

kiara-sonza

Thank you

Have a great day

0911060553270289.pdf

Copyright © 2025 Infor. All rights reserved.