Other ERPsFree referencePDF, Excel, Word exportInfor Lawson S3All Infor Lawson S3 tools

Infor Lawson S3 GL and AP Table Reference

This is a searchable reference to the Lawson S3 General Ledger, Accounts Payable, Cash Ledger and matching tables that report writers and developers use most, checked against the Lawson Applications 9.0 data dictionary. Each row gives the field prefix, the primary index and its keys, the columns used most in reports and the usual join.

Download:

Table	System	Description	Field prefix	Primary index and keys	Key reporting columns	Common join	SQL column example
GLTRANS	GL	General Ledger Transactions	GLT-	GLTSET1: COMPANY, FISCAL-YEAR, ACCT-PERIOD, CONTROL-GROUP, SYSTEM, JE-TYPE, JE-SEQUENCE, LINE-NBR	ACCT-UNIT, ACCOUNT, SUB-ACCOUNT, BASE-AMOUNT, POSTING-DATE, STATUS, SOURCE-CODE, DESCRIPTION	GLCONTROL on COMPANY, FISCAL-YEAR, ACCT-PERIOD, CONTROL-GROUP, SYSTEM, JE-TYPE, JE-SEQUENCE	BASE_AMOUNT
GLAMOUNTS	GL	General Ledger Amounts (beginning and period balances)	GAM-	GAMSET1: COMPANY, FISCAL-YEAR, ACCT-UNIT, ACCOUNT, SUB-ACCOUNT	DB-BEG-BAL, CR-BEG-BAL, DB-AMOUNT and CR-AMOUNT (occurs 13), CHART-NAME	GLMASTER on COMPANY, ACCT-UNIT, ACCOUNT, SUB-ACCOUNT	DB_AMOUNT_01 .. DB_AMOUNT_13
GLUNITS	GL	General Ledger Units (statistical balances)	GLU-	GLUSET1: COMPANY, FISCAL-YEAR, ACCT-UNIT, ACCOUNT, SUB-ACCOUNT	DB-BEG-BAL, CR-BEG-BAL, DB-UNITS, CR-UNITS	GLAMOUNTS on the same five keys	DB_UNITS_01
GLMASTER	GL	General Ledger Account Master	GLM-	GLMSET1: COMPANY, ACCOUNT, SUB-ACCOUNT, VAR-LEVELS, ACCT-UNIT	ACTIVE-STATUS, POSTING-LEVEL, CHART-NAME, CURRENCY-CODE	GLNAMES on COMPANY, ACCT-UNIT	ACTIVE_STATUS
GLNAMES	GL	General Ledger Names (accounting units)	GLN-	GLNSET1: COMPANY, ACCT-UNIT	DESCRIPTION, VAR-LEVELS, LEVEL-DEPTH, POSTING-FLAG, ACTIVE-STATUS	GLTRANS or GLAMOUNTS on COMPANY, ACCT-UNIT	LEVEL_DEPTH
GLCHARTDTL	GL	General Ledger Chart Detail (accounts in a chart)	GDT-	GDTSET1: CHART-NAME, SUMRY-ACCT-ID, ACCOUNT, SUB-ACCOUNT	ACCOUNT-DESC, CHART-SECTION, POSTING-LEVEL, ACTIVE-STATUS, ACCT-CATEGORY	GLSYSTEM.CHART-NAME, then ACCOUNT, SUB-ACCOUNT	ACCOUNT_DESC
GLCHART	GL	General Ledger Chart	GCH-	GCHSET1: CHART-NAME	DESCRIPTION, RE-ACCOUNT, RE-SUB-ACCT, SEGMENT-GROUP	GLCHARTDTL on CHART-NAME	RE_ACCOUNT
GLSYSTEM	GL	General Ledger Company	GLS-	GLSSET1: COMPANY	NAME, CHART-NAME, FISCAL-YEAR, ACCT-PERIOD, NBR-ACCT-PERS, CURRENCY-CODE	Any GL table on COMPANY	NBR_ACCT_PERS
GLCONTROL	GL	General Ledger Control (journal entry headers)	GLC-	GLCSET1: COMPANY, FISCAL-YEAR, ACCT-PERIOD, SYSTEM, JE-TYPE, CONTROL-GROUP, JE-SEQUENCE	STATUS, DESCRIPTION, POSTING-DATE, OPERATOR, SOURCE-CODE, REFERENCE	GLTRANS on the journal keys	CONTROL_GROUP
GLCODES	GL	General Ledger Codes (system codes, source codes, periods)	GCD-	GCDSET1: RECORD-TYPE, FOLDER-NAME, PROCESS-GROUP, SYSTEM, COMPANY	SOURCE-CODE, DESCRIPTION, OPEN-PERIODS	GLTRANS on SOURCE-CODE (via GCDSET3)	SOURCE_CODE
GLCGCPY	GL	Company Group Company	GCC-	GCCSET1: COMPANY, COMPANY-GROUP	COMPANY-GROUP, SEQ-NBR	GLSYSTEM on COMPANY	COMPANY_GROUP
APVENMAST	AP	Vendor	VEN-	VENSET1: VENDOR-GROUP, VENDOR	VENDOR-VNAME, VENDOR-SNAME, VEN-CLASS, VENDOR-STATUS, TAX-ID, TERM-CODE	APINVOICE on VENDOR-GROUP, VENDOR	VENDOR_VNAME
APVENLOC	AP	Vendor Location	VLO-	VLOSET1: VENDOR-GROUP, VENDOR, LOCATION-CODE	LOC-TYPE, VENDOR-VNAME, E-MAIL-ADDRESS, ACTIVE-STATUS	APVENMAST on VENDOR-GROUP, VENDOR	LOCATION_CODE
APVENCLASS	AP	Vendor Class	VCL-	VCLSET1: VENDOR-GROUP, VEN-CLASS	DESCRIPTION, CASH-CODE, REQUIRE-PO	APVENMAST on VENDOR-GROUP, VEN-CLASS	VEN_CLASS
APCOMPANY	AP	Accounts Payable Company	CPY-	CPYSET1: COMPANY	NAME, VENDOR-GROUP, PAY-GROUP, BASE-CURRENCY, CASH-CODE	APINVOICE on COMPANY	VENDOR_GROUP
APINVOICE	AP	Accounts Payable Invoice	API-	APISET1: COMPANY, VENDOR, INVOICE, SUFFIX, CANCEL-SEQ	INVOICE-DTE, DUE-DATE, PO-NUMBER, BASE-INV-AMT, REC-STATUS, VOUCHER-NBR, OBJ-ID	APVENMAST on VENDOR-GROUP, VENDOR	BASE_INV_AMT
APDISTRIB	AP	Invoice Distribution	APD-	APDSET1: API-OBJ-ID, DIST-SEQ-NBR	DIST-COMPANY, DIS-ACCT-UNIT, DIS-ACCOUNT, DIS-SUB-ACCT, ORIG-BASE-AMT, DISTRIB-DATE	APINVOICE on API-OBJ-ID = API-OBJ-ID, or COMPANY, VENDOR, INVOICE, SUFFIX, CANCEL-SEQ	DIS_ACCOUNT
APPAYMENT	AP	Invoice Payment	APP-	APPSET1: COMPANY, VENDOR, INVOICE, SUFFIX, CANCEL-SEQ, SEQ-NBR, VOID-SEQ	DUE-DATE, BASE-PMT-AMT, CHECK-DATE, TRANS-NBR, BANK-INST-CODE, CASH-CODE, REC-STATUS	APINVOICE on COMPANY, VENDOR, INVOICE, SUFFIX, CANCEL-SEQ	BASE_PMT_AMT
CBCHECK	CB	Accounts Payable Cash Payment (checks and payments)	CHK-	CHKSET1: CASH-CODE, BANK-INST-CODE, TRANS-IDENT	TRANS-NBR, CHECK-DATE, PAID-VENDOR, PAID-NAME, BASE-PMT-AMT, REC-STATUS	APPAYMENT on CASH-CODE, BANK-INST-CODE, TRANS-IDENT	PAID_VENDOR
MAINVDTL	MA	Matching Invoice Detail (PO match lines)	MAD-	MADSET1: COMPANY, VENDOR, INVOICE, SUFFIX, PO-NUMBER, PO-RELEASE, PO-CODE, LINE-NBR, SEQ-NBR	ITEM, MATCHED-QTY, MATCH-UNIT-CST, TOT-BASE-AMT, API-OBJ-ID	APINVOICE on API-OBJ-ID	MATCHED_QTY

