# SyteLine SQL User-Defined Data Types Reference for Custom Procedures

> Source: https://netray.co/tools/syteline-sql-user-defined-data-types-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

The SQL Server user-defined data types SyteLine uses for columns and stored procedure parameters, with scripts to read exact lengths from your own database.

## Category

developer

## Erp

Infor SyteLine

## Audience

- SyteLine developer
- DBA
- Mongoose developer

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### UDDT name

**Key:** uddt

**Type:** text

#### Base SQL type

**Key:** base

**Type:** text

#### Used for (example columns)

**Key:** used

**Type:** text

#### Use it in custom code for

**Key:** custom

**Type:** text

**Rows:**

**Uddt:** InfobarType

**Base:** nvarchar, long message length

**Used:** @Infobar OUTPUT parameter on base stored procedures

**Custom:** Declare @Infobar InfobarType OUTPUT on every custom procedure called from an IDO method

**Uddt:** ItemType

**Base:** nvarchar

**Used:** item_mst.item, jobmatl_mst.item, poitem_mst.item

**Custom:** Item parameters and Uf_ columns that hold an item code

**Uddt:** DescriptionType

**Base:** nvarchar

**Used:** item_mst.description and other description columns

**Custom:** Description parameters so text is not truncated differently from base

**Uddt:** CustNumType

**Base:** nvarchar

**Used:** customer_mst.cust_num, co_mst.cust_num

**Custom:** Customer number parameters

**Uddt:** CoNumType

**Base:** nvarchar

**Used:** co_mst.co_num, coitem_mst.co_num

**Custom:** Customer order number parameters

**Uddt:** CoLineType

**Base:** smallint

**Used:** coitem_mst.co_line

**Custom:** Order line parameters

**Uddt:** CoReleaseType

**Base:** smallint

**Used:** coitem_mst.co_release

**Custom:** Order release parameters

**Uddt:** JobType

**Base:** nvarchar

**Used:** job_mst.job, jobroute_mst.job

**Custom:** Job number parameters

**Uddt:** SuffixType

**Base:** smallint

**Used:** job_mst.suffix

**Custom:** Job suffix parameters

**Uddt:** OperNumType

**Base:** int

**Used:** jobroute_mst.oper_num, jobmatl_mst.oper_num

**Custom:** Operation number parameters

**Uddt:** WhseType

**Base:** nvarchar

**Used:** whse_mst.whse, itemwhse_mst.whse

**Custom:** Warehouse parameters

**Uddt:** LocType

**Base:** nvarchar

**Used:** location columns such as itemloc_mst.loc

**Custom:** Stock location parameters

**Uddt:** LotType

**Base:** nvarchar

**Used:** lot_mst.lot, lot columns on transactions

**Custom:** Lot number parameters

**Uddt:** QtyUnitType

**Base:** decimal

**Used:** Unit quantities such as qty_ordered

**Custom:** Quantity variables so precision matches base calculations

**Uddt:** CostPrcType

**Base:** decimal

**Used:** Unit cost and price columns

**Custom:** Cost and price variables

**Uddt:** AmountType

**Base:** decimal

**Used:** Extended amounts on orders and ledgers

**Custom:** Totals and money variables

**Uddt:** DateType

**Base:** datetime

**Used:** Date columns such as due_date

**Custom:** Date parameters and Uf_ date columns

**Uddt:** ListYesNoType

**Base:** tinyint

**Used:** Yes/no flag columns stored as 0 or 1

**Custom:** Boolean flags; compare with 0 and 1

**Uddt:** FlagNyType

**Base:** tinyint

**Used:** Flag columns stored as 0 or 1

**Custom:** Boolean flags where base uses FlagNyType

**Uddt:** RowPointerType

**Base:** uniqueidentifier

**Used:** RowPointer column on every application table

**Custom:** Row identity in custom code; never compare as a string

**Uddt:** SiteType

**Base:** nvarchar

**Used:** site_ref and site columns

**Custom:** Site parameters in multi-site procedures

**Uddt:** UsernameType

**Base:** nvarchar

**Used:** CreatedBy and UpdatedBy audit columns

**Custom:** User name variables in audit or logging code

**Uddt:** VendNumType

**Base:** nvarchar

**Used:** vendor_mst.vend_num, po_mst.vend_num

**Custom:** Vendor number parameters

**Uddt:** PoNumType

**Base:** nvarchar

**Used:** po_mst.po_num, poitem_mst.po_num

**Custom:** PO number parameters

**Uddt:** UMType

**Base:** nvarchar

**Used:** u_m columns

**Custom:** Unit of measure parameters

**Notes:** 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;

## Content

**Intro:** 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.

**Sections:**

#### Why the types matter in custom code

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What is an EXTGEN stored procedure in SyteLine?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

infor mongoose extgen stored procedure, syteline ido development guide, infor syteline ido, ido in syteline, syteline user defined data types, syteline infobartype, syteline stored procedure parameters, syteline uddt list

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json