SyteLine Custom Stored Procedure Generator for IDO Methods

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'ItemType', N'WhseType', N'QtyUnitType', N'InfobarType')
ORDER BY t.name;

CREATE PROCEDURE dbo.UETItemReorderCheckSp (
    @Item      ItemType
  , @Whse      WhseType
  , @QtyShort  QtyUnitType   OUTPUT
  , @Infobar   InfobarType    OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Severity INT = 0;
  DECLARE @OnHand   QtyUnitType;
  DECLARE @Reorder  QtyUnitType;

  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'No warehouse record for item and warehouse: ' + 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

DECLARE @rc       INT;
DECLARE @QtyShort QtyUnitType;
DECLARE @Infobar  InfobarType;

-- Set the site context for this connection first, as your SyteLine version requires.
EXEC @rc = dbo.UETItemReorderCheckSp
     @Item     = N'PN-48213',
     @Whse     = N'MAIN',
     @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.

-- 1. On the IDOs form, open (or create) the custom IDO UETItemChecks and check it out.
-- 2. Add a method named UETItemReorderCheckSp 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/UETItemChecks?method=UETItemReorderCheckSp with the parameters in the same order.
-- 6. Grant the user groups that need it execute rights on the IDO method.