Developer ToolsFree generatorPDF, Excel, Word exportInfor Lawson S3All Infor Lawson S3 tools

Infor Lawson S3 GLAMOUNTS Trial Balance SQL Generator

This generator writes SQL against the Infor Lawson S3 general ledger balance files GLAMOUNTS and GLUNITS, plus a tie-out query against GLTRANS. Enter company, fiscal year, period and an account range and paste the result into SQL Server Management Studio, SQL Developer or a DB2 client.

Fill in

Database

Used in the header comment. The SQL below is portable ANSI (CASE, derived tables, UNION ALL).

Schema / owner of the product line tables

Company

Fiscal year

Through period (two digits)

Builds the _NN column suffix and the year-to-date cut-off.

Account from

Account to

Reconcile: accounting unit

Reconcile: account

Reconcile: subaccount

Generated code

Download:

1. Net activity for one period

-- Infor Lawson S3 GL period activity (SQL Server)
-- Company 100, fiscal year 2025, period 06
-- Credits are stored as negative values, so DB + CR = net.
SELECT GA.COMPANY,
       GA.ACCT_UNIT,
       GA.ACCOUNT,
       GA.SUB_ACCOUNT,
       GA.DB_AMOUNT_06 AS PERIOD_DEBITS,
       GA.CR_AMOUNT_06 AS PERIOD_CREDITS,
       GA.DB_AMOUNT_06 + GA.CR_AMOUNT_06 AS PERIOD_NET
FROM LAWSON.GLAMOUNTS GA
WHERE GA.COMPANY = 100
  AND GA.FISCAL_YEAR = 2025
  AND GA.ACCOUNT BETWEEN 1000 AND 9999
  AND (GA.DB_AMOUNT_06 <> 0 OR GA.CR_AMOUNT_06 <> 0)
ORDER BY GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT;

2. Year-to-date trial balance through the selected period

-- Beginning balance + periods 01..06 = ending balance
SELECT T.COMPANY,
       T.ACCT_UNIT,
       T.ACCOUNT,
       T.SUB_ACCOUNT,
       T.BEG_BAL,
       T.YTD_ACTIVITY,
       T.BEG_BAL + T.YTD_ACTIVITY AS ENDING_BAL
FROM (
  SELECT GA.COMPANY,
         GA.ACCT_UNIT,
         GA.ACCOUNT,
         GA.SUB_ACCOUNT,
         SUM(GA.DB_BEG_BAL + GA.CR_BEG_BAL) AS BEG_BAL,
         SUM(GA.DB_AMOUNT_01 + GA.CR_AMOUNT_01
           + CASE WHEN 06 >= 2 THEN GA.DB_AMOUNT_02 + GA.CR_AMOUNT_02 ELSE 0 END
           + CASE WHEN 06 >= 3 THEN GA.DB_AMOUNT_03 + GA.CR_AMOUNT_03 ELSE 0 END
           + CASE WHEN 06 >= 4 THEN GA.DB_AMOUNT_04 + GA.CR_AMOUNT_04 ELSE 0 END
           + CASE WHEN 06 >= 5 THEN GA.DB_AMOUNT_05 + GA.CR_AMOUNT_05 ELSE 0 END
           + CASE WHEN 06 >= 6 THEN GA.DB_AMOUNT_06 + GA.CR_AMOUNT_06 ELSE 0 END
           + CASE WHEN 06 >= 7 THEN GA.DB_AMOUNT_07 + GA.CR_AMOUNT_07 ELSE 0 END
           + CASE WHEN 06 >= 8 THEN GA.DB_AMOUNT_08 + GA.CR_AMOUNT_08 ELSE 0 END
           + CASE WHEN 06 >= 9 THEN GA.DB_AMOUNT_09 + GA.CR_AMOUNT_09 ELSE 0 END
           + CASE WHEN 06 >= 10 THEN GA.DB_AMOUNT_10 + GA.CR_AMOUNT_10 ELSE 0 END
           + CASE WHEN 06 >= 11 THEN GA.DB_AMOUNT_11 + GA.CR_AMOUNT_11 ELSE 0 END
           + CASE WHEN 06 >= 12 THEN GA.DB_AMOUNT_12 + GA.CR_AMOUNT_12 ELSE 0 END
           + CASE WHEN 06 >= 13 THEN GA.DB_AMOUNT_13 + GA.CR_AMOUNT_13 ELSE 0 END
         ) AS YTD_ACTIVITY
  FROM LAWSON.GLAMOUNTS GA
  WHERE GA.COMPANY = 100
    AND GA.FISCAL_YEAR = 2025
    AND GA.ACCOUNT BETWEEN 1000 AND 9999
  GROUP BY GA.COMPANY, GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT
) T
ORDER BY T.ACCT_UNIT, T.ACCOUNT, T.SUB_ACCOUNT;

3. Add accounting unit and account descriptions

-- GLNAMES keyed by COMPANY + ACCT_UNIT
-- Chart name comes from the company record (GLSYSTEM.CHART_NAME)
-- GLCHARTDTL joined on CHART_NAME + ACCOUNT + SUB_ACCOUNT
SELECT GA.COMPANY,
       GA.ACCT_UNIT,
       GN.DESCRIPTION AS ACCT_UNIT_DESC,
       GA.ACCOUNT,
       GA.SUB_ACCOUNT,
       GD.ACCOUNT_DESC,
       GD.CHART_SECTION,
       GA.DB_BEG_BAL + GA.CR_BEG_BAL AS BEG_BAL,
       GA.DB_AMOUNT_06 + GA.CR_AMOUNT_06 AS PERIOD_NET
FROM LAWSON.GLAMOUNTS GA
LEFT JOIN LAWSON.GLNAMES GN
       ON GN.COMPANY = GA.COMPANY
      AND GN.ACCT_UNIT = GA.ACCT_UNIT
LEFT JOIN LAWSON.GLSYSTEM GS
       ON GS.COMPANY = GA.COMPANY
LEFT JOIN LAWSON.GLCHARTDTL GD
       ON GD.CHART_NAME = GS.CHART_NAME
      AND GD.ACCOUNT = GA.ACCOUNT
      AND GD.SUB_ACCOUNT = GA.SUB_ACCOUNT
WHERE GA.COMPANY = 100
  AND GA.FISCAL_YEAR = 2025
  AND GA.ACCOUNT BETWEEN 1000 AND 9999
ORDER BY GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT;

4. Reconcile one account to GLTRANS

-- Posted detail for the same account, periods 01..06
-- GLTRANS is indexed on TO_COMPANY (the company the line posts to).
-- STATUS 9 = posted history; lower values are not yet posted. Verify on your release.
SELECT GT.TO_COMPANY,
       GT.ACCT_UNIT,
       GT.ACCOUNT,
       GT.SUB_ACCOUNT,
       GT.ACCT_PERIOD,
       SUM(GT.BASE_AMOUNT) AS GLTRANS_NET,
       COUNT(*) AS LINES
FROM LAWSON.GLTRANS GT
WHERE GT.TO_COMPANY = 100
  AND GT.FISCAL_YEAR = 2025
  AND GT.ACCT_PERIOD <= 06
  AND GT.ACCT_UNIT = '1010'
  AND GT.ACCOUNT = 1100
  AND GT.SUB_ACCOUNT = 0
  AND GT.STATUS = 9
GROUP BY GT.TO_COMPANY, GT.ACCT_UNIT, GT.ACCOUNT, GT.SUB_ACCOUNT, GT.ACCT_PERIOD
ORDER BY GT.ACCT_PERIOD;

-- Compare the sum of GLTRANS_NET with YTD_ACTIVITY from query 2
-- for ACCT_UNIT '1010', ACCOUNT 1100, SUB_ACCOUNT 0.

5. Amounts and units side by side (GLUNITS)

-- Do not join GLAMOUNTS to GLUNITS; stack them with UNION ALL instead.
SELECT 'AMOUNT' AS VALUE_TYPE,
       GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT,
       GA.DB_BEG_BAL + GA.CR_BEG_BAL AS BEG_BAL,
       GA.DB_AMOUNT_06 + GA.CR_AMOUNT_06 AS PERIOD_NET
FROM LAWSON.GLAMOUNTS GA
WHERE GA.COMPANY = 100
  AND GA.FISCAL_YEAR = 2025
  AND GA.ACCOUNT BETWEEN 1000 AND 9999
UNION ALL
SELECT 'UNITS' AS VALUE_TYPE,
       GU.ACCT_UNIT, GU.ACCOUNT, GU.SUB_ACCOUNT,
       GU.DB_BEG_BAL + GU.CR_BEG_BAL AS BEG_BAL,
       GU.DB_UNITS_06 + GU.CR_UNITS_06 AS PERIOD_NET
FROM LAWSON.GLUNITS GU
WHERE GU.COMPANY = 100
  AND GU.FISCAL_YEAR = 2025
  AND GU.ACCOUNT BETWEEN 1000 AND 9999
ORDER BY 2, 3, 4, 1;

Column names follow the S3 SQL naming of the 4GL fields (GAM-DB-AMOUNT occurs 13 times, so DB_AMOUNT_01..DB_AMOUNT_13). Verify on your release: the GLTRANS STATUS value your site treats as posted, the GLUNITS column names (DB_UNITS_NN / CR_UNITS_NN and the beginning balance fields), that GLCHARTDTL is unique on CHART_NAME + ACCOUNT + SUB_ACCOUNT for your charts, and the schema or owner name for your product line. Run against a reporting copy, not production, during close.

