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

SyteLine SQL User-Defined Data Types Reference for Custom Procedures

SyteLine 9 and CSI 10 define their columns and stored procedure parameters with SQL Server user-defined data types such as ItemType, JobType and InfobarType. This reference lists the common ones and gives the scripts to read their exact base type and length from your own database, so custom procedures and Uf_ columns match base.

Download:

UDDT name	Base SQL type	Used for (example columns)	Use it in custom code for
InfobarType	nvarchar, long message length	@Infobar OUTPUT parameter on base stored procedures	Declare @Infobar InfobarType OUTPUT on every custom procedure called from an IDO method
ItemType	nvarchar	item_mst.item, jobmatl_mst.item, poitem_mst.item	Item parameters and Uf_ columns that hold an item code
DescriptionType	nvarchar	item_mst.description and other description columns	Description parameters so text is not truncated differently from base
CustNumType	nvarchar	customer_mst.cust_num, co_mst.cust_num	Customer number parameters
CoNumType	nvarchar	co_mst.co_num, coitem_mst.co_num	Customer order number parameters
CoLineType	smallint	coitem_mst.co_line	Order line parameters
CoReleaseType	smallint	coitem_mst.co_release	Order release parameters
JobType	nvarchar	job_mst.job, jobroute_mst.job	Job number parameters
SuffixType	smallint	job_mst.suffix	Job suffix parameters
OperNumType	int	jobroute_mst.oper_num, jobmatl_mst.oper_num	Operation number parameters
WhseType	nvarchar	whse_mst.whse, itemwhse_mst.whse	Warehouse parameters
LocType	nvarchar	location columns such as itemloc_mst.loc	Stock location parameters
LotType	nvarchar	lot_mst.lot, lot columns on transactions	Lot number parameters
QtyUnitType	decimal	Unit quantities such as qty_ordered	Quantity variables so precision matches base calculations
CostPrcType	decimal	Unit cost and price columns	Cost and price variables
AmountType	decimal	Extended amounts on orders and ledgers	Totals and money variables
DateType	datetime	Date columns such as due_date	Date parameters and Uf_ date columns
ListYesNoType	tinyint	Yes/no flag columns stored as 0 or 1	Boolean flags; compare with 0 and 1
FlagNyType	tinyint	Flag columns stored as 0 or 1	Boolean flags where base uses FlagNyType
RowPointerType	uniqueidentifier	RowPointer column on every application table	Row identity in custom code; never compare as a string
SiteType	nvarchar	site_ref and site columns	Site parameters in multi-site procedures
UsernameType	nvarchar	CreatedBy and UpdatedBy audit columns	User name variables in audit or logging code
VendNumType	nvarchar	vendor_mst.vend_num, po_mst.vend_num	Vendor number parameters
PoNumType	nvarchar	po_mst.po_num, poitem_mst.po_num	PO number parameters
UMType	nvarchar	u_m columns	Unit of measure parameters

Lengths, precision and scale are set per database and can change between releases, so read them from your own SyteLine database instead of copying numbers. List all UDDTs: SELECT t.name, TYPE_NAME(t.system_type_id) AS base_type, t.max_length, t.precision, t.scale FROM sys.types t WHERE t.is_user_defined = 1 ORDER BY t.name; For nvarchar types max_length is in bytes, so divide by 2 for characters. Find which tables use one type: SELECT OBJECT_NAME(c.object_id) AS table_name, c.name AS column_name FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id WHERE t.name = 'ItemType' AND OBJECT_NAME(c.object_id) LIKE '%_mst' ORDER BY 1, 2;

Get your custom procedures reviewed

Send us a custom or EXTGEN procedure and we will check its parameter types, Infobar handling and upgrade safety against your SyteLine release.

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

Was this tool useful?

Why the types matter in custom code

Base SyteLine procedures declare parameters with these types, for example an item parameter as ItemType and the message output as @Infobar InfobarType OUTPUT. If your EXTGEN copy, custom procedure or Uf_ column uses a hand-picked nvarchar(20) where the base type is longer, values are silently truncated and joins fail on padded keys. Declaring with the same UDDT keeps your code in step with the database, including after an upgrade that lengthens a type. IDO methods that call a procedure pass @Infobar back to the form, so a non-zero return value plus a message in Infobar is the standard way to report an error.

How to use it

Search for the business object you are coding against, such as job or vendor, to find the type to declare. Then run the first script in the notes on a development copy of your application database to get the base type, max_length, precision and scale your release actually uses. Use the second script to see every _mst column declared with a type, which shows where a value travels and which joins expect it. Export the result to Excel and keep it next to your coding standards so every developer declares parameters the same way.

Declare parameters with the UDDT, not the base type
Create Uf_ columns with the matching UDDT
Return non-zero and set @Infobar on error

Worked example: a custom procedure signature

A procedure that returns open quantity for an item in a warehouse would be declared as CREATE PROCEDURE dbo.UET_ItemOpenQtySp @Item ItemType, @Whse WhseType, @OpenQty QtyUnitType OUTPUT, @Infobar InfobarType OUTPUT AS. Inside, any working variable that holds a cost uses CostPrcType and any total uses AmountType so rounding matches base calculations. When the procedure is called from an IDO method, map @Infobar to the method's Infobar parameter. Prefix custom objects with your own code so upgrades never overwrite them, and script them into source control.

Gotchas

max_length in sys.types is bytes, so an nvarchar type showing 60 holds 30 characters. Decimal types differ in precision and scale; adding a QtyUnitType to a CostPrcType in a variable of the wrong type rounds silently. A UDDT cannot be altered while columns use it, so never try to lengthen one yourself: that is an Infor schema change delivered by upgrade. When you copy an object to another database, script the UDDTs first or the CREATE fails. Some names differ between releases, so confirm each one with the lookup script before using it.

Frequently Asked Questions

What is an EXTGEN stored procedure in SyteLine?

An EXTGEN procedure is a copy of a base stored procedure whose name is the base name prefixed with EXTGEN_. Base procedures that support it check for the EXTGEN_ version and call it instead, so you can change behaviour without editing Infor code. Declare its parameters with exactly the same UDDTs as the base signature, and list your EXTGEN procedures with SELECT name FROM sys.procedures WHERE name LIKE 'EXTGEN[_]%' before every upgrade.

What is InfobarType?

InfobarType is the user-defined type for the @Infobar message parameter on SyteLine stored procedures. A procedure sets @Infobar to a message and returns a non-zero severity on error, and the IDO layer passes that message back to the form or calling code.

How do I see the length of ItemType in my database?

Run SELECT name, TYPE_NAME(system_type_id), max_length FROM sys.types WHERE name = 'ItemType' on the application database. For nvarchar types divide max_length by 2 to get characters. Run it on every environment before an upgrade, because releases can change lengths.

Do IDO property types follow these UDDTs?

IDO properties bound to a column take their type and length from the column, which comes from its UDDT. Unbound or derived properties are defined in IDO metadata, so set their length to match the UDDT of the value they carry to avoid truncation on forms.

If you want an assistant that knows your SyteLine schema and writes custom procedures to these conventions on your own servers, we can set that up for your developers.

Talk to an Expert Explore More Free Tools