Method of working out check digit for an SSCC shipping code in SQL on a system with Sage Line 500 and Datalinx WHM (Although its easy enough to change, the first “set @SSCC” basically gets the digits, the rest of the code simply calculates the check digit).

This proc uses the Datalinx WHM Pallet ID and Sage stock warehouse code as Sage Stock lot numbers may contain text and slashes.

If called from a Datalinx WHM telnet/SSH script all print lines must be commented out.

GS1 codes can be shorter or longer, remember only 16 digits can be used (The GS1 code goes in from the second digit and digit 18 is always a check code!)

After needing two different versions (One required spaces in between different blocks the other didn’t) I decided to be lazy and specify the field locations. This version expects no spaces, if you need spaces simply change the Substrings and increase the variable SSCC from varchar(18).

Another thing to mind is I don’t need to change the first digit (GS1 states the same SSCC code can not be used within a year).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: Generate an SSCC code with check digit -- The first digit is always zero (Unless we re-use lot numbers within a year) -- Digits 2 to 7 are our GS1 number of 1234567 -- Digits 9 to 17 are out lot number (With +0 taken off the front) -- Digit 18 is a check digit. Use GS1 Check Digit Rules to Calculate -- ============================================= -- GS1 Check Digit Rules -- 1 Starting with the right hand digit of the number, add all the alternate digit values -- 2 Multiply the result of step 1 by 3 -- 3 Add up all the other remaining digits -- 4 Add the result of step 2 to the result of step 3 -- 5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10 -- Check output with http://www.gs1.org/check-digit-calculator to ensure accurate -- ============================================= -- Data sources used: -- scheme.stquem -- ============================================= -- Example execution -- exec [dbo].[usp_generatecheckdigitfor_SSCC] @palletno = '+001234567', @wh = 'WH' -- ============================================= CREATE PROCEDURE [dbo].[usp_generatecheckdigitfor_SSCC] -- Add the parameters for the stored procedure here @palletno varchar(10) = '+000000000', @wh varchar(2) = 'WH' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @SSCC varchar(18) --The SSCC Code DECLARE @SSCCcd_working1 int --For Step 1, 2 and 4 and for final calc DECLARE @SSCCcd_working2 int --For Step 3 and 4 DECLARE @SSCCcd_r int --Rounded results calc DECLARE @SSCCcd_dif int --Used to calc check digit --Input is 17 numbers for SSCC number. A check digit must be calculated at the end --Our GS1 Company code is 7 digit long and is "1234567". An extra zero is added at the end as we can't send the plus symbol of out pallet id set @SSCC = (select '01234567' + SUBSTRING(users_text_field,2,8) as SSCC from scheme.stquem(nolock) where users_text_field = @palletno and warehouse = @wh) print @SSCC --Work out Check Digit from @SSCC print '1 Starting with the right hand digit of the number, add all the alternate digit values' set @SSCCcd_working1 = SUBSTRING(@SSCC,17,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,15,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,13,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,11,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,9,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,7,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,5,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,3,1) print @SSCCcd_working1 set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,1,1) print @SSCCcd_working1 print '2 Multiply the result of step 1 by 3' set @SSCCcd_working1 = @SSCCcd_working1*3 print @SSCCcd_working1 print '3 Add up all the other remaining digits' set @SSCCcd_working2 = SUBSTRING(@SSCC,16,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,14,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,12,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,10,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,8,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,6,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,4,1) print @SSCCcd_working2 set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,2,1) print @SSCCcd_working2 print '4 Add the result of step 2 to the result of step 3' set @SSCCcd_working1 = @SSCCcd_working1 + @SSCCcd_working2 print @SSCCcd_working1 print '5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10' set @SSCCcd_r = ROUND(@SSCCcd_working1,-1) print @SSCCcd_r -- Avoid issues from rounding down, add 10 to ensure @SSCCcd_r is always greater or equals to @SSCCcd_working1 If @SSCCcd_r < @SSCCcd_working1 set @SSCCcd_r = @SSCCcd_r+10 print @SSCCcd_r --Work out difference between rounding and check digit set @SSCCcd_dif = @SSCCcd_r - @SSCCcd_working1 print @SSCCcd_dif print @SSCC set @SSCC = @SSCC + CAST(@SSCCcd_dif as varchar) --Don't treat as number! print @SSCC select @SSCC as SSCC END |

*SQL Server 2008*