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

> Source: https://netray.co/erp-help/syteline-work-order-job-item-routing-status-tables
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## 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.

## Kind

how-to

## System

syteline

## Erp

Infor SyteLine (CloudSuite Industrial)

## Module

Production / Job Orders

## Query

work order item table

## Alternate Queries

- work order status table
- table name for routing
- syteline job table
- syteline jobroute jobmatl 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.

## Steps

### Find work order data in SQL

**Items:**

- Query 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.
- Join jobroute on job and suffix for operations: oper_num, wc (work centre), run and setup hours.
- Join jobmatl on job and suffix (and oper_num) for the materials required: item, matl_qty, matl_qty_used.
- Use jobtran for what actually happened: labour, machine and move transactions per operation.
- Use the item master (item table) to read the item description and to find the item's template routing job.
- Work with the site-aware views or filter site_ref when you query the base tables directly.
- Test queries on a reporting copy first and keep them read-only.
- Confirm the column list in SQL Server Management Studio before relying on a column.

## Sections

### Job header and status

**Body:**

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.

**Code:**

- 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

**Body:**

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.

**Code:**

- 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

**Body:**

- 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

**Body:**

- 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.

## 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.

## Ai Angle

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.

## Faq

### What is the work order table in SyteLine?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

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

## Search terms

syteline job table, syteline jobroute, syteline jobmatl, syteline work order tables, syteline routing table, syteline job status, syteline sql work order

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json