Stored Procedure Critical Number Examples
Setting up a program to create a simple critical number is relatively easy.
The first step is to 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; using a prefix that includes your company name is one way to do this.
- 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.
Next, open Query Analyzer or your preferred editor and create your custom stored procedure. The parameters of every critical number are the same and need to look like this (where SSSWBCanCoBookSp is the name of your procedure):
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
Write the logic to calculate your value and assign it to @Amount. The amount is returned to be displayed to the user.
In order to retrieve any parameters that you may have set up, you can call a standard function. It is dbo.SSSWBGetParm. 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, do the following:
Past Due Order Lines Example
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.SSSWBGetParm(@CrNum, 'COStatusList'), 'POS')
SET @CoitemStatList = ISNULL(dbo.SSSWBGetParm(@CrNum, 'COITEMStatusList'), 'PO')
SET @CredHold = ISNULL(dbo.SSSWBGetParm(@CrNum, 'CredHold'), 0)
SET @LateDays = ISNULL(dbo.SSSWBGetParm(@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') >= 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