Developer ToolsFree generatorPDF, Excel, Word exportInfor SyteLine

SyteLine SQL Query Generator for Item, Job and Customer Order Tables

Anyone writing SyteLine reports ends up in the same four tables: items, jobs, customer orders and order lines. The base tables carry a site reference and a few naming quirks that trip up newcomers. This generator builds clean read-only queries you can adapt.

Fill in

Site reference

Item prefix

Job status code

From date

To date

Order line status

Generated code

Download:

1. Items by prefix

SELECT i.item, i.description, i.u_m, i.p_m_t_code, i.product_code, i.unit_cost, i.lead_time
FROM item_mst i
WHERE i.site_ref = 'SITE1'
  AND i.item LIKE 'PN-%'
ORDER BY i.item;

2. Jobs by status and date

SELECT j.job, j.suffix, j.item, j.qty_released, j.qty_complete, j.qty_scrapped, j.stat, j.job_date
FROM job_mst j
WHERE j.site_ref = 'SITE1'
  AND j.stat = 'R'
  AND j.job_date >= '2026-10-01'
  AND j.job_date < DATEADD(day, 1, '2026-10-31')
ORDER BY j.job_date, j.job, j.suffix;

3. Open customer order lines

SELECT c.co_num, c.cust_num, ci.co_line, ci.co_release, ci.item,
       ci.qty_ordered, ci.qty_shipped,
       (ci.qty_ordered - ci.qty_shipped) AS qty_open,
       ci.due_date, ci.stat
FROM coitem_mst ci
JOIN co_mst c
  ON c.co_num = ci.co_num AND c.site_ref = ci.site_ref
WHERE ci.site_ref = 'SITE1'
  AND ci.stat = 'O'
  AND ci.due_date >= '2026-10-01'
  AND ci.due_date < DATEADD(day, 1, '2026-10-31')
ORDER BY ci.due_date, c.co_num, ci.co_line;

4. Demand and supply by item

SELECT ci.item,
       SUM(ci.qty_ordered - ci.qty_shipped) AS open_demand,
       (SELECT SUM(j.qty_released - j.qty_complete)
          FROM job_mst j
         WHERE j.site_ref = ci.site_ref AND j.item = ci.item AND j.stat = 'R') AS open_job_supply
FROM coitem_mst ci
WHERE ci.site_ref = 'SITE1'
  AND ci.stat = 'O'
  AND ci.item LIKE 'PN-%'
GROUP BY ci.item, ci.site_ref
ORDER BY open_demand DESC;

Read-only queries for SQL Server. Table and column names shown are those commonly found in SyteLine databases, but they can differ by version and customization, so confirm them in your own schema before running. Run against a reporting copy or with read-only access where possible, and always include the site reference filter in multi-site installations.

Ask SyteLine in plain English

Optional: see a read-only assistant that writes and shows the SQL for your questions over SyteLine data.

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

How to use the generator

Enter your site reference, an item prefix, a job status code and a date range, then copy the queries you need. Each block stands alone, so paste one into SQL Server Management Studio, a report builder or a script. The queries use the underlying tables with an explicit site filter, which makes results predictable in multi-site databases. Start with a narrow filter and a small date range, then widen it once the output looks right.

Always filter on the site reference.
Test on a reporting copy or with a read-only login.
Add row limits while exploring.

How the tables relate

The item table holds master data such as unit of measure, source code, cost and lead time. The job table holds production orders keyed by job and suffix with released, completed and scrapped quantities. The customer order header carries the customer and order dates, while order lines carry item, quantities, due date and status. Lines join to headers on order number and site. Comparing open order line quantity to open job quantity by item gives a first view of whether demand is covered by supply.

Common mistakes and safeguards

Forgetting the site filter returns rows from every site and silently multiplies numbers. Hard coding status letters without checking meaning gives wrong totals, so confirm the values in your own data. Using date comparisons that ignore the time portion drops rows on the last day, which is why the queries use a less-than test against the next day. Never update SyteLine tables with SQL: changes bypass validation and audit. Use the application, IDO methods or supported import tools for writes.

Read with SQL, write through IDOs or the application.
Check stat values against the status reference.
Prefer a reporting replica for heavy queries.

Frequently Asked Questions

Why do SyteLine tables end with _mst?

The base tables carry an _mst suffix and include a site reference column. Views without the suffix usually filter to the current site context. When querying directly with SQL, use the base tables and filter on the site reference yourself.

Can I use these queries in Power BI or SSRS?

Yes, they are standard T-SQL selects. Parameterize the site, dates and status instead of typing literals, and use a reporting login with read-only permissions. For large tables, filter early and avoid selecting every column.

Is it safe to run SQL against the production database?

Read-only selects are generally safe if they are filtered and efficient, but heavy queries can slow users. Prefer a replica or reporting copy, set a read-only login and avoid unfiltered scans of large tables during working hours.

How do I find the right column names for my version?

Query the database information schema for the table, or inspect the IDO that exposes it. Column names and extra user-defined columns differ across versions and installations, so always confirm before using a query in production reporting.

Netray's ERPray answers questions over SyteLine read-only, shows the SQL it ran and respects your roles. If you are tired of maintaining a pile of one-off queries, it is worth a look.

Talk to an Expert Explore More Free Tools

Related Tools

Infor SyteLine

More Developer Tools tools

See all

Back to All Tools