Example: Stored Procedure Critical Number

To set up a program for a simple critical number:

  1. Set up your critical number in the Critical Numbers form.
    • Choose a program name for your custom stored procedure.

      We recommend that you create a naming convention for your custom procedures so they do not conflict with current or future procedures. For example, you can use a prefix that includes your company name.

    • Choose the parameters you want to be able to set without changing the values in the code. Careful planning of parameter definitions can make the same program usable for multiple critical numbers you want to retrieve. For example, you can set a specific buyer's id or make the same program run against three different warehouses.
    • Keep in mind that you can override any and all settings on this form in your code.
  2. Open Query Analyzer or your preferred editor and create your custom stored procedure. The parameters of every critical number are the same. For example:
    
CREATE PROCEDURE [dbo].[SSSWBCanCoBookSp] (
  @KPINum    WBKPINumType
, @AsOfDate DateType
, @Amount   AmountType OUTPUT
, @Parm1           WBSourceNameType = NULL
, @Parm2           WBSourceNameType = NULL
, @Parm3           WBSourceNameType = NULL
, @Parm4           WBSourceNameType = NULL
, @Parm5           WBSourceNameType = NULL
, @Parm6           WBSourceNameType = NULL
, @Parm7           WBSourceNameType = NULL
, @Parm8           WBSourceNameType = NULL
, @Parm9           WBSourceNameType = NULL
, @Parm10          WBSourceNameType = NULL
, @Parm11          WBSourceNameType = NULL
, @Parm12          WBSourceNameType = NULL
, @Parm13          WBSourceNameType = NULL
, @Parm14          WBSourceNameType = NULL
, @Parm15          WBSourceNameType = NULL
, @Parm16          WBSourceNameType = NULL
, @Parm17          WBSourceNameType = NULL
, @Parm18          WBSourceNameType = NULL
, @Parm19          WBSourceNameType = NULL
, @Parm20          WBSourceNameType = NULL
, @Parm21          WBSourceNameType = NULL
, @Parm22          WBSourceNameType = NULL
, @Parm23          WBSourceNameType = NULL
, @Parm24          WBSourceNameType = NULL
, @Parm25          WBSourceNameType = NULL
, @Parm26          WBSourceNameType = NULL
, @Parm27          WBSourceNameType = NULL
, @Parm28          WBSourceNameType = NULL
, @Parm29          WBSourceNameType = NULL
, @Parm30          WBSourceNameType = NULL
, @Parm31          WBSourceNameType = NULL
, @Parm32          WBSourceNameType = NULL
, @Parm33          WBSourceNameType = NULL
, @Parm34          WBSourceNameType = NULL
, @Parm35          WBSourceNameType = NULL
, @Parm36          WBSourceNameType = NULL
, @Parm37          WBSourceNameType = NULL
, @Parm38          WBSourceNameType = NULL
, @Parm39          WBSourceNameType = NULL
, @Parm40          WBSourceNameType = NULL
, @Parm41          WBSourceNameType = NULL
, @Parm42          WBSourceNameType = NULL
, @Parm43          WBSourceNameType = NULL
, @Parm44          WBSourceNameType = NULL
, @Parm45          WBSourceNameType = NULL
, @Parm46          WBSourceNameType = NULL
, @Parm47          WBSourceNameType = NULL
, @Parm48          WBSourceNameType = NULL
, @Parm49          WBSourceNameType = NULL
, @Parm50          WBSourceNameType = NULL
) AS

    where SSSWBCanCoBookSp is the name of your procedure

  3. Write the logic to calculate your value and assign it to @Amount. The amount is returned to be displayed to the user.
  4. To retrieve any parameters that you may have set up, you can call a standard function. It is dbo.WBGetParm. Pass in the critical number you are dealing with (@KPINum), and the parameter you want to retrieve. The parameter is looked for first in that specific critical number, and then in the general listing on the Critical Number Parameters form. To retrieve a parameter called "Acct" and set it into a variable in your stored procedure, you must follow use this example:
An example of past due order lines:

