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