Infor SyteLineFree referencePDF, Excel, Word exportInfor SyteLineAll Infor SyteLine (CloudSuite Industrial) tools

SyteLine Job Tables and Columns Reference (SyteLine 10 / CSI 10)

This reference lists the SyteLine 9 and CSI 10 columns that production job reports use, across job_mst, jobroute_mst, jobmatl_mst and jobtran_mst. Each row shows the meaning, typical values, the join key and the formula that column feeds.

Download:

Table	Column	Meaning	Typical values	Join to	Report tip
job_mst	job	Job number	nvarchar	jobroute_mst.job, jobmatl_mst.job, jobtran_mst.job	Always join with suffix as well
job_mst	suffix	Job suffix	smallint, 0 for the main job	jobroute_mst.suffix	Key: job + suffix. Missing suffix mixes split jobs
job_mst	type	Record type in the job table	J job, E estimate, S standard	-	Estimates and standards share the table: always filter type = 'J' for production
job_mst	stat	Job status	Status code	-	See the SyteLine job and order status codes reference for values
job_mst	item	Item the job makes	Item code	item_mst.item	Join item_mst for description and product code
job_mst	qty_released	Quantity released to make	decimal	-	Remaining = qty_released - qty_complete - qty_scrapped
job_mst	qty_complete	Quantity completed to date	decimal	-	Updated from finished jobtran postings
job_mst	qty_scrapped	Quantity scrapped to date	decimal	-	Yield = qty_complete / (qty_complete + qty_scrapped)
job_mst	whse	Warehouse the job issues from and completes to	Warehouse code	whse_mst.whse	Filter by whse in multi-warehouse sites
job_mst	ord_type	What the job supplies	O customer order, I inventory (others exist)	-	Only join to coitem_mst when ord_type = 'O'
job_mst	ord_num	Customer order the job is for	CO number when ord_type = 'O'	coitem_mst.co_num	With ord_line and ord_release gives the exact order line
job_mst	ord_line	Customer order line	smallint	coitem_mst.co_line	Part of the order link
job_mst	ord_release	Customer order release	smallint	coitem_mst.co_release	Part of the order link
jobroute_mst	job	Job of the operation	nvarchar	job_mst.job	Join on job + suffix
jobroute_mst	suffix	Suffix of the operation	smallint	job_mst.suffix	Join on job + suffix
jobroute_mst	oper_num	Operation number	int, for example 10, 20, 30	jobmatl_mst.oper_num, jobtran_mst.oper_num	Key: job + suffix + oper_num
jobroute_mst	wc	Work center of the operation	Work center code	wc_mst.wc	Group open operations by wc for a queue report
jobroute_mst	qty_received	Quantity received into the operation	decimal	-	WIP at the operation = qty_received - qty_complete - qty_scrapped
jobroute_mst	qty_complete	Quantity completed at the operation	decimal	-	Compare with job_mst.qty_released for progress
jobroute_mst	qty_scrapped	Quantity scrapped at the operation	decimal	-	Scrap by operation and work center
jobroute_mst	complete	Operation complete flag	0 or 1	-	Filter complete = 0 for open operations
jobmatl_mst	job	Job of the material line	nvarchar	jobroute_mst.job	Join on job + suffix + oper_num
jobmatl_mst	oper_num	Operation the material is issued to	int	jobroute_mst.oper_num	Material belongs to an operation, not just the job
jobmatl_mst	sequence	Material sequence within the operation	smallint	-	Key: job + suffix + oper_num + sequence
jobmatl_mst	item	Component item	Item code	item_mst.item	Shortage reports join item on this column
jobmatl_mst	matl_qty	Quantity required per the units basis	decimal	-	Per unit or per lot depending on units
jobmatl_mst	units	Basis of matl_qty	U per unit, L per lot	-	Required = matl_qty * job qty_released when U; matl_qty when L
jobmatl_mst	qty_issued	Quantity issued to date	decimal	-	Still to issue = required - qty_issued
jobmatl_mst	matl_type	Material type	M material, T tool, F fixture, O other	-	Filter matl_type = 'M' for material shortage reports
jobtran_mst	trans_num	Labor or production transaction number	Numeric key	-	One row per posted or unposted labor or move transaction
jobtran_mst	job	Job of the transaction	nvarchar	jobroute_mst.job	Join on job + suffix + oper_num
jobtran_mst	oper_num	Operation reported	int	jobroute_mst.oper_num	Hours by operation
jobtran_mst	trans_type	Transaction type	Single-letter code, for example R run and S setup	-	Decode with SELECT trans_type, COUNT(*) FROM jobtran_mst GROUP BY trans_type
jobtran_mst	trans_date	Date of the transaction	datetime	-	Use for labor by day or shift
jobtran_mst	qty_complete	Quantity reported complete	decimal	-	Sum by operation to check jobroute_mst totals
jobtran_mst	qty_scrapped	Quantity reported scrapped	decimal	-	Scrap by employee or day
jobtran_mst	a_hrs	Actual hours reported	decimal	-	Actual labor hours = SUM(a_hrs) by job and oper_num
jobtran_mst	emp_num	Employee who reported	Employee number	employee_mst.emp_num	Labor by employee
jobtran_mst	wc	Work center reported	Work center code	wc_mst.wc	Can differ from jobroute_mst.wc if moved
jobtran_mst	posted	Posted flag	0 or 1	-	Unposted rows (posted = 0) are not yet in job costs

Applies to SyteLine 9.x and CSI 10.x on SQL Server. The _mst tables are the base tables; the views without _mst filter to the current site. Job status values are covered in the separate job and order status codes reference. Confirm letter codes on your own database with a GROUP BY before hard-coding them, because user-defined and release-specific values exist. Query a reporting copy or use a read-only login.

Get your job reports checked

Send us the SQL behind your WIP, shortage or labor report and we will check keys, type filters and units basis against your SyteLine version.

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

Was this tool useful?

How the job tables fit together

A job is identified by job plus suffix. Operations in jobroute_mst add oper_num, so their key is job, suffix, oper_num. Material lines in jobmatl_mst hang off an operation and add sequence. Labor and move transactions in jobtran_mst point back to job, suffix and oper_num. Estimates and standard routings are stored in the same job_mst table with a different type, which is why every production report must filter type = 'J'. When a job is made for a customer order, ord_type is 'O' and ord_num, ord_line and ord_release point to the coitem_mst row.

job_mst: job + suffix
jobroute_mst: job + suffix + oper_num
jobmatl_mst: job + suffix + oper_num + sequence
jobtran_mst: trans_num, linked by job + suffix + oper_num

Formulas this reference supports

Remaining to make is qty_released minus qty_complete minus qty_scrapped on job_mst. WIP at an operation is jobroute_mst qty_received minus qty_complete minus qty_scrapped. Material required depends on units: when units is 'U' the requirement is matl_qty times the job quantity released, when 'L' it is matl_qty for the lot. Still to issue is required minus qty_issued. Actual hours are SUM(a_hrs) from jobtran_mst grouped by job, suffix and oper_num, and only posted rows have reached job cost. Yield is qty_complete divided by qty_complete plus qty_scrapped.

Worked example: material shortages on released jobs

Join job_mst j to jobmatl_mst m on job and suffix, filter j.type = 'J' and m.matl_type = 'M', and compute required as CASE WHEN m.units = 'U' THEN m.matl_qty * j.qty_released ELSE m.matl_qty END. Subtract m.qty_issued to get still to issue, then join item_mst or itemwhse_mst on m.item for on hand. Rows where still to issue exceeds on hand are your shortage list. Add j.ord_num and j.ord_line when j.ord_type = 'O' so planners see which customer order is at risk. Scrap factors on material lines increase the real requirement, so include them if your site uses them.

Gotchas

Forgetting suffix in joins mixes a job with its splits. Forgetting type mixes estimates into WIP and capacity numbers. Joining jobmatl_mst to jobroute_mst on job and suffix only, without oper_num, multiplies material by the number of operations. Summing jobtran_mst hours without checking posted includes transactions that have not reached costs yet. Finally, the database columns here differ from IDO property names on SLJobs, SLJobRoutes, SLJobmatls and SLJobTrans, so match names to the layer you are querying, and test every join on a copy before a report goes live.

Frequently Asked Questions

What tables does SyteLine 10 use for production jobs?

job_mst holds the job header, jobroute_mst the operations, jobmatl_mst the material per operation and jobtran_mst the labor and production transactions. They join on job and suffix, and on oper_num below the header. Estimates and standard routings use the same job_mst table with type E or S.

Is SyteLine CSI 10 schema different from SyteLine 9?

The core job tables and their keys are the same in SyteLine 9 and CSI 10, so most job reports run on both. Releases add columns and some IDO properties change. Check new or renamed columns with a query on INFORMATION_SCHEMA.COLUMNS on your own database before an upgrade.

Should I report on tables or views in SyteLine?

The views without the _mst suffix filter rows to the site set in the session context, which suits single-site reports run inside SyteLine. External tools that connect directly usually query the _mst tables and filter site_ref explicitly. Either way, use a read-only login.

How do I get actual hours per job operation?

Sum jobtran_mst.a_hrs grouped by job, suffix and oper_num, filtered to posted = 1 if you want only what has reached job cost. Split setup and run time with trans_type. Join jobroute_mst on the same three keys to compare with planned hours and work center.

If your planners want to ask SyteLine which jobs are short or behind in plain English on your own servers, we can add a read-only AI layer over these tables.

Talk to an Expert Explore More Free Tools