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

SyteLine Customer Order Tables Columns Reference

This is a column-level reference for the customer order tables in Infor SyteLine 9 and 10: co_mst, coitem_mst, co_ship_mst, co_bln_mst and the custaddr_mst join. Each row gives the meaning, typical values, join and a report tip, and three standard joins are included as rows.

Download:

Table	Column	Meaning	Typical values or type	Join to	Report tip
co_mst	co_num	Customer order number	Text, key	coitem_mst.co_num	Always join on co_num plus site_ref in multi-site databases
co_mst	cust_num	Customer number	Text	custaddr_mst.cust_num	Use with cust_seq to reach the right address
co_mst	cust_seq	Customer address sequence	0 for the bill-to, higher for ship-tos	custaddr_mst.cust_seq	cust_seq 0 gives the main customer name
co_mst	order_date	Order date	Date		Use for bookings by period
co_mst	type	Order type	R regular, B blanket		Blanket orders carry lines in co_bln_mst with releases in coitem_mst
co_mst	stat	Order status	Status code		See the job and order status codes reference for meanings
co_mst	cust_po	Customer PO number	Text		Common search field for customer service reports
co_mst	slsman	Salesperson	Text		Group bookings by salesperson
co_mst	terms_code	Payment terms code	Text		Useful in credit and AR reports
coitem_mst	co_num	Order number	Text	co_mst.co_num	Part of the line key
coitem_mst	co_line	Order line	Integer	co_ship_mst.co_line	Part of the line key
coitem_mst	co_release	Line release	Integer, 0 for regular lines	co_ship_mst.co_release	Blanket releases use values above 0
coitem_mst	item	Item ordered	Text	item_mst.item	Join item_mst for description and product code
coitem_mst	cust_item	Customer's item number	Text		Show it on customer-facing reports
coitem_mst	qty_ordered	Quantity ordered	Decimal, in base unit of measure		Open qty is commonly qty_ordered minus qty_shipped
coitem_mst	qty_shipped	Quantity shipped	Decimal		Compare with co_ship_mst totals when auditing
coitem_mst	qty_invoiced	Quantity invoiced	Decimal		Shipped not invoiced = qty_shipped minus qty_invoiced
coitem_mst	qty_returned	Quantity returned	Decimal		Net it out of shipped quantities for true shipments
coitem_mst	price	Unit price	Decimal, order currency		Check the order currency before summing across orders
coitem_mst	disc	Line discount percent	Decimal		Net value = qty * price * (1 - disc / 100)
coitem_mst	due_date	Line due date	Date		Basis for late-line reports
coitem_mst	promise_date	Promised date	Date		Compare with due_date for on-time to promise
coitem_mst	stat	Line status	Status code		Filter open lines by status, not only by quantity
coitem_mst	whse	Shipping warehouse	Text	itemwhse_mst.whse	Join itemwhse_mst on item and whse for on-hand
coitem_mst	u_m	Unit of measure	Text		Quantities are stored in the item's base unit
coitem_mst	ref_type, ref_num, ref_line_suf, ref_release	Supply linked to the line	Reference type plus document keys	job_mst or poitem_mst depending on ref_type	Shows the job or PO supplying a make-to-order line
co_ship_mst	co_num, co_line, co_release	Line the shipment belongs to	Keys	coitem_mst	Join on all three columns
co_ship_mst	date_seq	Shipment sequence for the line	Integer		Several shipments per line are normal
co_ship_mst	ship_date	Shipment date	Date		Use for shipments by period and on-time delivery
co_ship_mst	qty_shipped	Quantity on this shipment	Decimal		Sum per line to reconcile with coitem_mst.qty_shipped
co_bln_mst	co_num, co_line	Blanket order line	Keys	coitem_mst on co_num and co_line	Releases for the blanket line sit in coitem_mst
co_bln_mst	item	Item on the blanket line	Text	item_mst.item	Use for blanket coverage reports
custaddr_mst	cust_num, cust_seq	Customer address key	Keys	co_mst on cust_num and cust_seq	Join on both columns
custaddr_mst	name	Customer or ship-to name	Text		Use cust_seq 0 for the customer name
join	co_mst to coitem_mst	Order header to lines	ON co.co_num = ci.co_num	Add site_ref in multi-site databases	Header filters such as order date go on co_mst
join	coitem_mst to co_ship_mst	Lines to shipments	ON s.co_num = ci.co_num AND s.co_line = ci.co_line AND s.co_release = ci.co_release		Use LEFT JOIN to keep unshipped lines
join	co_mst to custaddr_mst	Order to customer address	ON ca.cust_num = co.cust_num AND ca.cust_seq = co.cust_seq		Use cust_seq 0 instead for the bill-to name

