# Sage X3 Database Tables and Data Model Reference > Source: https://netray.co/tools/sage-x3-database-tables-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Key Sage X3 tables by module with what they store, typical key columns and naming conventions for SQL and reporting. ## Category other-erp ## Erp Sage X3 ## Audience - X3 developer - Report writer - Data migration analyst ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Module **Key:** module **Type:** text #### What it stores **Key:** stores **Type:** text #### Leading key columns **Key:** keys **Type:** text #### Note **Key:** note **Type:** text **Rows:** **Table:** COMPANY **Module:** Common data **Stores:** Companies **Keys:** CPY_0 **Note:** Top of the organization structure **Table:** FACILITY **Module:** Common data **Stores:** Sites (facilities) **Keys:** FCY_0 **Note:** Plants, warehouses and legal sites; most tables carry a site column **Table:** BPARTNER **Module:** Common data **Stores:** Business partner header **Keys:** BPRNUM_0 **Note:** One row for each customer, supplier or other partner **Table:** BPCUSTOMER **Module:** Sales **Stores:** Customer data **Keys:** BPCNUM_0 **Note:** Linked to BPARTNER by number **Table:** BPSUPPLIER **Module:** Purchasing **Stores:** Supplier data **Keys:** BPSNUM_0 **Note:** Linked to BPARTNER by number **Table:** BPADDRESS **Module:** Common data **Stores:** Partner addresses **Keys:** BPANUM_0, BPAADD_0 **Note:** Address codes per partner **Table:** ITMMASTER **Module:** Common data **Stores:** Product (item) master **Keys:** ITMREF_0 **Note:** Descriptions, category, units and stock management flags **Table:** ITMSALES **Module:** Sales **Stores:** Item sales data **Keys:** ITMREF_0 **Note:** Sales unit and sales attributes **Table:** ITMFACILIT **Module:** Common data **Stores:** Item-site data **Keys:** ITMREF_0, STOFCY_0 **Note:** Per-site settings such as planning and costing **Table:** TABUOM **Module:** Common data **Stores:** Units of measure **Keys:** UOM_0 **Note:** Unit definitions **Table:** SORDER **Module:** Sales **Stores:** Sales order header **Keys:** SOHNUM_0 **Note:** Customer, dates, status **Table:** SORDERQ **Module:** Sales **Stores:** Sales order line quantities **Keys:** SOHNUM_0, SOPLIN_0 **Note:** Line quantities and delivery detail **Table:** SORDERP **Module:** Sales **Stores:** Sales order line prices **Keys:** SOHNUM_0, SOPLIN_0 **Note:** Line pricing and amounts **Table:** SDELIVERY **Module:** Sales **Stores:** Delivery header **Keys:** SDHNUM_0 **Note:** Shipments to customers **Table:** SDELIVERYD **Module:** Sales **Stores:** Delivery lines **Keys:** SDHNUM_0, SDDLIN_0 **Note:** Links back to sales order lines **Table:** SINVOICE **Module:** Sales **Stores:** Sales invoice header **Keys:** NUM_0 **Note:** Customer invoices and credit notes **Table:** SINVOICED **Module:** Sales **Stores:** Sales invoice lines **Keys:** NUM_0, SIDLIN_0 **Note:** Invoice detail **Table:** PORDER **Module:** Purchasing **Stores:** Purchase order header **Keys:** POHNUM_0 **Note:** Supplier, dates, status **Table:** PORDERQ **Module:** Purchasing **Stores:** Purchase order line quantities **Keys:** POHNUM_0, POPLIN_0 **Note:** Quantities and expected receipt **Table:** PORDERP **Module:** Purchasing **Stores:** Purchase order line prices **Keys:** POHNUM_0, POPLIN_0 **Note:** Prices and amounts **Table:** PRECEIPT **Module:** Purchasing **Stores:** Purchase receipt header **Keys:** PTHNUM_0 **Note:** Goods received **Table:** PRECEIPTD **Module:** Purchasing **Stores:** Purchase receipt lines **Keys:** PTHNUM_0, PTDLIN_0 **Note:** Receipt detail **Table:** PINVOICE **Module:** Purchasing **Stores:** Purchase invoice header **Keys:** NUM_0 **Note:** Supplier invoices **Table:** BOM **Module:** Manufacturing **Stores:** Bill of material header **Keys:** ITMREF_0, BOMALT_0 **Note:** One row per product and alternate **Table:** BOMD **Module:** Manufacturing **Stores:** Bill of material components **Keys:** ITMREF_0, BOMALT_0, BOMSEQ_0 **Note:** Component lines **Table:** ROUTING **Module:** Manufacturing **Stores:** Routing header **Keys:** ITMREF_0, ROUALT_0 **Note:** Routing per product and alternate **Table:** WORKSTATIO **Module:** Manufacturing **Stores:** Work centers **Keys:** WST_0 **Note:** Capacity and cost data **Table:** MFGHEAD **Module:** Manufacturing **Stores:** Work order header **Keys:** MFGNUM_0 **Note:** Work order status and dates **Table:** MFGITM **Module:** Manufacturing **Stores:** Work order products **Keys:** MFGNUM_0, MFGLIN_0 **Note:** Products made by the work order **Table:** MFGMAT **Module:** Manufacturing **Stores:** Work order materials **Keys:** MFGNUM_0, MFGLIN_0 **Note:** Material requirements and issues **Table:** MFGOPE **Module:** Manufacturing **Stores:** Work order operations **Keys:** MFGNUM_0, OPENUM_0 **Note:** Operations and times **Table:** STOCK **Module:** Stock **Stores:** Stock on hand by site, location and lot **Keys:** STOFCY_0, ITMREF_0 **Note:** Key varies by lot, serial and status **Table:** STOJOU **Module:** Stock **Stores:** Stock transaction journal **Keys:** VCRTYP_0, VCRNUM_0 **Note:** History of stock movements **Table:** GACCOUNT **Module:** Finance **Stores:** General accounts **Keys:** COA_0, ACC_0 **Note:** Chart of accounts entries **Table:** GACCENTRY **Module:** Finance **Stores:** Journal entry header **Keys:** TYP_0, NUM_0 **Note:** Accounting entries **Table:** GACCENTRYD **Module:** Finance **Stores:** Journal entry lines **Keys:** TYP_0, NUM_0, LIN_0 **Note:** Debits and credits with analytical dimensions **Table:** AUTILIS **Module:** Administration **Stores:** Users **Keys:** LOG_0 **Note:** Login and profile settings **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use this reference **Body:** 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 **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What database does Sage X3 use? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms sage x3 database tables, sage x3 data models, sage x3 table list, sage x3 itmmaster, sage x3 sorder table, sage x3 sql tables, sage x3 data dictionary, sage x3 stock table --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json