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

SyteLine Material Transaction and Reference Types Reference

This reference decodes Infor SyteLine 8, 9 and 10 material transactions: the 17 transaction types and 10 reference types, where each comes from, how it moves on-hand and whether it belongs in an item usage report. A decode query lets you map the stored codes in your own matltran_mst table.

Download:

List	Type	Typically created by	Effect on on-hand	Reference fields	Counts as usage
Transaction type	Adjustment	Quantity Adjustment	+ or - by sign of quantity	Usually Inventory reference	No
Transaction type	Misc Receipt	Miscellaneous Receipt	+	Usually Inventory reference	No
Transaction type	Misc Issue	Miscellaneous Issue	-	Usually Inventory reference	Depends
Transaction type	Stock Move	Quantity Move	None at warehouse level; moves between locations	Inventory	No
Transaction type	Other Cost	Cost-only postings	None	Check the reference type on the row	No
Transaction type	Receipt	Purchase Order Receiving and other receipts	+ when received to stock	Purchase Order: PO number, line, release	No
Transaction type	Ship	Order Shipping	- (returns reverse the sign)	Customer Order: order, line, release	Yes
Transaction type	Transfer	Transfer order ship and receive	- at source, + at destination	Transfer: transfer order and line	Depends
Transaction type	Transfer Loss	Transfer order receipt with loss	Removes quantity lost in transit	Transfer	No
Transaction type	Cycle Count	Cycle Count Update	+ or - by count variance	Inventory	No
Transaction type	Physical Inventory	Physical inventory posting	+ or - by count variance	Inventory	No
Transaction type	Finish	Job completion to stock (job receipt)	+ finished item	Job: job, suffix, operation	No
Transaction type	Labor/Next Operation	Job labor and move transactions	None; moves quantity between operations	Job	No
Transaction type	Issue/WIP Change	Job Material Transactions	- on issue, + on return	Job: job, suffix, operation	Yes
Transaction type	Withdrawal/Return	Material withdrawals and returns against an order	- on withdrawal, + on return	Check the reference type on the row	Yes
Transaction type	Split/Merge/Create	Lot split, merge and create activity	Net zero across lots when balanced	Inventory	No
Transaction type	Scrap	Scrap reported on job operations	None on stock; reduces WIP	Job	Depends
Reference type	Inventory	Inventory forms not tied to an order	See transaction type	Reference number usually blank	Depends
Reference type	Customer Order	Order Shipping and order returns	See transaction type	ref_num = order, ref_line_suf = line, ref_release = release	Yes
Reference type	Purchase Order	Purchase Order Receiving and returns	See transaction type	ref_num = PO, ref_line_suf = line, ref_release = release	No
Reference type	RMA	RMA return processing	See transaction type	RMA number and line	No
Reference type	Transfer	Transfer order ship and receive	See transaction type	Transfer order number and line	Depends
Reference type	Job	Job material, labor and completion	See transaction type	ref_num = job, ref_line_suf = suffix, ref_release = operation	Yes
Reference type	SRO	Service order material	See transaction type	SRO number and its line or operation	Yes
Reference type	Project JIT Production	Project just-in-time production	See transaction type	Project identifiers	Depends
Reference type	Production Schedule	Production schedule (repetitive) transactions	See transaction type	Production schedule identifiers	Depends
Reference type	Work Center	Work Center Material Transactions	See transaction type	Work center	Yes

Type and reference lists follow the SyteLine Material Transactions report and Reference Type help. Single-letter stored codes are left blank on purpose: map them in your own database with SELECT trans_type, ref_type, COUNT(*), MIN(trans_date), MAX(trans_date) FROM matltran_mst GROUP BY trans_type, ref_type and compare a few rows with the Material Transactions form. Confirm column names in SSMS for your version.

Get your usage report checked

Send the output of the decode query and your report SQL, and we will check the transaction types you include against how your site actually posts inventory.

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

Was this tool useful?

How to use it

Every inventory movement in SyteLine writes a row to the material transaction table, matltran_mst, shown on the Material Transactions form. Two fields tell you what happened: the transaction type (what kind of movement) and the reference type (what document it belongs to). Use the Transaction type rows to decide which movements to include in a report and the Reference type rows to know how to read ref_num, ref_line_suf and ref_release. Then run the decode query from the notes and fill the Stored code column for your database, since that is what your SQL will filter on.

Worked example: an item usage report

Usage normally means consumption: material issued to jobs, shipped to customers, issued to service orders and consumed at work centers. Start from matltran_mst, keep the Ship and Issue/WIP Change transaction types, and add Withdrawal/Return and Misc Issue only if your site uses them for consumption rather than corrections. Exclude Stock Move, Cycle Count, Physical Inventory and Adjustment, which correct quantities rather than consume them. Group by item and month on trans_date, and sum quantity with its sign so returns net against issues instead of inflating usage.

Include: Ship, Issue/WIP Change, work center and SRO issues
Decide per site: Misc Issue, Withdrawal/Return, Scrap, Transfer
Exclude: Stock Move, Cycle Count, Physical Inventory, Adjustment

Inventory aging from transactions

An aging report needs the date each unit on hand arrived. For lot-tracked items, use the lot creation or receipt date. For untracked items, a common method is first-in-first-out back-allocation: take current on-hand, walk receipt-type rows (Receipt, Finish, Misc Receipt and positive adjustments) from newest to oldest until the quantity is covered, and age each slice by its trans_date. Leave Stock Move rows out, since they change location, not age. State the method on the report so readers know it is an estimate built from transactions.

Gotchas

Do not hard-code letters you found in a forum: map trans_type and ref_type from your own data with the decode query, because a wrong letter silently drops a whole class of rows. The sign of the quantity carries the direction, so a customer return through Order Shipping can appear as a Ship row with negative quantity. Reference fields change meaning by reference type: for jobs ref_line_suf is the job suffix, for orders it is the line. Large sites have millions of rows, so filter on trans_date and item in reports and run them on a reporting copy.

Frequently Asked Questions

Which SyteLine table holds material transactions?

matltran_mst holds the material transaction history shown on the Material Transactions form, one row per movement with item, warehouse, location, transaction type, reference type, reference number fields, date and quantity. Related cost detail is stored with the transaction. Use it for usage, movement and aging reports.

What are the trans_type codes in matltran?

The stored values are single-letter codes mapped to the 17 transaction types in this table. We do not list letters because they must be confirmed for your version. Run SELECT trans_type, ref_type, COUNT(*) FROM matltran_mst GROUP BY trans_type, ref_type and compare sample rows with the Material Transactions form to map them.

How do I build an item usage report in SyteLine?

Filter matltran_mst to consumption transaction types (shipments and job issues, plus any site-specific issue types), sum signed quantity by item and period on trans_date, and exclude moves, counts and adjustments. Returns then net against issues. Check totals against the Material Transactions form for one item before trusting the report.

Why does my SyteLine inventory aging not match on-hand?

Usually because moves or counts were treated as receipts, or because the back-allocation started from the wrong on-hand figure. Age only receipt-type rows, start from current on-hand per item and warehouse, and treat counts and adjustments consistently. Lot-tracked items are better aged from lot dates than from transactions.

If you want planners to ask 'what did we use of this item last quarter' in plain English against SyteLine on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools