# Infor Lawson S3 GLAMOUNTS Trial Balance SQL Generator

> Source: https://netray.co/tools/infor-lawson-s3-glamounts-trial-balance-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in company, fiscal year and period to get ready-to-run SQL for Lawson S3 GL balances from GLAMOUNTS and GLUNITS, with a GLTRANS reconciliation query.

## Category

developer

## Erp

Infor Lawson S3

## Audience

- Lawson developer
- Lawson DBA
- Finance systems analyst
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Database

**Key:** dialect

**Type:** select

**Options:**

- SQL Server
- Oracle
- IBM DB2

**Default:** SQL Server

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

#### Schema / owner of the product line tables

**Key:** schema

**Type:** text

**Default:** LAWSON

#### Company

**Key:** co

**Type:** number

**Default:** 100

#### Fiscal year

**Key:** fy

**Type:** number

**Default:** 2025

#### Through period (two digits)

**Key:** period

**Type:** select

**Options:**

- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13

**Default:** 06

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

#### Account from

**Key:** acct_from

**Type:** number

**Default:** 1000

#### Account to

**Key:** acct_to

**Type:** number

**Default:** 9999

#### Reconcile: accounting unit

**Key:** recon_au

**Type:** text

**Default:** 1010

#### Reconcile: account

**Key:** recon_acct

**Type:** number

**Default:** 1100

#### Reconcile: subaccount

**Key:** recon_sub

**Type:** number

**Default:** 0

**Blocks:**

#### 1. Net activity for one period

**Body:** -- Infor Lawson S3 GL period activity ({{dialect}})
-- Company {{co}}, fiscal year {{fy}}, period {{period}}
-- Credits are stored as negative values, so DB + CR = net.
SELECT GA.COMPANY,
       GA.ACCT_UNIT,
       GA.ACCOUNT,
       GA.SUB_ACCOUNT,
       GA.DB_AMOUNT_{{period}} AS PERIOD_DEBITS,
       GA.CR_AMOUNT_{{period}} AS PERIOD_CREDITS,
       GA.DB_AMOUNT_{{period}} + GA.CR_AMOUNT_{{period}} AS PERIOD_NET
FROM {{schema}}.GLAMOUNTS GA
WHERE GA.COMPANY = {{co}}
  AND GA.FISCAL_YEAR = {{fy}}
  AND GA.ACCOUNT BETWEEN {{acct_from}} AND {{acct_to}}
  AND (GA.DB_AMOUNT_{{period}} <> 0 OR GA.CR_AMOUNT_{{period}} <> 0)
ORDER BY GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT;

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

**Body:** -- Beginning balance + periods 01..{{period}} = 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 {{period}} >= 2 THEN GA.DB_AMOUNT_02 + GA.CR_AMOUNT_02 ELSE 0 END
           + CASE WHEN {{period}} >= 3 THEN GA.DB_AMOUNT_03 + GA.CR_AMOUNT_03 ELSE 0 END
           + CASE WHEN {{period}} >= 4 THEN GA.DB_AMOUNT_04 + GA.CR_AMOUNT_04 ELSE 0 END
           + CASE WHEN {{period}} >= 5 THEN GA.DB_AMOUNT_05 + GA.CR_AMOUNT_05 ELSE 0 END
           + CASE WHEN {{period}} >= 6 THEN GA.DB_AMOUNT_06 + GA.CR_AMOUNT_06 ELSE 0 END
           + CASE WHEN {{period}} >= 7 THEN GA.DB_AMOUNT_07 + GA.CR_AMOUNT_07 ELSE 0 END
           + CASE WHEN {{period}} >= 8 THEN GA.DB_AMOUNT_08 + GA.CR_AMOUNT_08 ELSE 0 END
           + CASE WHEN {{period}} >= 9 THEN GA.DB_AMOUNT_09 + GA.CR_AMOUNT_09 ELSE 0 END
           + CASE WHEN {{period}} >= 10 THEN GA.DB_AMOUNT_10 + GA.CR_AMOUNT_10 ELSE 0 END
           + CASE WHEN {{period}} >= 11 THEN GA.DB_AMOUNT_11 + GA.CR_AMOUNT_11 ELSE 0 END
           + CASE WHEN {{period}} >= 12 THEN GA.DB_AMOUNT_12 + GA.CR_AMOUNT_12 ELSE 0 END
           + CASE WHEN {{period}} >= 13 THEN GA.DB_AMOUNT_13 + GA.CR_AMOUNT_13 ELSE 0 END
         ) AS YTD_ACTIVITY
  FROM {{schema}}.GLAMOUNTS GA
  WHERE GA.COMPANY = {{co}}
    AND GA.FISCAL_YEAR = {{fy}}
    AND GA.ACCOUNT BETWEEN {{acct_from}} AND {{acct_to}}
  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

**Body:** -- 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_{{period}} + GA.CR_AMOUNT_{{period}} AS PERIOD_NET
FROM {{schema}}.GLAMOUNTS GA
LEFT JOIN {{schema}}.GLNAMES GN
       ON GN.COMPANY = GA.COMPANY
      AND GN.ACCT_UNIT = GA.ACCT_UNIT
LEFT JOIN {{schema}}.GLSYSTEM GS
       ON GS.COMPANY = GA.COMPANY
LEFT JOIN {{schema}}.GLCHARTDTL GD
       ON GD.CHART_NAME = GS.CHART_NAME
      AND GD.ACCOUNT = GA.ACCOUNT
      AND GD.SUB_ACCOUNT = GA.SUB_ACCOUNT
WHERE GA.COMPANY = {{co}}
  AND GA.FISCAL_YEAR = {{fy}}
  AND GA.ACCOUNT BETWEEN {{acct_from}} AND {{acct_to}}
ORDER BY GA.ACCT_UNIT, GA.ACCOUNT, GA.SUB_ACCOUNT;

#### 4. Reconcile one account to GLTRANS

**Body:** -- Posted detail for the same account, periods 01..{{period}}
-- 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 {{schema}}.GLTRANS GT
WHERE GT.TO_COMPANY = {{co}}
  AND GT.FISCAL_YEAR = {{fy}}
  AND GT.ACCT_PERIOD <= {{period}}
  AND GT.ACCT_UNIT = '{{recon_au}}'
  AND GT.ACCOUNT = {{recon_acct}}
  AND GT.SUB_ACCOUNT = {{recon_sub}}
  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 '{{recon_au}}', ACCOUNT {{recon_acct}}, SUB_ACCOUNT {{recon_sub}}.

#### 5. Amounts and units side by side (GLUNITS)

**Body:** -- 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_{{period}} + GA.CR_AMOUNT_{{period}} AS PERIOD_NET
FROM {{schema}}.GLAMOUNTS GA
WHERE GA.COMPANY = {{co}}
  AND GA.FISCAL_YEAR = {{fy}}
  AND GA.ACCOUNT BETWEEN {{acct_from}} AND {{acct_to}}
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_{{period}} + GU.CR_UNITS_{{period}} AS PERIOD_NET
FROM {{schema}}.GLUNITS GU
WHERE GU.COMPANY = {{co}}
  AND GU.FISCAL_YEAR = {{fy}}
  AND GU.ACCOUNT BETWEEN {{acct_from}} AND {{acct_to}}
ORDER BY 2, 3, 4, 1;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### Where the balances live in S3

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What is Infor Lawson S3?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

infor lawson s3, lawson glamounts sql, lawson trial balance query, lawson gltrans query, infor bi for lawson, infor lawson report listing, lawson glunits, what is infor s3

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json