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

SyteLine Purchasing Tables and Columns Reference for PO Reports

This reference lists the SyteLine 9 and 10 columns most purchase order reports need, from po_mst headers down to matltran_mst receipt rows. Each row gives the meaning, typical values, the join and a tip for open, late and received-not-vouchered reports.

Download:

Table	Column	Meaning	Typical values or type	Join to	Report tip
po_mst	po_num	Purchase order number, the header key	nvarchar, zero or prefix padded per your PO prefix setup	poitem_mst.po_num	Always join lines on po_num; never on vendor alone
po_mst	vend_num	Vendor the PO is placed with	nvarchar vendor code	vendor_mst.vend_num, vendaddr_mst.vend_num	Vendor name is not on po_mst; join vendaddr_mst
po_mst	order_date	Date the PO was entered or ordered	datetime	-	Use for PO volume by month; use poitem due_date for lateness
po_mst	stat	Header status	Single-letter code, for example P planned, O ordered, C complete	-	Decode on your database with SELECT stat, COUNT(*) FROM po_mst GROUP BY stat
po_mst	type	PO type	R regular, B blanket	-	Blanket POs carry releases; filter type when counting regular POs
po_mst	buyer	Buyer responsible for the PO	nvarchar buyer code	-	Group open lines by buyer for a chase list
po_mst	terms_code	Payment terms on the PO	Terms code	terms_mst.terms_code	Defaults from the vendor; can differ per PO
po_mst	whse	Default receiving warehouse	Warehouse code	whse_mst.whse	Lines can override; report the line whse for receiving
poitem_mst	po_num	PO number of the line	Same as po_mst.po_num	po_mst.po_num	Part of the line key with po_line and po_release
poitem_mst	po_line	Line number	smallint	-	Key: po_num + po_line + po_release
poitem_mst	po_release	Release number on the line	smallint, 0 on regular PO lines	-	Leaving po_release out of a join duplicates blanket release rows
poitem_mst	item	Item ordered	Item code, or a non-inventory item	item_mst.item	LEFT JOIN item_mst so non-inventory lines are not dropped
poitem_mst	qty_ordered	Quantity ordered in the item stocking U/M	decimal	-	Open qty = qty_ordered - qty_received
poitem_mst	qty_received	Quantity received to date, stocking U/M	decimal	-	Compare with qty_voucher to find received but not invoiced
poitem_mst	qty_voucher	Quantity vouchered (invoiced) to date	decimal	-	Received not vouchered = qty_received - qty_voucher
poitem_mst	u_m	Purchase unit of measure on the line	U/M code	u_m_mst.u_m	qty_ordered is in stocking U/M; the _conv columns hold the purchase U/M values
poitem_mst	item_cost	Unit cost in stocking U/M, PO currency	decimal	-	Open value = (qty_ordered - qty_received) * item_cost
poitem_mst	due_date	Date the line is due	datetime	-	Late = due_date < GETDATE() and qty_ordered - qty_received > 0
poitem_mst	stat	Line status	P planned, O ordered, F filled, C complete	-	For open lines use stat = 'O'; F means fully received but not closed
poitem_mst	whse	Receiving warehouse for the line	Warehouse code	whse_mst.whse	Use this, not po_mst.whse, for receiving dock reports
poitem_mst	ref_type	What the line supplies	I inventory, J job, O customer order (others exist)	-	Drives which table ref_num points to
poitem_mst	ref_num	Job or customer order the line supplies	Job or order number, blank for inventory	job_mst.job when J, co_mst.co_num when O	Use with ref_line_suf and ref_release for the exact job suffix or order line
poitem_mst	ref_line_suf	Job suffix or customer order line of the reference	smallint	job_mst.suffix or coitem_mst.co_line	Meaning depends on ref_type
vendor_mst	vend_num	Vendor code, the vendor key	nvarchar	po_mst.vend_num	One row per vendor
vendor_mst	curr_code	Vendor currency	Currency code	currency_mst.curr_code	PO costs are in vendor currency; convert before summing across vendors
vendor_mst	terms_code	Default payment terms	Terms code	terms_mst.terms_code	Default copied to new POs
vendaddr_mst	vend_num	Vendor code for the address row	nvarchar	vendor_mst.vend_num	Holds the vendor name and address
vendaddr_mst	name	Vendor name	nvarchar	-	Join po_mst.vend_num = vendaddr_mst.vend_num for the name on reports
matltran_mst	ref_type	Source document type of the material transaction	P for purchase order rows	-	PO receipts and returns: ref_type = 'P'
matltran_mst	ref_num	PO number for ref_type P rows	nvarchar	poitem_mst.po_num	Join ref_num = po_num
matltran_mst	ref_line_suf	PO line for ref_type P rows	smallint	poitem_mst.po_line	Join ref_line_suf = po_line
matltran_mst	ref_release	PO release for ref_type P rows	smallint	poitem_mst.po_release	Join ref_release = po_release
matltran_mst	trans_type	Kind of material transaction	Single-letter code	-	Decode receipts vs returns with SELECT trans_type, COUNT(*) FROM matltran_mst WHERE ref_type = 'P' GROUP BY trans_type
matltran_mst	trans_date	Date of the receipt or return	datetime	-	Use for receipts by day; join to the PO line for due vs actual
matltran_mst	qty	Quantity moved, stocking U/M	decimal, sign shows direction	-	Sum qty per PO line to reconcile with poitem_mst.qty_received

