How-toInfor SyteLine (CloudSuite Industrial)Production / Job Orders

Work order (job) item, status and routing tables in SyteLine

Question

work order item table

Also searched as

work order status table
table name for routing
syteline job table
syteline jobroute jobmatl table

Short answer

In SyteLine a work order is a job, keyed by job and suffix. The header is in the job table (item, quantity, status), operations are in jobroute, materials in jobmatl, and production transactions in jobtran. Item standard routings are stored as template jobs rather than in a separate routing table.

Applies to: Infor SyteLine 8.x to CloudSuite Industrial (on-prem SQL Server). Column names can vary by version, so check your schema before using the SQL.

Find work order data in SQL

1Query the job table for the header: job, suffix, item, qty_released, qty_complete, stat (status), and dates. Always include the site_ref filter in multi-site databases.
2Join jobroute on job and suffix for operations: oper_num, wc (work centre), run and setup hours.
3Join jobmatl on job and suffix (and oper_num) for the materials required: item, matl_qty, matl_qty_used.
4Use jobtran for what actually happened: labour, machine and move transactions per operation.
5Use the item master (item table) to read the item description and to find the item's template routing job.
6Work with the site-aware views or filter site_ref when you query the base tables directly.
7Test queries on a reporting copy first and keep them read-only.
8Confirm the column list in SQL Server Management Studio before relying on a column.

Job header and status

The job table holds one row per job and suffix. Status in the stat column is a one-letter code; typical values include planned (P), firm planned (F), released (R), stopped (S) and complete (C). The job table also has a type field that separates real production jobs from standard or template jobs.

Use stat to filter work in process. A job released and not yet complete is open work. For quantities, compare qty_released with qty_complete and qty_scrapped.

SELECT j.job, j.suffix, j.item, j.stat, j.qty_released, j.qty_complete
FROM job j
WHERE j.stat = 'R'
  AND j.site_ref = 'SITE1'
ORDER BY j.job, j.suffix;

Routing: operations on a job and on an item

The routing of a job is the set of rows in jobroute. The standard routing of an item is stored as a template job, which the item points to, and the job order generation copies it to the new job. That is why a routing question in SyteLine often ends at jobroute.

To list operations of a job, join job to jobroute. Do not expect a table literally named routing to carry everything.

SELECT jr.job, jr.suffix, jr.oper_num, jr.wc, jr.run_lbr_hrs, jr.setup_hrs
FROM jobroute jr
WHERE jr.job = 'J000123' AND jr.suffix = 0
ORDER BY jr.oper_num;

Materials and actuals

jobmatl lists the required components per operation with the planned quantity and the quantity issued so far. Compare them for shortage and over-issue analysis.

jobtran holds the posted transactions. Use it for actual hours and quantity by operation. Cost roll-up and variance reports are built from these tables.

Safe reporting practice

Use the Mongoose SQL views or the IDO layer for reports that ship to users, because they respect business rules and site scoping. Direct SQL is for analysis.

Never update these tables directly. Changes belong to the job order forms or IDO methods so that costs, schedules and inventory stay consistent.

Try it here, free

SyteLine SQL Query Generator for Item, Job and Order Tables

Open full tool

Fill in site, item prefix, job status and date to produce read-only SQL for item master, jobs and open customer order lines.

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.

Common pitfalls

!Forgetting site_ref and mixing sites in a multi-site database.
!Looking for a routing table instead of the template job and jobroute.
!Assuming status letters without checking the values used in your version.
!Joining on job only and ignoring suffix, which duplicates rows.
!Updating job tables with SQL and breaking cost and scheduling consistency.

How an ERP-grounded AI assistant handles this

SyteRay can translate 'show open jobs with a material shortage at operation 20' into the right joins on job, jobroute and jobmatl for your version, show the SQL and run it read-only on your server. It is useful for the table-name hunting that eats consultant time. The model runs on-prem when required.

See ERPray See SyteRay for SyteLine

Frequently asked questions

What is the work order table in SyteLine?

