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
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
    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
      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
        Posts: 3353
        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
          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
            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