TransLution supports two types of stored procedures that are called while doing scanning. On the scanner prompt sequence form, they are referenced by the labels Suggestion Script and External Script. The validation function (External Script) is described in detail below. When using steps, these are called Custom Validations or Suggestion Script.

 

 

Custom Stored Procedure Validation (External Script)

Whenever there is no standard validation that will validate the scanned data as required, a custom validation can be written. A custom validation is when a stored procedure is run after a value has been scanned but before it is logged to the database. If the script returns anything other than an empty string, this is shown on the scanner as an error message and the data is not logged to the scanner data table but rather to a table called SCANNER_UNPROCESSED_DATA which exists only for troubleshooting purposes.

 

The name of the custom validation stored procedure to run for any given step is defined in the window as Standard Validations as shown below.

 

Adding Custom Validations

 

Note how standard validations are shown in blue and custom validations are pink.

 

In order to make the scripts usable, the stored procedure has to know what was scanned and also what job the scan belongs to. This is done by passing two default parameters to the stored procedure - the first is the ID of the current job on the scanner and the second is the current scanned value. These two parameters are always passed to the validation stored procedure. You can define as many parameters as you require in your stored procedure and then map any step values to these parameters exactly as you would for a standard validation.

 

The recommended naming convention for the stored procedures is usp_FunctionArea_Prompt. We find that if you reference both the prompt and the function area in your stored procedure name then if the database ends up with 50 or more stored procedures (which can easily happen) it makes them easier to find when they are grouped by the function area they belong to. e.g. usp_WipReceiving_JobNumber.

 

There are various ways to use this Stored Procedure Validation functionality.

 

 

Error Message

The most common use of stored procedure validation is simply to return an error, forcing the user to scan a new value.  The simple stored procedure below simply confirms that the user scanned the location RECEIVING. If the user has not scanned the correct value or the validation does not return a response of '' (empty string) then the scanning will not be allowed to continue and the user will stay on the same prompt until he has scanned a value that is accepted. Of course, this example would be better done by defaulting the prompt to be RECEIVING and is used for illustrative purposes only.

 

 

USE [TL_StandardFunctions]

GO

/****** Object: StoredProcedure [dbo].[sp_ValidateRECEIVING] Script Date: 01/03/2011 08:56:45 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[sp_ValidateRECEIVING]

@Scan varchar(50),

@jobID int

AS

IF (@Scan <> "RECEIVING")

BEGIN

SELECT 'Not a Valid RECEIVING Location'

END

IF (@Scan = "RECEIVING")

BEGIN

SELECT '' as response

END

 

There is an extension to this feature that works on both EazyTouch and Android. If a function area is defined as a Touch Screen function then it is possible to show errors as a full screen display instead of just as a message box. This is described in detail in the EazyTouch setup options. If this option is selected then all error message for that function area, instead of being shown as a message box, are shown full screen and flashing.

 

 

Y/N Option - While this is still supported for legacy sites, the functionality has been superceded in V6.2 by the Confirm Option.

Another way to do this validation is to warn the operator about the value he has scanned but then allow him to decide to continue and accept this value. The prefix YN: in front of the error message is what tells TransLution if it is a normal error or one with a Y/N option. If it is a normal error the error message returned is shown below. When the user clicks OK they are returned to the original prompt and forced to rescan the value.

 

USE [TL_240]

GO

/****** Object: StoredProcedure [dbo].[sp_ValidateRECEIVING] Script Date: 01/03/2011 08:56:45 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[sp_ValidateRECEIVING]

@Scan varchar(50),

@jobID int

AS

IF (@Scan <> "RECEIVING")

BEGIN

--SELECT 'YN: Not a Valid RECEIVING Location. Continue Anyway?'

END

IF (@Scan = "RECEIVING")

BEGIN

SELECT '' as response

END

 

 

Normal Stored Procedure Validation Error

 

If the Y/N option is used the message looks as below.

 

Yes/No option Stored Procedure Validation Error

 

 

Note that the user can now select Y to accept and continue or N to re-scan the location.

 

Dynamic Substitution

This is only required if core substitution does not achieve the required results.

The third way of using the Stored Procedure validation applies only to non-processed function areas or non-standard standard prompts. With this mechanism, we have the ability to replace the scanned value with another value. A good example of this is substituting the scanned barcode on an item for a Syspro item code. Say for example that with a TransLution EazyScan system which has no TransLution item master, the client needs to scan an EAN13 barcode but then, when making the Business Object calls to Syspro, it needs to replace this scanned barcode with the Syspro Item code. When a SUB is used, then there is no message shown to the operator.

 

In order to do this, the user will scan the barcode as normal, the stored procedure will do a lookup to find the stock code related to the stored barcode and then as long as the return message is prefaced by SUB: then the scanned value will be replaced by what is specified in the stored procedure logic. In order to maintain the audit trail, there is a column in the Scanner Processed Data table called Actual Scanned Value which is where the un-substituted, scanned value is stored. An example of a simple stored procedure is shown below.

 

USE [TL_240]

GO

/****** Object: StoredProcedure [dbo].[sp_TestSUB] Script Date: 04/09/2012 13:20:22 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[sp_TestSUB]

@Scan varchar(50),

@jobID int

AS

Declare @SubValue varchar(50)

SELECT TOP (1) @SubValue = ITEM_CODE

FROM PRODUCT_CONFIG

WHERE (BARCODE = @Scan)

SELECT 'SUB:' + @SubValue as response