Applies to SyteLine 9.x and CSI 10.x on SQL Server, where base tables carry the _mst suffix and site-specific views drop it. Status and type letters vary slightly by release and by user-defined codes, so decode them on your own database before hard-coding a filter. Blanket PO lines live in their own table; find it on your version with SELECT name FROM sys.tables WHERE name LIKE 'po%' before joining. Run report queries against a reporting copy or with a read-only login.

Get your open PO report checked

Send us the SQL or report definition you use for open and late PO lines and we will check the joins and quantity basis against your SyteLine version.

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

Was this tool useful?

How to use it

Search by table name to see one table, or by a word such as late, voucher or vendor to see every column involved in that question. The Join to column tells you which key connects the row to the next table. The Report tip column gives the expression you usually need, such as open quantity. Export to Excel and add your own Uf_ user fields so a new report writer has one page for the purchasing schema. Column names here are database names; IDO property names on SLPos and SLPoItems are different (PoNum, VendNum, QtyOrdered and so on), so use those when you build a form, a report on an IDO or a REST load.

The formulas behind common PO reports

Open quantity is qty_ordered minus qty_received, both in the item stocking unit of measure. Open value is open quantity times item_cost, which is in the PO currency, so convert with the vendor curr_code before you add lines from different vendors. A line is late when due_date is before today and open quantity is above zero; filtering on line stat = 'O' removes planned and completed lines. Received but not vouchered is qty_received minus qty_voucher and drives the accrued liability check at month end. For receipt history, matltran_mst rows with ref_type 'P' carry the PO number, line and release in ref_num, ref_line_suf and ref_release.

Open qty = qty_ordered - qty_received
Late = due_date < GETDATE() AND open qty > 0
Unvouchered = qty_received - qty_voucher
Open value = open qty * item_cost, in PO currency

Worked example: open and late PO lines

Start from poitem_mst p, join po_mst h on h.po_num = p.po_num, then vendaddr_mst v on v.vend_num = h.vend_num for the vendor name. Select p.po_num, p.po_line, p.po_release, p.item, p.due_date, p.qty_ordered - p.qty_received AS open_qty and DATEDIFF(day, p.due_date, GETDATE()) AS days_late. Filter p.stat = 'O' and p.qty_ordered > p.qty_received. Order by days_late descending and you have the expediting list. Add p.ref_type and p.ref_num to show which job or customer order is waiting on each late line, which is the column buyers ask for first.

Gotchas

The most common mistake is joining receipts or lines on po_num only, which multiplies rows when a PO has several lines or releases. Always carry po_line and po_release. The second is reading quantities in the wrong unit: poitem_mst holds stocking U/M values in qty_ordered and qty_received, while the _conv columns hold purchase U/M values shown on the form. The third is querying the base _mst table in a multi-site database without a site_ref filter, which mixes sites. Finally, non-inventory lines have no item_mst row, so use LEFT JOIN when you bring in item descriptions.

Frequently Asked Questions

Which table holds SyteLine purchase order lines?

poitem_mst holds PO lines and releases, keyed by po_num, po_line and po_release. The header is po_mst, keyed by po_num. Receipts and returns against a line are written to matltran_mst with ref_type 'P', carrying the PO number, line and release in ref_num, ref_line_suf and ref_release.

How do I build a custom SyteLine purchase order report?

Write and test the SQL on a reporting copy, or better, base the report on the SLPoItems IDO so it respects site and security. For SSRS or Crystal, join poitem_mst to po_mst and vendaddr_mst with the keys in this table. Register the report in SyteLine through the report options and background task setup if users must launch it from a form.

Where is the vendor name in SyteLine?

The vendor name and address are on vendaddr_mst, not vendor_mst. vendor_mst holds terms, currency and other purchasing defaults. Join both on vend_num. A PO header carries vend_num only, so any report that shows a vendor name needs the vendaddr_mst join.

Why do my PO receipt totals not match qty_received?

Usually the join is missing po_release or ref_line_suf, so rows repeat, or returns are excluded. Sum matltran_mst.qty for ref_type 'P' with all three PO keys and include return transactions. Also check the unit of measure: matltran_mst and qty_received are both in stocking U/M.

If your buyers want to ask SyteLine which PO lines are late and what they hold up, in plain English and on your own servers, we can set that up read-only.

Talk to an Expert Explore More Free Tools