# SyteLine Job Tables and Columns Reference (SyteLine 10 / CSI 10) > Source: https://netray.co/tools/syteline-job-tables-columns-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Searchable column list for SyteLine production job tables with the composite keys, units basis and the formulas for WIP, shortages and actual hours. ## Category syteline ## Erp Infor SyteLine ## Audience - Report writer - SyteLine developer - Production planner - ERP administrator ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Column **Key:** column **Type:** text #### Meaning **Key:** meaning **Type:** text #### Typical values **Key:** values **Type:** text #### Join to **Key:** join **Type:** text #### Report tip **Key:** tip **Type:** text **Rows:** **Table:** job_mst **Column:** job **Meaning:** Job number **Values:** nvarchar **Join:** jobroute_mst.job, jobmatl_mst.job, jobtran_mst.job **Tip:** Always join with suffix as well **Table:** job_mst **Column:** suffix **Meaning:** Job suffix **Values:** smallint, 0 for the main job **Join:** jobroute_mst.suffix **Tip:** Key: job + suffix. Missing suffix mixes split jobs **Table:** job_mst **Column:** type **Meaning:** Record type in the job table **Values:** J job, E estimate, S standard **Join:** - **Tip:** Estimates and standards share the table: always filter type = 'J' for production **Table:** job_mst **Column:** stat **Meaning:** Job status **Values:** Status code **Join:** - **Tip:** See the SyteLine job and order status codes reference for values **Table:** job_mst **Column:** item **Meaning:** Item the job makes **Values:** Item code **Join:** item_mst.item **Tip:** Join item_mst for description and product code **Table:** job_mst **Column:** qty_released **Meaning:** Quantity released to make **Values:** decimal **Join:** - **Tip:** Remaining = qty_released - qty_complete - qty_scrapped **Table:** job_mst **Column:** qty_complete **Meaning:** Quantity completed to date **Values:** decimal **Join:** - **Tip:** Updated from finished jobtran postings **Table:** job_mst **Column:** qty_scrapped **Meaning:** Quantity scrapped to date **Values:** decimal **Join:** - **Tip:** Yield = qty_complete / (qty_complete + qty_scrapped) **Table:** job_mst **Column:** whse **Meaning:** Warehouse the job issues from and completes to **Values:** Warehouse code **Join:** whse_mst.whse **Tip:** Filter by whse in multi-warehouse sites **Table:** job_mst **Column:** ord_type **Meaning:** What the job supplies **Values:** O customer order, I inventory (others exist) **Join:** - **Tip:** Only join to coitem_mst when ord_type = 'O' **Table:** job_mst **Column:** ord_num **Meaning:** Customer order the job is for **Values:** CO number when ord_type = 'O' **Join:** coitem_mst.co_num **Tip:** With ord_line and ord_release gives the exact order line **Table:** job_mst **Column:** ord_line **Meaning:** Customer order line **Values:** smallint **Join:** coitem_mst.co_line **Tip:** Part of the order link **Table:** job_mst **Column:** ord_release **Meaning:** Customer order release **Values:** smallint **Join:** coitem_mst.co_release **Tip:** Part of the order link **Table:** jobroute_mst **Column:** job **Meaning:** Job of the operation **Values:** nvarchar **Join:** job_mst.job **Tip:** Join on job + suffix **Table:** jobroute_mst **Column:** suffix **Meaning:** Suffix of the operation **Values:** smallint **Join:** job_mst.suffix **Tip:** Join on job + suffix **Table:** jobroute_mst **Column:** oper_num **Meaning:** Operation number **Values:** int, for example 10, 20, 30 **Join:** jobmatl_mst.oper_num, jobtran_mst.oper_num **Tip:** Key: job + suffix + oper_num **Table:** jobroute_mst **Column:** wc **Meaning:** Work center of the operation **Values:** Work center code **Join:** wc_mst.wc **Tip:** Group open operations by wc for a queue report **Table:** jobroute_mst **Column:** qty_received **Meaning:** Quantity received into the operation **Values:** decimal **Join:** - **Tip:** WIP at the operation = qty_received - qty_complete - qty_scrapped **Table:** jobroute_mst **Column:** qty_complete **Meaning:** Quantity completed at the operation **Values:** decimal **Join:** - **Tip:** Compare with job_mst.qty_released for progress **Table:** jobroute_mst **Column:** qty_scrapped **Meaning:** Quantity scrapped at the operation **Values:** decimal **Join:** - **Tip:** Scrap by operation and work center **Table:** jobroute_mst **Column:** complete **Meaning:** Operation complete flag **Values:** 0 or 1 **Join:** - **Tip:** Filter complete = 0 for open operations **Table:** jobmatl_mst **Column:** job **Meaning:** Job of the material line **Values:** nvarchar **Join:** jobroute_mst.job **Tip:** Join on job + suffix + oper_num **Table:** jobmatl_mst **Column:** oper_num **Meaning:** Operation the material is issued to **Values:** int **Join:** jobroute_mst.oper_num **Tip:** Material belongs to an operation, not just the job **Table:** jobmatl_mst **Column:** sequence **Meaning:** Material sequence within the operation **Values:** smallint **Join:** - **Tip:** Key: job + suffix + oper_num + sequence **Table:** jobmatl_mst **Column:** item **Meaning:** Component item **Values:** Item code **Join:** item_mst.item **Tip:** Shortage reports join item on this column **Table:** jobmatl_mst **Column:** matl_qty **Meaning:** Quantity required per the units basis **Values:** decimal **Join:** - **Tip:** Per unit or per lot depending on units **Table:** jobmatl_mst **Column:** units **Meaning:** Basis of matl_qty **Values:** U per unit, L per lot **Join:** - **Tip:** Required = matl_qty * job qty_released when U; matl_qty when L **Table:** jobmatl_mst **Column:** qty_issued **Meaning:** Quantity issued to date **Values:** decimal **Join:** - **Tip:** Still to issue = required - qty_issued **Table:** jobmatl_mst **Column:** matl_type **Meaning:** Material type **Values:** M material, T tool, F fixture, O other **Join:** - **Tip:** Filter matl_type = 'M' for material shortage reports **Table:** jobtran_mst **Column:** trans_num **Meaning:** Labor or production transaction number **Values:** Numeric key **Join:** - **Tip:** One row per posted or unposted labor or move transaction **Table:** jobtran_mst **Column:** job **Meaning:** Job of the transaction **Values:** nvarchar **Join:** jobroute_mst.job **Tip:** Join on job + suffix + oper_num **Table:** jobtran_mst **Column:** oper_num **Meaning:** Operation reported **Values:** int **Join:** jobroute_mst.oper_num **Tip:** Hours by operation **Table:** jobtran_mst **Column:** trans_type **Meaning:** Transaction type **Values:** Single-letter code, for example R run and S setup **Join:** - **Tip:** Decode with SELECT trans_type, COUNT(*) FROM jobtran_mst GROUP BY trans_type **Table:** jobtran_mst **Column:** trans_date **Meaning:** Date of the transaction **Values:** datetime **Join:** - **Tip:** Use for labor by day or shift **Table:** jobtran_mst **Column:** qty_complete **Meaning:** Quantity reported complete **Values:** decimal **Join:** - **Tip:** Sum by operation to check jobroute_mst totals **Table:** jobtran_mst **Column:** qty_scrapped **Meaning:** Quantity reported scrapped **Values:** decimal **Join:** - **Tip:** Scrap by employee or day **Table:** jobtran_mst **Column:** a_hrs **Meaning:** Actual hours reported **Values:** decimal **Join:** - **Tip:** Actual labor hours = SUM(a_hrs) by job and oper_num **Table:** jobtran_mst **Column:** emp_num **Meaning:** Employee who reported **Values:** Employee number **Join:** employee_mst.emp_num **Tip:** Labor by employee **Table:** jobtran_mst **Column:** wc **Meaning:** Work center reported **Values:** Work center code **Join:** wc_mst.wc **Tip:** Can differ from jobroute_mst.wc if moved **Table:** jobtran_mst **Column:** posted **Meaning:** Posted flag **Values:** 0 or 1 **Join:** - **Tip:** Unposted rows (posted = 0) are not yet in job costs **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How the job tables fit together **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What tables does SyteLine 10 use for production jobs? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms syteline 10 erp, syteline csi 10, syteline reporting, tables and views for erp, syteline job_mst columns, syteline jobroute_mst, syteline jobmatl_mst, syteline jobtran_mst --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json