Tables, prefixes and index keys checked against the LawsonGuru data dictionary for Lawson Applications 9.0 (lawsonguru.com/gurucontent/datadict/90). The SQL column example shows the usual database naming: the 4GL prefix is dropped, hyphens become underscores and occurs fields get a _01 to _nn suffix. Confirm names on your own database, because Apps 10 and site changes can add fields.

Get a GL and AP query pack for your Lawson database

Tell us your Lawson version and database platform, and we will send trial balance, AP aging and GL detail queries using your column names.

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

Was this tool useful?

4GL names vs database column names

In Lawson 4GL and the data dictionary, every field carries the table prefix, such as GLT-BASE-AMOUNT in GLTRANS or API-INVOICE-DTE in APINVOICE. In the relational database behind LSF, columns are usually stored without the prefix and with underscores, so GLT-BASE-AMOUNT appears as BASE_AMOUNT. Fields that occur more than once get a numeric suffix, so the 13 GLAMOUNTS period buckets become DB_AMOUNT_01 to DB_AMOUNT_13 and CR_AMOUNT_01 to CR_AMOUNT_13. DME and Transaction calls use the 4GL element names without the prefix. Direct SQL uses the database column names.

GLTRANS vs GLAMOUNTS for reporting

Use GLAMOUNTS for balances and trial balances. It holds one row per company, fiscal year, accounting unit, account and subaccount, with beginning balances and 13 period buckets, so a full-year trial balance reads very few rows. Use GLTRANS when you need detail: journal lines, source codes, posting dates, references and descriptions. GLTRANS can be very large, so filter on its indexes. GLTSET1 starts with company, fiscal year and period, and GLTSET2 starts with to-company, fiscal year, period and the account string. Posted totals from GLTRANS for a period should match the GLAMOUNTS buckets for the same accounts.

