• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Financial Management Lawson S3 Financials

Chart of Acct query

 5 Replies
 0 Subscribed to this topic
 43 Subscribed to this forum
Sort:
Author
Messages
Don
New Member Send Private Message
Posts: 2
New Member
5/4/2012 2:41 PM
Hi There,

I am totally new to lawson. I just start to look at the tables on GLs, and try to build chart of account hierachy, for top to detail GL. I look at two tables, GLCHARTDTL and GLCHARTSUM and I knnow ChartName, so here is my query:

SELECT S.CHART_NAME, D.SUMRY_ACCT_ID, D.CHART_SECTION, D.ACCOUNT, D.SUB_ACCOUNT,D.ACCOUNT_DESC, S.SUMMARY_ACCT,S.ACCOUNT_DESC, S.TOTAL_DESC,S.PARENT_OBJ_ID
FROM dbo.GLCHARTDTL D
inner JOIN GLCHARTSUM S ON D.SUMRY_ACCT_ID = S.SUMRY_ACCT_ID
--WHERE D.CHART_NAME='CHART' AND D.SUMRY_ACCT_ID=56
GROUP BY S.CHART_NAME, D.SUMRY_ACCT_ID, D.CHART_SECTION, D.ACCOUNT, D.SUB_ACCOUNT,D.ACCOUNT_DESC, S.SUMMARY_ACCT, S.ACCOUNT_DESC,S.TOTAL_DESC,S.PARENT_OBJ_ID
HAVING S.CHART_NAME='CHART' AND S.CHART_SECTION=2

But this is not giving me the result I expect, Do I use the right tables?

Thanks
Gail Gudgel
Basic Member Send Private Message
Posts: 14
Basic Member
5/4/2012 8:56 PM
Don, would a GL200 provide what you need? that's Lawson's report for the Chart of Accounts Listing.
Don
New Member Send Private Message
Posts: 2
New Member
5/7/2012 12:08 PM
Gail,

Thanks for reply. But I don't see GL200 table in our lawson database, in fact, there is no tables name with number after GL, they are like GLTRANS, GLSYSJE, etc.

Do I miss anything?
John Henley
Send Private Message
Posts: 3351
5/7/2012 12:41 PM
GL200 is a batch job, not a table.
Thanks for using the LawsonGuru.com forums!
John
TimF
Basic Member Send Private Message
Posts: 4
Basic Member
5/7/2012 1:18 PM
The GL200 utilized the tables GLCHARTSUM and GLCHARTDTL for summary and detail information respectively.

From: forums-s3-financials@lawsonguru.com [mailto:forums-s3-financials@lawsonguru.com]
Sent: Monday, May 07, 2012 8:22 AM
To: Ferguson, Tim
Subject: RE: Chart of Acct query [00009767:00018832]

[LawsonGuru.com Logo]<https://www.lawsonguru.com/>
Lawson S3 Financials Forum Notification
A message was posted to a thread you are tracking.
RE: Chart of Acct query

Posted by: Don
05/07/2012 07:08 AM

Gail,

Thanks for reply. But I don't see GL200 table in our lawson database, in fact, there is no tables name with number after GL, they are like GLTRANS, GLSYSJE, etc.

Do I miss anything?

________________________________

To view the complete thread and reply via your browser, please visit:
https://www.lawsonguru.co...chart-of-acct-query/

You were sent this email because you opted to receive email notifications when someone posted and/or responded to a message on this forum.
To unsubscribe to this thread please visit your user profile page and change your subscription options.

Thank you,
LawsonGuru.com
amynelson
Advanced Member Send Private Message
Posts: 25
Advanced Member
5/7/2012 2:50 PM
Try something like this query:


SELECT
GDT.ACCOUNT,
GDT.ACCOUNT_DESC,
GCS.SUMMARY_ACCT AS LVL1,
GCS.ACCOUNT_DESC AS LVL1_DESC,
GCS.SUMRY_ACCT_ID AS LVL1_NUM,
GCSb.SUMMARY_ACCT AS LVL2,
GCSb.ACCOUNT_DESC AS LVL2_DESC,
GCSb.SUMRY_ACCT_ID AS LVL2_NUM,
GCSc.SUMMARY_ACCT AS LVL3,
GCSc.ACCOUNT_DESC AS LVL3_DESC,
GCSc.SUMRY_ACCT_ID AS LVL3_NUM,
GCSd.SUMMARY_ACCT AS LVL4,
GCSd.ACCOUNT_DESC AS LVL4_DESC,
GCSd.SUMRY_ACCT_ID AS LVL4_NUM
FROM lawdbo.GLCHARTDTL GDT
INNER JOIN lawdbo.GLCHARTSUM GCS ON GDT.CHART_NAME=GCS.CHART_NAME AND GDT.SUMRY_ACCT_ID=GCS.SUMRY_ACCT_ID
LEFT OUTER JOIN lawdbo.GLCHARTSUM GCSb ON GCS.PARENT_OBJ_ID=GCSb.SUMRY_ACCT_ID
LEFT OUTER JOIN lawdbo.GLCHARTSUM GCSc ON GCSb.PARENT_OBJ_ID=GCSc.SUMRY_ACCT_ID
LEFT OUTER JOIN lawdbo.GLCHARTSUM GCSd ON GCSc.PARENT_OBJ_ID=GCSd.SUMRY_ACCT_ID
LEFT OUTER JOIN lawdbo.GLCHARTSUM GCSe ON GCSd.PARENT_OBJ_ID=GCSe.SUMRY_ACCT_ID

You'll have to join to GLCHARTSUM as many times as needed to incorporate all the levels of your chart.
Hope this helps,
Amy