ERP Table and Field Explorer

SyteLine Database Tables and Field Explorer

Search the core Infor SyteLine (CloudSuite Industrial) tables by table name, field or description, see each table's key, important columns and joins, then pick two to four tables to get a SQL Server JOIN skeleton. Every table is a _mst table that carries site_ref, and the item's current routing and BOM live in jobroute_mst and jobmatl_mst under a type S job.

72 tables, 371 documented fields and 100 join paths across 9 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: SyteLine 9 and later store data in _mst tables with a site_ref column; views named without the suffix (item, coitem) filter to the current site and return no rows until the site context is set with EXEC dbo.SetSiteSp. _mst_all tables hold copies of other sites' data for multi-site.

Tables by module

2Pick tables

72 of 72

Items

item_mst

Item master: one row per item per site, with source, status, costing, planning and the link to the current routing and BOM.

Also known as: Items form, SLItems, item master

Key:itemsite_ref
FieldMeaning
itemItem number.
descriptionItem description.
u_mStocking unit of measure.
product_codeProduct code, drives default GL accounts and reporting (prodcode_mst).
p_m_t_codeSource: P purchased, M manufactured, T transferred.
statItem status, for example A active, O obsolete, S slow moving.
matl_typeMaterial type: M material, T tool, F fixture, O other.
stocked1 when the item is stocked.
lot_tracked1 when the item is lot tracked.
serial_tracked1 when the item is serial tracked.
jobJob number of the current routing and BOM (job_mst row with type S).
suffixSuffix of the current routing and BOM job.

Joins

  • item_mst.job = job_mst.job AND item_mst.suffix = job_mst.suffix

    Current routing and BOM header (job type S).

  • item_mst.job = jobroute_mst.job AND item_mst.suffix = jobroute_mst.suffix

    Current operations.

  • item_mst.job = jobmatl_mst.job AND item_mst.suffix = jobmatl_mst.suffix

    Current materials (single-level BOM).

  • item_mst.product_code = prodcode_mst.product_code
  • item_mst.item = itemwhse_mst.item
  • item_mst.item = itemloc_mst.item
  • item_mst.item = lot_mst.item
  • item_mst.item = serial_mst.item
  • item_mst.item = matltran_mst.item
  • item_mst.item = matltrack_mst.item
  • item_mst.item = rsvd_inv_mst.item
  • item_mst.item = trnitem_mst.item
  • item_mst.item = itemprice_mst.item
  • item_mst.item = itemcust_mst.item
  • item_mst.item = itemvend_mst.item
  • item_mst.item = coitem_mst.item
  • item_mst.item = poitem_mst.item

Other planning and costing columns used in public SyteLine code include plan_code, buyer, lead_time, family_code, cost_type, cost_method, unit_weight, drawing_nbr and revision. item_glbl holds the global item for multi-site setups.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:

Dialect: SQL Server (T-SQL)

-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SQL Server (T-SQL). Review keys, filters and indexes before running on production.
-- Querying the _mst tables directly: filter site_ref. To use the site views instead, run EXEC dbo.SetSiteSp 'YOURSITE', @Infobar OUTPUT first.
-- Job, order and PO numbers are stored right-justified: pad typed values with dbo.ExpandKyByType or compare with LTRIM.
-- Read only. Write through IDOs or forms, not with direct UPDATE statements.

SELECT TOP 100
    co.co_num,
    co.cust_num,
    co.cust_seq,
    co.order_date,
    co.stat,
    cust.name,
    cust.addr##1,
    cust.city,
    cust.state,
    cust.zip,
    coit.co_line,
    coit.co_release,
    coit.item,
    coit.qty_ordered,
    coit.qty_shipped,
    item.description,
    item.u_m,
    item.product_code,
    item.p_m_t_code,
    item.matl_type
FROM dbo.co_mst AS co
-- co_mst to custaddr_mst: Ship-to name and address.
LEFT JOIN dbo.custaddr_mst AS cust
    ON co.cust_num = cust.cust_num
   AND co.cust_seq = cust.cust_seq
   AND co.site_ref = cust.site_ref
LEFT JOIN dbo.coitem_mst AS coit
    ON co.co_num = coit.co_num
   AND co.site_ref = coit.site_ref
LEFT JOIN dbo.item_mst AS item
    ON coit.item = item.item
   AND coit.site_ref = item.site_ref
WHERE co.site_ref = 'YOURSITE'  -- replace with your site code
ORDER BY co.co_num;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table name such as coitem_mst, a column such as qty_ordered, or a business word such as invoice. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the columns that matter, and the tables it joins to. Click a join to jump to that table.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. The SQL skeleton includes the documented join columns plus site_ref, ready for SQL Server Management Studio.

  4. 4

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the whole setup to your Netray account to reopen it later.

Questions about Infor SyteLine (CloudSuite Industrial) tables

What is the difference between item_mst and item in SyteLine?

Since SyteLine 9 the physical tables end in _mst and carry a site_ref column, so one database can hold several sites. The objects without the suffix, such as item and coitem, are views that filter to the current site. They return no rows from a plain SQL session until you set the site context, for example EXEC dbo.SetSiteSp 'SITE', @Infobar OUTPUT. In reports you can either do that or query the _mst tables and filter site_ref yourself.

Where is the bill of material stored in SyteLine?

The current routing and BOM of an item are stored like a job. item_mst.job and item_mst.suffix point to a job_mst row with type S. Its operations are in jobroute_mst and its materials, by operation, in jobmatl_mst. When you release a production job (type J), the current operations and materials are copied into that job's own jobroute_mst and jobmatl_mst rows, so later BOM changes do not alter existing jobs.

Which tables hold customer orders and order lines?

co_mst is the order header (customer, ship-to sequence, order date, status, terms). coitem_mst holds lines and releases keyed by co_num, co_line and co_release, with qty_ordered, qty_shipped, qty_invoiced, price, due_date and stat. Shipments are in co_ship_mst, blanket lines in co_bln_mst, and orders moved to history in coh_mst and citemh_mst.

Why does my WHERE co_num = '1234' return nothing?

SyteLine stores job, order and PO numbers right-justified with leading spaces. Either pad the value the way the application does, with dbo.ExpandKyByType('CoNumType', '1234'), or compare with LTRIM(co_num) = '1234'. Padding keeps the index usable; LTRIM on the column does not.

Where are customer and vendor names stored?

Not in customer_mst or vendor_mst. Names and addresses are in custaddr_mst (join on cust_num and cust_seq, where cust_seq 0 is the bill-to) and vendaddr_mst (join on vend_num). Infor documents vendaddr_mst as the table shared among all sites, while vendor_mst holds site specific data.

How do I find inventory transactions for a job, PO or order?

Use matltran_mst. trans_type tells you the movement (R PO receipt, I job issue, F job finish, S CO shipment and so on) and ref_type tells you the source document: O customer order, P purchase order, J job, T transfer, R RMA. ref_num, ref_line_suf and ref_release then point to the order, line and release, or to the job and suffix.

Is it safe to update SyteLine tables with SQL?

Read with SQL, write through the application. Inserts and updates through IDOs, forms or the documented import utilities fire the triggers, validation and cost logic that direct UPDATE statements skip. Multi-tenant cloud editions do not allow direct database access at all, so use IDO REST or data views there.

What are the _mst_all tables?

In a multi-site setup, _mst_all tables hold rows from all sites in the replication group, while the base _mst table holds the local site only. Infor's example is terms_mst and terms_mst_all, where site_ref tells the sites apart. Replication rules decide which categories, and so which _all tables, are filled.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.