The job table. A work order is a job with a suffix, and it carries the item, quantities, status and dates. Operations are in jobroute, materials in jobmatl.

Where is the routing table in SyteLine?

The operations of a job are in jobroute. An item's standard routing is kept as a template job that the item references, copied when a new job is generated.

Where is work order status stored?

In the stat column of the job table, as a one-letter code. Typical values are planned, firm planned, released, stopped and complete.

Can I update job tables directly with SQL?

Do not. Use the job order forms or IDO methods so cost, schedule and inventory transactions stay in sync.

How-to

How to Maintain the BOM and Routing in SyteLine

In SyteLine the manufacturing structure for an item is a standard (type S) job: its material lines are the BOM and its operation lines are the routing. You maintain it from the item's BOM and routing forms, set effectivity dates, then let new job orders pull a copy. Existing jobs do not update automatically, which is where most confusion comes from.

How-to

How to Close a Job Order in SyteLine

Close a job order from Production > Job Orders once every routing operation is reported complete and the finished quantity has been received, then run Close Jobs to post the remaining WIP balance to variance and lock the job from further transactions.

Advanced

How to Build SQL Views for Custom SyteLine Reporting

Reports should read from purpose-built SQL views, not directly from SyteLine's core transactional tables, so an Infor patch cannot silently break them and heavy report queries do not lock rows the ERP itself needs. Put custom views in their own schema or under a clear naming convention, build them on stable base tables, and grant reporting tools read access to the views only.

How-to

How to Run a Cost Rollup in SyteLine

Run Item Cost Rollup from Product Definition > Costing > Cost Rollup, select the site and item range, run it in Simulate mode first to review variances, then run it live to post new standard costs to Item records and the Costed Bill of Material.

Error fix

Fixing SyteLine's 'Object reference not set to an instance of an object' error

This is a generic .NET NullReferenceException surfacing through the SyteLine IDO Runtime, not a SyteLine-specific error code. It almost always means a form, script or IDO method referenced a field, row or object that came back null, usually after a customization, a missing related record, or a view/IDO method call before the form finished loading. Turn on detailed client logging and check the most recent customization or form event first.

Error fix

Diagnosing and fixing SyteLine session timeout errors

SyteLine session timeouts come from one of three independent layers: the IDO Data Service session timeout on the app server, the IIS/application pool idle timeout for the web (Mongoose Web) client, or a load balancer/proxy idle timeout in front of a CloudSuite hosted environment. Fixing the wrong layer is the most common mistake - you need to identify which layer is actually expiring the session before changing anything.

AI for ERP

AI for Infor SyteLine and CloudSuite Industrial

Add grounded AI to Infor SyteLine or CloudSuite Industrial: natural-language answers, agents over IDOs and ION, on-prem or CloudSuite deployment.

Stuck on Infor SyteLine (CloudSuite Industrial)?

Talk to engineers who work inside Infor SyteLine (CloudSuite Industrial) every week, and who build private AI that answers these questions from your own ERP data.

Work order (job) item, status and routing tables in SyteLine

Find work order data in SQL

Job header and status

Routing: operations on a job and on an item

Materials and actuals

Safe reporting practice

Fill in

1. Items by prefix

2. Jobs by status and date

3. Open customer order lines

4. Demand and supply by item

Ask SyteLine in plain English

Common pitfalls

How an ERP-grounded AI assistant handles this

Frequently asked questions

Related

How to Maintain the BOM and Routing in SyteLine

How to Close a Job Order in SyteLine

How to Build SQL Views for Custom SyteLine Reporting

How to Run a Cost Rollup in SyteLine

Fixing SyteLine's 'Object reference not set to an instance of an object' error

Diagnosing and fixing SyteLine session timeout errors

AI for Infor SyteLine and CloudSuite Industrial

Stuck on Infor SyteLine (CloudSuite Industrial)?

Keep exploring

Free tools

ERP fixes and how-tos

Guides

AI on your ERP

Fill in

1. Items by prefix

2. Jobs by status and date

3. Open customer order lines

4. Demand and supply by item

Ask SyteLine in plain English