I have been asked by my auditors to extract all of the journal entries for 2005 for their analysis. I connected to the database and pulled all the records from GLTRANS and gave them to the auditors in excel. They complained that debits and credits do not tie out and they suspect that Lawson keeps some 'dummy' entries in this table that may be skewing their results. I poked around in the database and discovered the GLCONTROL table which appears to be a summary table.

After spending some time with the output from dbdoc for both tables, it is clear that there is a relationship between the tables that the Lawson documentation does not do a great job of explaining.

Does anyone have experience with extracting data from these tables? I would greatly appreciate any guidance provided on the mysteries of the Lawson data model. Thanks

Jon

Its been a while since I've dealt with the GL side of Lawson but I remember running into a similar issue. If I remember right, it had something to do with how Lawson posts reversed journal entries or recurring journal entries. What we had to do to figure out what was going on was narrow the issue. We isolated accounting units and accounts and compared the GLTRANS to the what was appearing in Lawson. It ended up being explainable and how to do with how Lawson processed the transactions, but I'm sorry that I can't remember all the details.

I wrote a script a few years back for my company to do the exact same thing. I pull the summary records from GLAMOUNT table, and the detail records from the GLTRANS table.

The trick here is DO NOT pull transactions/summary amount from the "Undistributed Retained Earnings" account. You can find what that account is from GL00.7 table. I don't remember exactly why, but it may have something to do with the account is a temperary holding account that is cleared out after each period and while it is not cleared out, it will mess up your credit/debit. Your problem could be related to the Undistributed Retained Earning account.

Fyi on a sample query to pull GLCONTROL summary and and related GLTRANS detail for Journal Entries...

select c.company,
c.fiscal_year,
c.acct_period,
c.r_system,
c.je_type,
c.control_group,
c.je_sequence,
c.entry_db,
c.entry_cr,
t.tran_amount
from YourDbSchema.gltrans t,
YourDbSchema.glcontrol c
where t.company = c.company
and t.fiscal_year = c.fiscal_year
and t.acct_period = c.acct_period
and t.r_system = c.r_system
and t.je_type = c.je_type
and t.control_group = c.control_group
and t.je_sequence = c.je_sequence
order by c.company,
c.fiscal_year,
c.acct_period,
c.r_system,
c.je_type,
c.control_group,
c.je_sequence,
c.entry_db,
c.entry_cr