Every _mst table also carries system columns: site_ref, RowPointer, RecordDate, CreateDate and audit columns such as CreatedBy and UpdatedBy. Status code meanings are in the job and order status codes reference. Confirm columns in SSMS or in the SLCos and SLCoitems IDO property lists for your version before relying on them in production reports.

Get a custom order report built

Tell us the order report you need and your SyteLine version, and we will send SQL against these tables with the joins and filters checked for your site.

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

Was this tool useful?

How to use it

Filter by table to see the columns you need, or search for a business term such as 'due' or 'invoiced'. Build reports on a reporting copy or read-only login, starting from coitem_mst for anything line based (open orders, late lines, backlog) and from co_mst for header facts (order date, customer, salesperson). Add co_ship_mst when you need shipment dates rather than totals. If you prefer an IDO or REST route, the same data comes through SLCos and SLCoitems, with property names in mixed case such as CoNum and CoLine.

Worked example: open order backlog

For a backlog report, select from coitem_mst joined to co_mst on co_num and to custaddr_mst on cust_num and cust_seq. Keep lines whose status is open, calculate open quantity as qty_ordered minus qty_shipped, and value it as open quantity times price times (1 - disc / 100), converting currency where orders are not in your domestic currency. Group by customer and due_date month, and flag lines where due_date is earlier than today. Export to Excel from SSMS, Report Builder or your BI tool.

Open qty = qty_ordered - qty_shipped
Open value = open qty * price * (1 - disc / 100)
Late = due_date earlier than today with open qty above zero

Multi-site and system columns

Every _mst table includes site_ref, and in a multi-site database the same order number can exist for more than one site, so include site_ref in every join and filter. RowPointer is a unique GUID per row, useful for change tracking and for linking notes and attachments. RecordDate is updated on change, which makes it the practical column for incremental extracts to a data warehouse. CreateDate and the audit columns record who created and changed rows. Views without the _mst suffix filter by the current site context and are often not what a reporting login expects.

Common mistakes

The most frequent error is joining coitem_mst to co_ship_mst on co_num and co_line only, which multiplies rows for blanket releases; include co_release. The second is summing price across orders in different currencies. Third, filtering open lines by quantity alone includes lines that were closed short; use status as well, with meanings from the job and order status codes reference. Finally, remember that blanket orders keep their line definition in co_bln_mst and their releases in coitem_mst.

Frequently Asked Questions

Which tables hold customer orders in SyteLine?

co_mst holds the order header, coitem_mst the lines and releases, co_ship_mst the shipments per line, and co_bln_mst the blanket order lines. Customer names and addresses come from custaddr_mst, joined on cust_num and cust_seq. The SLCos and SLCoitems IDOs expose the same data through Mongoose.

How do I export SyteLine reports to Excel?

Most standard reports can be output to Excel formats from the report options, and grid views on forms can be exported from the client. For custom reports, run the SQL against a reporting copy and export from SSMS, or use the IDO REST API or your BI tool to load data straight into Excel or Power BI.

What does cust_seq mean on a customer order?

cust_seq is the customer address sequence. Sequence 0 is the main customer, used as the bill-to, and higher sequences are ship-to addresses. Join co_mst to custaddr_mst on both cust_num and cust_seq to get the ship-to on the order, or force cust_seq 0 to get the customer name.

How do I find the job supplying a customer order line?

Read ref_type, ref_num, ref_line_suf and ref_release on coitem_mst. When the reference type points to a job, ref_num and ref_line_suf give the job and suffix to join to job_mst; when it points to a PO, they give the PO and line. Map the stored ref_type values in your own database first.

If you want sales and planning to ask SyteLine about orders, backlog and shipments in plain English on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools