# SyteLine Custom Stored Procedure Generator for IDO Methods

> Source: https://netray.co/tools/syteline-custom-stored-procedure-ido-method-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a SyteLine-style stored procedure with typed parameters, @Infobar messaging and a severity return code, then test it and publish it as an IDO method.

## Category

developer

## Erp

Infor SyteLine

## Audience

- SyteLine developer
- SQL Server DBA
- Integration engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Procedure name (end in Sp)

**Key:** proc

**Type:** text

**Default:** UETItemReorderCheckSp

**Help:** Use a distinctive prefix so it can never collide with a base Infor procedure.

#### UDDT for item

**Key:** item_type

**Type:** text

**Default:** ItemType

#### UDDT for warehouse

**Key:** whse_type

**Type:** text

**Default:** WhseType

#### UDDT for quantity

**Key:** qty_type

**Type:** text

**Default:** QtyUnitType

**Help:** Confirm with block 1 query; use whichever quantity type your database defines for itemwhse quantities.

#### Not-found message text

**Key:** err

**Type:** text

**Default:** No warehouse record for item and warehouse:

#### Test item

**Key:** test_item

**Type:** text

**Default:** PN-48213

#### Test warehouse

**Key:** test_whse

**Type:** text

**Default:** MAIN

#### IDO that will publish the method

**Key:** ido

**Type:** text

**Default:** UETItemChecks

**Blocks:**

#### 1. Confirm the user-defined data types exist

**Body:** SELECT t.name AS uddt, TYPE_NAME(t.system_type_id) AS base_type, t.max_length, t.precision, t.scale
FROM sys.types AS t
WHERE t.is_user_defined = 1
  AND t.name IN (N'{{item_type}}', N'{{whse_type}}', N'{{qty_type}}', N'InfobarType')
ORDER BY t.name;

#### 2. Stored procedure

**Body:** CREATE PROCEDURE dbo.{{proc}} (
    @Item      {{item_type}}
  , @Whse      {{whse_type}}
  , @QtyShort  {{qty_type}}   OUTPUT
  , @Infobar   InfobarType    OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Severity INT = 0;
  DECLARE @OnHand   {{qty_type}};
  DECLARE @Reorder  {{qty_type}};

  SET @QtyShort = 0;
  SET @Infobar  = NULL;

  -- Site view (itemwhse, not itemwhse_mst): rows are limited to the session site.
  -- When you test outside the application, set the site context first, as your version requires.
  SELECT @OnHand = iw.qty_on_hand,
         @Reorder = iw.qty_reorder
  FROM itemwhse AS iw
  WHERE iw.item = @Item
    AND iw.whse = @Whse;

  IF @@ROWCOUNT = 0
  BEGIN
    SET @Infobar = N'{{err}} ' + ISNULL(@Item, N'') + N' / ' + ISNULL(@Whse, N'');
    SET @Severity = 16;
    RETURN @Severity;
  END

  IF ISNULL(@OnHand, 0) < ISNULL(@Reorder, 0)
    SET @QtyShort = ISNULL(@Reorder, 0) - ISNULL(@OnHand, 0);

  RETURN @Severity;
END
GO

#### 3. Test harness

**Body:** DECLARE @rc       INT;
DECLARE @QtyShort {{qty_type}};
DECLARE @Infobar  InfobarType;

-- Set the site context for this connection first, as your SyteLine version requires.
EXEC @rc = dbo.{{proc}}
     @Item     = N'{{test_item}}',
     @Whse     = N'{{test_whse}}',
     @QtyShort = @QtyShort OUTPUT,
     @Infobar  = @Infobar  OUTPUT;

SELECT @rc AS return_code, @QtyShort AS qty_short, @Infobar AS infobar;
-- Expect return_code 0 and a qty_short for a real item/warehouse,
-- and return_code 16 with a message for a pair that does not exist.

#### 4. Publish it as an IDO method

**Body:** -- 1. On the IDOs form, open (or create) the custom IDO {{ido}} and check it out.
-- 2. Add a method named {{proc}} with method type Stored Procedure.
-- 3. Load or enter the parameters in procedure order: Item, Whse, QtyShort, Infobar.
--    Mark QtyShort and Infobar as output parameters.
-- 4. Check the IDO in and refresh the IDO metadata cache the way your version requires.
-- 5. Call it from a form event handler (method call), a form script, or REST:
--    POST .../ido/invoke/{{ido}}?method={{proc}} with the parameters in the same order.
-- 6. Grant the user groups that need it execute rights on the IDO method.

**Notes:** Verify for your version: the UDDT names (block 1), the itemwhse column names qty_on_hand and qty_reorder, and how your release sets site context for a raw SQL session. Return 0 for success and a non-zero severity with a message in @Infobar for errors, which is how the application shows the error to the user.

## Content

**Intro:** This generator writes a SQL Server stored procedure that follows SyteLine conventions so it can be published as an IDO method on a custom IDO. You get the procedure, a test harness, a UDDT check and the publishing steps.

**Sections:**

#### How to use it

**Body:** Name the procedure with a distinctive prefix and the Sp suffix, then confirm the user-defined data types with block 1 before you create anything. SyteLine defines parameter types such as ItemType, WhseType and InfobarType so lengths match the application; using them keeps your method consistent with base procedures. Create the procedure in a test database, run block 3 with a known item and warehouse, then follow block 4 to publish it. The defaults implement a reorder check: how far the warehouse quantity on hand is below its reorder point.

**Bullets:**

- Read through site views (no _mst suffix) so rows follow the session site.
- Keep parameter order identical between the procedure and the IDO method.
- Never write to base tables directly from a custom method without validation.

#### The @Infobar and severity convention

**Body:** Base SyteLine procedures return an integer severity and pass any message back through an @Infobar output parameter. Zero means success; a non-zero value tells the calling form or IDO that the call failed, and the text in @Infobar is what the user sees. Following the same convention means a form event handler or a REST invoke call can treat your method like a base method: check the return value, show the message, stop. Using 16 for an error is a common choice that mirrors SQL Server error severity; what matters to callers is zero versus non-zero.

#### Worked example

**Body:** Item PN-48213 in warehouse MAIN has qty_on_hand 40 and qty_reorder 100. The test harness returns return_code 0 and qty_short 60. For item PN-99999, which has no itemwhse row in MAIN, it returns return_code 16, qty_short 0 and infobar No warehouse record for item and warehouse: PN-99999 / MAIN. Published on UETItemChecks, a planner's form can call the method from a button and display the shortage, and an integration can call the same method through IDO invoke.

#### Gotchas

**Body:** Running the test in SSMS without a site context can return no rows from site views even when data exists, which looks like a bug in your procedure. Changing parameter order after publishing breaks every caller silently, because IDO invoke passes parameters by position. Custom procedures are yours to maintain: keep them in source control with a deployment script, and re-test after each Infor update in case a base table or UDDT changes. For logic that must run in the cloud, an IDO extension class is the long-term home.

**Cta:** If you want planners to ask questions like this of SyteLine in plain English on your own servers, we can expose methods like this one to a private, read-only assistant.

**Faq:**

#### What is an IDO method in SyteLine?

**Answer:** An IDO method is an operation published on an IDO that forms, scripts and integrations can call. It can be backed by a stored procedure or by code in an extension class. Callers pass parameters by position, and output parameters come back to the caller, which is why the stored procedure signature must match the method definition.

#### Why use UDDTs instead of NVARCHAR?

**Answer:** SyteLine UDDTs such as ItemType carry the lengths the application expects. If Infor changes a length in a later release, objects declared with the UDDT follow it, while hard-coded NVARCHAR lengths can truncate values. Check the types exist with the sys.types query before you create the procedure.

#### Should I use the site view or the _mst table?

**Answer:** Inside methods called from the application, read site views such as itemwhse so rows follow the session site. Use _mst tables with an explicit site_ref filter only for cross-site reporting. Mixing them is a common cause of duplicate rows in multi-site databases.

#### How do custom parameters show up in Mongoose?

**Answer:** When you add the method to the IDO you define its parameters in procedure order and mark which are output. Form event handlers then pass properties or variables to them, and REST invoke calls pass them as a JSON array. Output parameter values are returned after the call completes.

## Search terms

ido in syteline, syteline ido development guide, infor mongoose custom parameters, syteline stored procedure ido method, syteline infobar output parameter, syteline custom ido method, csi stored procedure severity, infor mongoose extgen stored procedure

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