Sage X3 Database Tables and Data Model Reference
Sage X3 stores its data in a relational database (SQL Server or Oracle) using short uppercase table names and columns that end in _0. This reference lists the main tables by module, with what each stores and how rows are identified.
| Table | Module | What it stores | Leading key columns | Note |
|---|---|---|---|---|
COMPANY | Common data | Companies | CPY_0 | Top of the organization structure |
FACILITY | Common data | Sites (facilities) | FCY_0 | Plants, warehouses and legal sites; most tables carry a site column |
BPARTNER | Common data | Business partner header | BPRNUM_0 | One row for each customer, supplier or other partner |
BPCUSTOMER | Sales | Customer data | BPCNUM_0 | Linked to BPARTNER by number |
BPSUPPLIER | Purchasing | Supplier data | BPSNUM_0 | Linked to BPARTNER by number |
BPADDRESS | Common data | Partner addresses | BPANUM_0, BPAADD_0 | Address codes per partner |
ITMMASTER | Common data | Product (item) master | ITMREF_0 | Descriptions, category, units and stock management flags |
ITMSALES | Sales | Item sales data | ITMREF_0 | Sales unit and sales attributes |
ITMFACILIT | Common data | Item-site data | ITMREF_0, STOFCY_0 | Per-site settings such as planning and costing |
TABUOM | Common data | Units of measure | UOM_0 | Unit definitions |
SORDER | Sales | Sales order header | SOHNUM_0 | Customer, dates, status |
SORDERQ | Sales | Sales order line quantities | SOHNUM_0, SOPLIN_0 | Line quantities and delivery detail |
SORDERP | Sales | Sales order line prices | SOHNUM_0, SOPLIN_0 | Line pricing and amounts |
SDELIVERY | Sales | Delivery header | SDHNUM_0 | Shipments to customers |
SDELIVERYD | Sales | Delivery lines | SDHNUM_0, SDDLIN_0 | Links back to sales order lines |
SINVOICE | Sales | Sales invoice header | NUM_0 | Customer invoices and credit notes |
SINVOICED | Sales | Sales invoice lines | NUM_0, SIDLIN_0 | Invoice detail |
PORDER | Purchasing | Purchase order header | POHNUM_0 | Supplier, dates, status |
PORDERQ | Purchasing | Purchase order line quantities | POHNUM_0, POPLIN_0 | Quantities and expected receipt |
PORDERP | Purchasing | Purchase order line prices | POHNUM_0, POPLIN_0 | Prices and amounts |
PRECEIPT | Purchasing | Purchase receipt header | PTHNUM_0 | Goods received |
PRECEIPTD | Purchasing | Purchase receipt lines | PTHNUM_0, PTDLIN_0 | Receipt detail |
PINVOICE | Purchasing | Purchase invoice header | NUM_0 | Supplier invoices |
BOM | Manufacturing | Bill of material header | ITMREF_0, BOMALT_0 | One row per product and alternate |
BOMD | Manufacturing | Bill of material components | ITMREF_0, BOMALT_0, BOMSEQ_0 | Component lines |
ROUTING | Manufacturing | Routing header | ITMREF_0, ROUALT_0 | Routing per product and alternate |
WORKSTATIO | Manufacturing | Work centers | WST_0 | Capacity and cost data |
MFGHEAD | Manufacturing | Work order header | MFGNUM_0 | Work order status and dates |
MFGITM | Manufacturing | Work order products | MFGNUM_0, MFGLIN_0 | Products made by the work order |
MFGMAT | Manufacturing | Work order materials | MFGNUM_0, MFGLIN_0 | Material requirements and issues |
MFGOPE | Manufacturing | Work order operations | MFGNUM_0, OPENUM_0 | Operations and times |
STOCK | Stock | Stock on hand by site, location and lot | STOFCY_0, ITMREF_0 | Key varies by lot, serial and status |
STOJOU | Stock | Stock transaction journal | VCRTYP_0, VCRNUM_0 | History of stock movements |
GACCOUNT | Finance | General accounts | COA_0, ACC_0 | Chart of accounts entries |
GACCENTRY | Finance | Journal entry header | TYP_0, NUM_0 | Accounting entries |
GACCENTRYD | Finance | Journal entry lines | TYP_0, NUM_0, LIN_0 | Debits and credits with analytical dimensions |
AUTILIS | Administration | Users | LOG_0 | Login and profile settings |
Leading key columns show how rows are identified, not the full primary key. Check the full definition in Development > Data and parameters > Tables in your X3 folder. Column names carry the _0 suffix in SQL, and _1, _2 for multi-occurrence fields.
Ask your X3 data questions in plain English
Optional: a private read-only AI layer over Sage X3, deployable on your own servers.
Optional. The tool and every export work without this. No spam.
How to use this reference
Search by module or by table name, then export the rows you need into your report specification or migration mapping. Because X3 table names are short codes, the reference is most useful for translating a business object such as a sales order or work order into the header and line tables behind it. For field level detail, X3 includes a data dictionary under Development, where each table lists columns, indexes and links.
The X3 data model and naming conventions
Each X3 folder (for example SEED or PROD) corresponds to a database schema, so queries are written as SCHEMA.TABLE. Column names end with _0 for the first occurrence and _1, _2 and so on for array fields. Every table also carries system columns such as UPDTICK_0, AUUID_0, CREDATTIM_0 and ROWID. Documents are split into header, quantity and price tables for sales and purchasing, and into header and line tables for deliveries, receipts and invoices.
- Header, line quantity and line price: SORDER, SORDERQ, SORDERP
- Item and item-site data: ITMMASTER, ITMSALES, ITMFACILIT
- Work orders: MFGHEAD, MFGITM, MFGMAT, MFGOPE
- Stock: STOCK for the balance and STOJOU for the history
Reading X3 data safely
Report from a read-only database user or a replica and never update X3 tables directly. Writing straight to the database bypasses X3's business logic, sequence counters and the UPDTICK optimistic locking, and can leave documents inconsistent. For changes use X3 imports, web services or subprograms. Also remember that status and type columns store numeric codes whose meaning is defined by menu local numbers (MENUs), which sit in the dictionary and the ADOVSC family of tables, so translate codes before reporting.
Common mistakes
Assuming the Syracuse or MongoDB layer holds business data (it holds web server configuration and user settings, while business data sits in the SQL database). Joining sales tables on number alone without including the site or document type where the key requires it. Reporting on STOCK without understanding that rows are split by lot, status and location. Forgetting that the same table name exists in different folders, each with its own data.
Frequently Asked Questions
What database does Sage X3 use?
Sage X3 stores business data in Microsoft SQL Server or Oracle Database, depending on how it was installed. Newer versions also use MongoDB for the Syracuse web layer, but that holds configuration and user settings rather than business transactions.
Why do Sage X3 columns end in _0?
X3 names a column with a numeric suffix for each occurrence. Normal single-value columns use _0, and arrays or multi-value fields use _1, _2 and so on. This is a naming convention from the X3 data dictionary.
Where is the Sage X3 data dictionary?
In the Development menu under Data and parameters, where tables, columns, indexes, links and abbreviations are defined for the selected folder. Developers use it to see the complete list of columns that this reference only summarizes.
Can I write directly to X3 tables?
Avoid it. Direct updates bypass business rules, counters and locking, and can corrupt document consistency. Use the X3 import templates, web services or subprograms for writes, and use direct SQL only for read-only reporting.
If your team keeps writing the same X3 reports, we can add a private, read-only natural-language layer over your X3 database that runs on your own servers.
Related Tools
Sage 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.
Other ERPsSage X3 Maintenance Checklist and Schedule
A tick-box routine maintenance plan for Sage X3: database, batch server, Syracuse, backups, patches, security and testing.
Other ERPsSage 300 Database Table Reference
A quick lookup of well-known Sage 300 SQL table names, the module they belong to and what each stores, for reports and integrations.
SAPSAP Core Tables and Key Fields Reference
A searchable lookup of the SAP tables ABAP developers use most, with their primary key fields and owning module.
More Other ERPs tools
See all- Sage X3 Private AI Layer Blueprint Generator
- Sage X3 AI Assistant Sizing and Value Calculator
- Infor VISUAL Work Order Status and Structure Reference
- Infor VISUAL Manufacturing Scheduling Checklist
- Infor XA (MAPICS) Period End Close Checklist
- Infor VISUAL Database Table Reference
- Infor XA (MAPICS) Application Module Reference
- IBM i Commands for Infor XA Administrators Reference
- Acumatica Generic Inquiry Design Template
Keep exploring
Free tools
- 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.
- Sage X3 AI Assistant Sizing and Value CalculatorSize GPUs and estimate value for a private AI assistant on Sage X3. Enter named users, usage and cost assumptions to get concurrency, tokens per day, index size and payback.
ERP fixes and how-tos
- 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.
- 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 maintenance guide: services, patches and databaseA routine maintenance checklist for on-prem Sage X3: services, Syracuse, database, logs, backups, patches and what to test before and after changes.
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 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.
- 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.