CREATE PROCEDURE SSSWBCanCoitemPastDueSp (
  @KPINum           WBKPINumType
, @AsOfDate        DateType
, @Amount          AmountType OUTPUT
, @Parm1           WBSourceNameType = NULL
, @Parm2           WBSourceNameType = NULL
, @Parm3           WBSourceNameType = NULL
, @Parm4           WBSourceNameType = NULL
, @Parm5           WBSourceNameType = NULL
, @Parm6           WBSourceNameType = NULL
, @Parm7           WBSourceNameType = NULL
, @Parm8           WBSourceNameType = NULL
, @Parm9           WBSourceNameType = NULL
, @Parm10          WBSourceNameType = NULL
, @Parm11          WBSourceNameType = NULL
, @Parm12          WBSourceNameType = NULL
, @Parm13          WBSourceNameType = NULL
, @Parm14          WBSourceNameType = NULL
, @Parm15          WBSourceNameType = NULL
, @Parm16          WBSourceNameType = NULL
, @Parm17          WBSourceNameType = NULL
, @Parm18          WBSourceNameType = NULL
, @Parm19          WBSourceNameType = NULL
, @Parm20          WBSourceNameType = NULL
, @Parm21          WBSourceNameType = NULL
, @Parm22          WBSourceNameType = NULL
, @Parm23          WBSourceNameType = NULL
, @Parm24          WBSourceNameType = NULL
, @Parm25          WBSourceNameType = NULL
, @Parm26          WBSourceNameType = NULL
, @Parm27          WBSourceNameType = NULL
, @Parm28          WBSourceNameType = NULL
, @Parm29          WBSourceNameType = NULL
, @Parm30          WBSourceNameType = NULL
, @Parm31          WBSourceNameType = NULL
, @Parm32          WBSourceNameType = NULL
, @Parm33          WBSourceNameType = NULL
, @Parm34          WBSourceNameType = NULL
, @Parm35          WBSourceNameType = NULL
, @Parm36          WBSourceNameType = NULL
, @Parm37          WBSourceNameType = NULL
, @Parm38          WBSourceNameType = NULL
, @Parm39          WBSourceNameType = NULL
, @Parm40          WBSourceNameType = NULL
, @Parm41          WBSourceNameType = NULL
, @Parm42          WBSourceNameType = NULL
, @Parm43          WBSourceNameType = NULL
, @Parm44          WBSourceNameType = NULL
, @Parm45          WBSourceNameType = NULL
, @Parm46          WBSourceNameType = NULL
, @Parm47          WBSourceNameType = NULL
, @Parm48          WBSourceNameType = NULL
, @Parm49          WBSourceNameType = NULL
, @Parm50          WBSourceNameType = NULL
) AS
DECLARE @CoStatList  LongListType
, @CoitemStatList    LongListType
, @CredHold          ListYesNoType
, @LateDays          GenericIntType
, @QtyDue            QtyUnitType
, @OrdTotal          AmountType
, @ParmsSite         SiteType
, @CustNum           CustNumType
, @CoNum             CoNumType
, @LineFilter        CoLineType
, @ItemFilter        ItemType
, @ProdCodeFilter    ProductCodeType
, @WhseFilter        WhseType
, @StatFilter        CoitemStatusType
SELECT @ParmsSite = site
FROM parms
SET @CoStatList     = ISNULL(dbo.WBGetParm(@CrNum, 'COStatusList'), 'POS')
SET @CoitemStatList = ISNULL(dbo.WBGetParm(@CrNum, 'COITEMStatusList'), 'PO')
SET @CredHold       = ISNULL(dbo.WBGetParm(@CrNum, 'CredHold'), 0)
SET @LateDays       = ISNULL(dbo.WBGetParm(@CrNum, 'LateDaysTolerance'), 0)
SET @CustNum        = dbo.ExpandKyByType('CustNumType', @Parm1)
SET @CoNum          = dbo.ExpandKyByType('CoNumType', @Parm2)
SET @LineFilter = NULLIF(@Parm3, '')
SET @ItemFilter = NULLIF(@Parm4, '')
SET @ProdCodeFilter = NULLIF(@Parm5, '')
SET @WhseFilter = NULLIF(@Parm6, '')
SET @StatFilter = NULLIF(@Parm7, '')
SELECT @Amount = COUNT(*)
FROM coitem
LEFT OUTER JOIN item itm
  ON itm.item = coitem.item
WHERE (@CoNum IS NULL OR co_num = @CoNum)
  AND charindex(coitem.stat, @CoitemStatList) > 0
  AND qty_ordered > qty_shipped
  AND ISNULL(due_date, '1900-01-01') &GT= dateadd(dd, @LateDays, @AsOfDate)
  AND ship_site = @ParmsSite
  AND EXISTS (SELECT 1 FROM co
              WHERE (@CustNum IS NULL OR co.cust_num = @CustNum)
                AND co.co_num = coitem.co_num
                AND charindex(co.stat, @CoStatList) > 0
                AND co.credit_hold = @CredHold
             )
  AND (@LineFilter IS NULL OR coitem.co_line = @LineFilter)
  AND (@ItemFilter IS NULL OR coitem.item = @ItemFilter)
  AND (@ProdCodeFilter IS NULL OR itm.product_code = @ProdCodeFilter)
  AND EXISTS (SELECT 1 FROM co
              WHERE (@WhseFilter IS NULL OR co.whse = @WhseFilter)
                AND (@StatFilter IS NULL OR co.stat = @StatFilter)
                AND co.co_num = coitem.co_num)
  
RETURN 0
Related topics