Sage X3 Table Dictionary, Control Tables and User Tables Reference
This reference lists the Sage X3 V11/V12 technical tables that sit behind the dictionary, parameters, local menus, translated texts, users, batch server and payments. Each row gives the primary key fields, the function that maintains the data and a tip for writing SQL against it.
| Table | Area | Stores | Primary key (fields) | Maintained in | SQL tip |
|---|---|---|---|---|---|
ATABLE | Dictionary | Table dictionary: one line per table | CODFIC | GESATB (Tables) | Abbreviation and description per table; start here to find a table name. |
ATABZON | Dictionary | Fields of each table | CODFIC + CODZONE | GESATB (Tables), Columns tab | Filter CODFIC_0 to list a table's fields; CODTYP_0 is the data type code to resolve in ATYPE. |
ATABIND | Dictionary | Index definitions of each table | CODFIC + CODIND | GESATB (Tables), Indexes tab | Shows key names such as ITM0 and their fields before you write joins. |
ATYPE | Dictionary | Data types | CODTYP | GESATY (Data types) | Resolve a field's CODTYP to its length and linked object. |
ACTIV | Dictionary | Activity codes | CODACT | GESACV (Activity codes) | List specific activity codes (X, Y, Z) to see what a patch must not overwrite. |
AFONCTION | Dictionary | Function dictionary | CODINT | GESAFC (Functions) | Map a function code such as GESSOH to its process and access setup. |
ADOPAR | Parameters | Parameter definitions | PARAM | GESADP (Parameter definitions) | Chapter and level (folder, company or site) of each parameter before reading its values. |
ADOVAL | Parameters | Parameter values by folder, company and site | PARAM + level (company, site) | ADPVAL (Parameter values) | Filter PARAM_0 for one parameter; the same parameter can have a folder, company and site value. |
ATABTAB | Parameters | Miscellaneous table definitions | NUMTAB | GESADV (Definition of miscellaneous tables) | Tells you what each miscellaneous table number is for. |
ATABDIV | Parameters | Miscellaneous table data | NUMTAB + CODE | GESADI (Miscellaneous tables) | Filter NUMTAB_0 to read one list; entry descriptions are translatable texts in ATEXTRA. |
APLSTD | Texts | Local menu values (texts by language) | LANCHP + LANNUM + LAN | Local menus (development) | Join a status field on LANCHP_0 = menu number and LANNUM_0 = value, filter LAN_0 for the text in LANMES_0. |
ATEXTE | Texts | Dictionary messages and texts | LAN + NUMERO | Dictionary texts | Dictionary titles and messages are stored by text number and language. |
ATEXTRA | Texts | Translatable texts of records (descriptions) | CODFIC + ZONE + LANGUE + IDENT1 + IDENT2 | Translation of record texts | Read translated descriptions: CODFIC_0 = table, ZONE_0 = field, IDENT1_0 = key value. |
AUTILIS | Security | Users | USR | GESAUS (Users) | List users for licence and access reviews; the same table is often joined to CREUSR_0 and UPDUSR_0 audit columns. |
ABATRQT | Batch | Batch server requests | NUMREQ | Batch server request management | One line per submitted request; use it for failed or long-running job reports. |
TABCUR | Finance | Currencies | CUR | Currency setup | Currency codes and decimals for formatting amounts in reports. |
TABCHANGE | Finance | Exchange rates | CHGTYP + CURDEN + CUR + CHGSTRDAT | Exchange rate entry | Pick the latest CHGSTRDAT_0 on or before the document date for the rate type. |
BANK | Finance | Bank accounts | BAN | Bank account setup | Bank code used on payments in PAYMENTH.BAN_0. |
GACCDUDATE | Finance | Open items (due dates) | TYP + NUM + LIG + DUDLIG | Created by journal and invoice posting | Base for aged balance reports; group by BPR_0 and due date. |
PAYMENTH | Finance | Payment header | NUM | Payment and receipt entry | Header with the business partner (BPR_0), bank (BAN_0) and currency (CUR_0). |
PAYMENTD | Finance | Payment lines | NUM + LIN | Payment and receipt entry | Join to PAYMENTH on NUM_0 to see which open items each payment settles. |
FXDASSETS | Finance | Fixed assets | AASREF | Fixed assets | Asset register by company (CPY_0); key AASREF_0 is the asset reference. |
Table names and keys follow the Sage X3 V12 dictionary; confirm field names on your version with GESATB or a SELECT on ATABZON. Column names in the database carry an index suffix (_0 for single fields). Business tables such as ITMMASTER, SORDER or GACCENTRY are in the separate Sage X3 database tables reference.
Need a data dictionary for your folder?
Tell us your Sage X3 version and folder and we will help you build a field-level dictionary, including specific tables, for reporting or migration.
Optional. The tool and every export work without this. No spam.
How to use it
Search by table name, function code or a word such as menu, parameter or batch. The keys column shows the fields of the primary index without the _0 suffix; in SQL every single-value column is named FIELD_0. Use the Maintained in column to open the matching function and compare what you see in SQL with the screen. Export the table if you are building a data dictionary for a report writer or a migration team.
- Use ATABLE and ATABZON to discover any table's fields.
- Use APLSTD to turn local menu integers into labels.
- Use ADOVAL to audit parameter values by company and site.
Where this lives in Sage X3
Tables are maintained in Development > Data and parameters > Tables (GESATB). Miscellaneous tables are defined in GESADV and filled in GESADI. Parameter definitions are in GESADP and their values in ADPVAL, by folder, company or site. Users are maintained in GESAUS, activity codes in GESACV and functions in GESAFC, and batch server requests are followed from the batch server administration functions. Each folder has its own schema in the database, so the same table exists once per folder.
Worked example: list the fields of a table
To list the fields of ITMMASTER in folder SEED on SQL Server, start with SELECT * FROM SEED.ATABZON WHERE CODFIC_0 = 'ITMMASTER' and look at the columns returned: CODZONE_0 is the field code and CODTYP_0 the data type, which you resolve in SEED.ATYPE on CODTYP_0. The same query shows the length, the array dimension, the linked table and the local menu number of each field, which is the quickest way to learn the dictionary column names on your version. A field with a dimension above 1 appears in the business table as several columns (ITMDES1_0, ITMDES1_1 and so on). Use the local menu number with APLSTD to decode status values in reports.
Gotchas
Local menu fields store integers, not text: join to APLSTD on LANCHP_0 and LANNUM_0 and filter LAN_0 to your language. Array fields become several columns (_0, _1, _2) and must be read individually. ADOVAL can hold the same parameter at folder, company and site level; the most specific value wins. Never write to dictionary tables with SQL: changes must go through the functions so validation regenerates the database objects. Each folder has its own schema, so always qualify table names with the folder schema in SQL.
Frequently Asked Questions
Which table holds the Sage X3 table dictionary?
ATABLE holds one line per table (code CODFIC, abbreviation and description) and ATABZON holds the fields of each table, keyed by CODFIC, NUMLIG and CODZONE. ATABIND holds index definitions. Together they let you document any table, including specific ones, directly from SQL.
Where are Sage X3 users stored?
Users are stored in AUTILIS, keyed by USR and maintained in the Users function (GESAUS). Creation and update user columns on business tables, such as CREUSR_0 and UPDUSR_0, hold the same user codes, so AUTILIS is the table to join when you report who created or changed records. For access reviews, export users with their profiles rather than reading screens one by one.
What is the PAYMENTD table in Sage X3?
PAYMENTD holds the lines of a payment or receipt, keyed by NUM and LIN, under the PAYMENTH header keyed by NUM. Join PAYMENTD to PAYMENTH on NUM_0 to see which open items, from GACCDUDATE, each payment settled.
What are Sage X3 miscellaneous tables?
Miscellaneous tables are small code lists, such as reason codes, defined in GESADV (stored in ATABTAB) and filled in GESADI (stored in ATABDIV). Each list has a number, NUMTAB, and each entry a CODE plus optional extra alphanumeric and numeric values defined for that list. Filter ATABDIV on NUMTAB_0 to read one list, and read the translated descriptions from ATEXTRA.
If you want your team to ask Sage X3 questions in plain English, with the dictionary resolved for them, on your own servers, we can set that up.
Related Tools
Sage X3 Database Tables Reference
Key Sage X3 tables by module with what they store, typical key columns and naming conventions for SQL and reporting.
Other ERPsSage X3 4GL (L4G) Syntax and Functions Reference
One searchable table of the Sage X3 V11/V12 4GL keywords developers look up most, with one-line syntax and the gotchas, plus the fstat return values.
Other ERPsSage X3 REST API Query URL Builder (Syracuse api1)
Fill in server, endpoint folder, class, representation and filters to get ready-to-run Sage X3 api1 query and detail URLs with curl and PowerShell examples.
Other ERPsSage X3 Import Template Worksheet
An editable import tracker for Sage X3: which object, which import template, source file, records loaded, errors and owner, with error rates calculated.
More Other ERPs tools
See all- Sage X3 Maintenance Checklist and Schedule
- Sage X3 Private AI Layer Blueprint Generator
- Sage X3 AI Assistant Sizing and Value Calculator
- Sage X3 SOAP Web Service Request Generator (CAdxWebServiceXmlCC)
- Sage X3 Patch, Syracuse and Version Upgrade Checklist (V11, V12 and Later Releases)
- Sage X3 On-Premises License and Maintenance Cost Calculator
- Infor VISUAL Work Order Status and Structure Reference
- Infor VISUAL Manufacturing Scheduling Checklist
- Infor XA (MAPICS) Period End Close Checklist
Keep exploring
Free tools
- Sage X3 Database Tables ReferenceKey Sage X3 tables by module with what they store, typical key columns and naming conventions for SQL and reporting.
- Sage X3 Import Template WorksheetAn editable import tracker for Sage X3: which object, which import template, source file, records loaded, errors and owner, with error rates calculated.
- Sage X3 Maintenance Checklist and ScheduleA tick-box routine maintenance plan for Sage X3: database, batch server, Syracuse, backups, patches, security and testing.
ERP fixes and how-tos
- Sage X3 Table Dictionary: How to Browse Tables and What PAYMENTD HoldsHow to browse the Sage X3 data dictionary, how X3 names tables and columns, and what the payment tables such as PAYMENTD hold, with safe SQL guidance.
- Sage X3 AI Integration On-Prem: A Private ArchitectureHow to add a private AI assistant to on-prem Sage X3: SQL read replica, Syracuse SData and web services, retrieval index, local GPU model and audit trail.
- Sage X3 Production Planning, Scheduling and TrackingHow Sage X3 production planning works: master data, MRP, work orders, scheduling and shop floor tracking, and what to set up first. Practical overview.
AI on your ERP
- AI for Sage X3, Grounded in Your Own Ledgers and Stock DataAdd AI to Sage X3 finance and stock data without exporting it to a public model. On-prem and private-cloud LLMs for Sage X3 in the UK and France.
- AI for Sage X3 Purchasing and Stock: Orders, Receipts and LotsAI for Sage X3 purchasing and stock: PO review, supplier delivery analysis, stock status and lot questions over X3 data using GraphQL, SOAP and private models.
Integrations
- How to add AI agents to Sage X3How to add AI agents to Sage X3: a tool layer over SData and web services, read-first design, human approval on writes, and on-prem models for MRP and POs.
- How to connect Power BI to Sage X3Connect Power BI to Sage X3 through a warehouse, replica or API, use the on-prem gateway, build manufacturing dashboards and add natural-language questions.