Infor SyteLine3 min readNetray Engineering TeamUpdated March 4, 2025

A Practical Guide to SyteLine's Core SQL Tables

Writing SQL against SyteLine starts with a small vocabulary of core tables and the conventions that connect them. Get those right and most commercial reporting questions are within reach; get them wrong and queries return plausible nonsense, the wrong site's rows, double-counted lines, or statuses misread. This guide covers the tables that answer the questions manufacturers ask daily, and the conventions that trip up newcomers.

The Commercial Core

Customer orders live in co (headers) and coitem (lines): co carries the customer, order date, and status, while coitem carries the item, quantities ordered and shipped, due and promise dates, and pricing. The item master is item, holding descriptions, unit of measure, and planning parameters. Purchasing mirrors sales: po headers and poitem lines. Manufacturing runs through job and jobmatl for jobs and their material requirements. Around this core sit customer and vendor masters, inventory balances, and transaction histories, but a large share of everyday questions resolve against these few tables and their joins.

co ↔ coitem: order headers to lines, the backbone of order-status reporting.
coitem ↔ item: line items to the item master for descriptions and planning data.
po ↔ poitem: the purchasing mirror of the sales structure.
job ↔ jobmatl: jobs to material requirements for shop-floor and shortage analysis.

The Conventions That Make or Break Queries

Three conventions matter everywhere. First, _mst: physical master tables carry the _mst suffix (item_mst, co_mst), often surfaced through views; know which layer you are querying. Second, site_ref: multi-site databases scope nearly every row by site, and omitting the site filter silently mixes plants. Third, statuses and quantities: order openness is not a flag but a judgment over status codes and quantity arithmetic (ordered versus shipped versus returned), which is why two hand-written open-orders reports so often disagree.

From Hand-Written SQL to a Governed Layer

These conventions are exactly what an ERP-aware query layer encodes. SyteRay compiles the full schema, 1,970 tables on its reference deployment, into a pack that grounds AI-generated SQL, validates every query against the real schema before execution, and routes definitional concepts like open orders through shared semantic views so every surface agrees. The result is the correctness of the senior report writer's SQL, available conversationally to the whole team, with the executed SQL shown for verification.

Frequently Asked Questions

What is the difference between co and coitem in SyteLine?

co holds customer order headers, one row per order, with customer, dates, and status. coitem holds the lines, one row per ordered item, with quantities, due dates, and prices. Order-level questions read co; almost everything operational, shipments, shortages, line status, needs coitem.

What does the _mst suffix mean in SyteLine tables?

_mst marks the physical master tables in the database (item_mst, co_mst, custaddr_mst). Application access frequently goes through corresponding views. When writing reports, be consistent about which layer you query and understand how your environment exposes them.

Why do my multi-site SyteLine queries return wrong numbers?

Almost always missing site_ref filtering. SyteLine scopes rows by site, and a query without a site filter aggregates every plant in the database. Every join and every WHERE clause in a multi-site environment needs to handle site_ref deliberately.

Key Takeaways

1The Commercial Core: Customer orders live in co (headers) and coitem (lines): co carries the customer, order date, and status, while coitem carries the item, quantities ordered and shipped, due and promise dates, and pricing. The item master is item, holding descriptions, unit of measure, and planning parameters.
2The Conventions That Make or Break Queries: Three conventions matter everywhere. First, _mst: physical master tables carry the _mst suffix (item_mst, co_mst), often surfaced through views; know which layer you are querying.
3From Hand-Written SQL to a Governed Layer: These conventions are exactly what an ERP-aware query layer encodes. SyteRay compiles the full schema, 1,970 tables on its reference deployment, into a pack that grounds AI-generated SQL, validates every query against the real schema before execution, and routes definitional concepts like open orders through shared semantic views so every surface agrees.