Get your Lawson GL queries reviewed

Send us your product line, release and the report you are trying to tie out and we will check the joins and status filters against your data dictionary.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

Where the balances live in S3

GLAMOUNTS (General Ledger Amounts-Period) holds one row per company, fiscal year, accounting unit, account and subaccount (index GAMSET1). The 4GL fields GAM-DB-AMOUNT and GAM-CR-AMOUNT occur 13 times, one per period, and appear in SQL as DB_AMOUNT_01 to DB_AMOUNT_13 and CR_AMOUNT_01 to CR_AMOUNT_13. Opening balances sit in DB_BEG_BAL and CR_BEG_BAL. GLUNITS has the same key with DB_UNITS_NN and CR_UNITS_NN for statistical units. GLTRANS holds journal detail with ACCT_PERIOD, BASE_AMOUNT and STATUS. Descriptions come from GLNAMES (accounting units, keyed on COMPANY and ACCT_UNIT) and GLCHARTDTL (accounts, keyed on CHART_NAME, ACCOUNT and SUB_ACCOUNT). The chart a company uses is set on the company record (table GLSYSTEM), so query 3 reaches GLCHARTDTL through GLSYSTEM.CHART_NAME.

Period net = DB_AMOUNT_NN + CR_AMOUNT_NN (credits are negative)
Ending balance = DB_BEG_BAL + CR_BEG_BAL + periods 01..NN
GLTRANS is indexed on TO_COMPANY, not COMPANY
13 period buckets allow an adjustment period

How the year-to-date query works

SQL cannot loop over column names, so query 2 adds every period column through a CASE test against the period you pick. For period 06, buckets 01 to 06 are added and 07 to 13 contribute zero. This keeps one portable statement for SQL Server, Oracle and DB2 instead of dynamic SQL. The outer query adds the beginning balance to the activity to give the ending balance. For a whole company, the ending balances across all accounts should net to zero; if they do not, check whether the account range filter excluded some accounts or whether a period is still open with unposted journals.

Worked example: tie an account to GLTRANS

Run query 2 for company 100, fiscal year 2025, through period 06 and note YTD_ACTIVITY for accounting unit 1010, account 1100. Run query 4 with the same values. The sum of GLTRANS_NET across periods 01 to 06 should equal YTD_ACTIVITY. If GLTRANS is higher, check that the STATUS filter matches what your site treats as posted and look for an intercompany line where COMPANY and TO_COMPANY differ. If GLTRANS is lower, look for released journals that Journal Posting (GL190) has not posted yet, a purge of old GLTRANS detail, or balances loaded directly into GLAMOUNTS during conversion.

Common mistakes

The most frequent error is joining GLAMOUNTS to GLUNITS on a partial key, which multiplies rows, or with an inner join, which drops accounts that have money but no units. Stack them with UNION ALL as in query 5. Second, people subtract credits (DB - CR) and double the credit side because credits are already negative. Third, reading DB_AMOUNT_06 alone as the period-6 balance: it is activity, not a balance. Fourth, filtering GLTRANS on COMPANY when intercompany entries post to TO_COMPANY. Finally, filter GLTRANS on the posted status only, so unreleased or released journals are not mixed into a tie-out.

Frequently Asked Questions

What is Infor Lawson S3?

S3 (System Foundation, Supply chain, Services) is the classic Infor Lawson product line running on Lawson System Foundation (LSF), with applications such as General Ledger, AP, AR and HR built in Lawson 4GL. Data sits in a relational database (SQL Server, Oracle or DB2) in product line tables like GLAMOUNTS and GLTRANS.

Is there an Infor BI for Lawson alternative to writing SQL?

Many sites report GL through Infor BI or Lawson reporting tools that read the same tables. Direct SQL is still the fastest way to validate a report or reconcile a balance, and it is what a BI data model is built on. Use read-only access on a reporting copy.

Why are there 13 amount columns?

GAM-DB-AMOUNT and GAM-CR-AMOUNT occur 13 times so a fiscal calendar with 12 periods plus an adjustment period, or a 13-period calendar, fits in one row. Unused buckets stay zero, so a year-to-date query can safely add all 13 when you report a full year, and the generator's CASE tests stop at your chosen period.

Which GLTRANS STATUS values exist?

GLTRANS STATUS separates journals still being entered or released from posted history, and posted lines carry status 9, which is why query 4 filters STATUS 9. Open the GLTRANS file definition in your data dictionary (dbdef) to see the full value list for your release, and confirm with your GL team, since some sites have custom posting steps.

If you want finance and IT to ask Lawson GL questions in plain English on your own servers, with the SQL shown for every answer, we can set that up.

Talk to an Expert Explore More Free Tools