Balances: GLAMOUNTS, keyed by GAMSET1
Journal detail: GLTRANS joined to GLCONTROL
Account descriptions: GLCHARTDTL through the company's CHART-NAME in GLSYSTEM
Accounting unit names: GLNAMES

Finding the data dictionary and screens

The full field list for any table is in the Lawson data dictionary. On the server, the dbdef utility shows the definition for your product line, and the LawsonGuru site publishes the 9.0 dictionary by table, with indexes, relations and the programs that read and update each table. Common screens tied to these tables include GL40.1 Journal Entry and GL190 Journal Posting (GLTRANS, GLAMOUNTS), GL90.1 Transaction Analysis, GL10.1 Company (GLSYSTEM), AP10.1 Vendor (APVENMAST), AP20.1 Basic Invoice and AP30.1 Invoice Maintenance (APINVOICE), and AP90.1 Invoice Search by Vendor, Invoice.

Sign convention and common mistakes

Lawson normally stores credits as negative amounts, so a period's net activity is the debit bucket plus the credit bucket, not debit minus credit. Before you publish a report, check your result against the GL trial balance or the GL90.1 inquiry for a few accounts. Other common mistakes are forgetting that the account string is accounting unit plus account plus subaccount, joining GLTRANS to accounts without COMPANY, and summing GLTRANS lines whose STATUS shows they are not posted. Several GLTRANS indexes exclude STATUS 8 rows, so check what each status value means on your release before filtering.

Frequently Asked Questions

What is Lawson S3?

Lawson S3 is the Lawson, now Infor, on-premises application suite covering Financials, HR, Payroll and Supply Chain, running on the Lawson Environment or Lawson System Foundation (LSF). Its data lives in tables such as GLTRANS, GLAMOUNTS and APINVOICE, defined in the Lawson data dictionary.

Which Lawson table holds GL balances?

GLAMOUNTS holds GL balances by company, fiscal year, accounting unit, account and subaccount. It has debit and credit beginning balances and 13 period buckets for debits and credits. GLUNITS holds the same structure for statistical units. Use GLTRANS only when you need transaction detail.

How do I join APINVOICE to APDISTRIB?

APDISTRIB's primary index APDSET1 is API-OBJ-ID plus DIST-SEQ-NBR, so the usual join is APINVOICE OBJ-ID to APDISTRIB API-OBJ-ID. APDISTRIB also carries COMPANY, VENDOR, INVOICE, SUFFIX and CANCEL-SEQ, so you can join on the invoice key fields instead.

Where can I get an Infor Lawson report listing?

The data dictionary lists, for each table, the programs that reference and update it, which gives you a report and program listing by table. On your own system, use the Lawson job and program lists and dbdef. LawsonGuru publishes the 9.0 dictionary for tables, elements and programs.

If you want finance to ask Lawson GL and AP questions in plain English, on your own servers and with the query shown, we can set up a private assistant over these tables.

Talk to an Expert Explore More Free Tools