SSCC Labels (example)

Introduction

SSCC is short for Serial Shipping Container Code. An SSCC label is internationally recognized as a shipping code for pallets and containers. Each container or pallet has a unique SSCC label with code. This code makes the worldwide tracking & tracing process of pallets and containers possible. This optimizes the logistics process to the maximum. In most cases an SSCC label is combined with an edi message, also called desadv, which can be automatically linked when scanning the sscc code.

The SSCC code is build up as follow:

Prefix 00 (barcode = application identifier).
GS1 company reference (six caracters).
The first six digits that has been created by while sending orders (interpretation )
Two digits for the line number in order to make it unique. (interpretation )

At every print, the line number is incremented for the relevant document. CheckSum (one position).

On the following website you can find further explanation:

https://www.palletlabel.com/sscc-label/

Here is an example of a SSCC palletlabel.

Design

When the process has been set up a document will be printed. In this case we have chosen to work with a print event to complete packaging.

Scan the barcode of the SSCC when the question from the track & trace number pops up.
This will be written in the packaging upon delivery; right click on delivery packing list.

In the following paragraphs the necessary steps for design are explained.

Create a table.
Create a function to calculate.
Create a Stored Procedure that will be used in the Crystal Report.
Create Crystal Report.

The necessary steps for design are presented in HANA and SQL.

Create table

CREATE TABLE

SQL

CREATE TABLE "ACM_SSCC_PER_RDR" ("DOCENTRY" VARCHAR(6) NOT NULL ,
     "LASTNR" INT NOT NULL )

HANA

SQL

CREATE COLUMN TABLE "ACM_SSCC_PER_RDR" ("DOCENTRY" VARCHAR(6) NOT NULL ,
 "LASTNR" INTEGER CS_INT NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE

Create function to calculate Checksum

This function will create a check sum based upon an input parameter SSCC and adds it to a full SSCC number.

SQL

/****** Object:  UserDefinedFunction [dbo].[ACM_FN_GET_SSCC]    Script Date: 21-9-2020 16:34:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ACM_FN_GET_SSCC]
(
    @SSCC VARCHAR(17)
)
RETURNS VARCHAR(18)
AS
BEGIN
    DECLARE    @Index TINYINT,
        @Multiplier TINYINT,
        @Sum SMALLINT

    SELECT    @Index = LEN(@SSCC),
        @Multiplier = 3,
        @Sum = 0

    WHILE @Index > 0
        SELECT    @Sum = @Sum + @Multiplier * CAST(SUBSTRING(@SSCC, @Index, 1) AS TINYINT),
            @Multiplier = 4 - @Multiplier,
            @Index = @Index - 1

    RETURN    CASE @Sum % 10
            WHEN 0 THEN @SSCC + '0'
            ELSE @SSCC + CAST(10 - @Sum % 10 AS CHAR(1))
        END
END

HANA

SQL

CREATE FUNCTION "ACM_FN_GET_SSCC"(SSCC NVARCHAR(30))
RETURNS Result NVARCHAR(100) LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE Index1 TINYINT;
    DECLARE Multiplier TINYINT;
    DECLARE CheckSum SMALLINT;

    SELECT LENGTH(:SSCC), 3, 0 INTO Index1, Multiplier, CheckSum FROM DUMMY;

    WHILE :Index1 > 0
    DO
        SELECT :CheckSum + :Multiplier * CAST(SUBSTRING(:SSCC, :Index1, 1) AS TINYINT),
            4 - :Multiplier INTO CheckSum, Multiplier 
        FROM DUMMY;    

        Index1 := :Index1 - 1;        
    END WHILE;     

    SELECT CASE MOD(:CheckSum, 10) WHEN 0 THEN :SSCC || '0' 
          ELSE :SSCC || CAST((10 - MOD(:CheckSum, 10)) AS CHAR(1)) 
          END INTO Result 
    FROM DUMMY; 
 END;

Create Stored Procedure for use in Crystal Reports

CREATE STORED PROCEDURE FOR USE IN CRYSTAL REPORT

SQL

/****** Object:  StoredProcedure [dbo].[ACM_GET_SSCC_DATA]    Script Date: 21-9-2020 15:07:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ACM_GET_SSCC_DATA] 

@DocEntry INT

as 

BEGIN

DECLARE @DocEntryOUT varchar(18);
DECLARE @RowCount INT;
DECLARE @LastNr INT;
DECLARE @gs1company NVARCHAR(9);
DECLARE @Document NVARCHAR(6);

SET @gs1company =  '123456789' /* Enter GS1 company reference */

-- Prepare DocumentID for SSCC table (Max 6 characters)
SET @Document = (SELECT RIGHT('000000' + CAST(@DocEntry AS VARCHAR(100)),6))

--Check for existing SSCC value for Delivery Document
SET @RowCount = (SELECT COUNT(*) FROM ACM_SSCC_PER_RDR WHERE DOCENTRY = @Document)

IF  @RowCount =  0 

    -- No value for document yet, create first value 0
    -- And set variable to 0
    BEGIN
    INSERT INTO ACM_SSCC_PER_RDR
    VALUES (@Document,0)

    SET @LastNr = 0
    END

ELSE 
    -- Value for document found, read last number from table
    BEGIN
    SET @LastNr = (SELECT ISNULL(LASTNR, 0) FROM ACM_SSCC_PER_RDR WHERE DOCENTRY = @Document)
    END

-- 6 digits for documentID and 2 digits for label nr
SET @DocEntryOUT = (SELECT @gs1company + @Document + RIGHT('00' + CAST(@LastNr +1 AS VARCHAR(2)),2))  

-- Update existing SSCC value for document
UPDATE ACM_SSCC_PER_RDR SET LASTNR = LastNr +1 WHERE DOCENTRY = @Document;

-- Output SSCC value including additional check digit through function ACM_FN_GET_SSCC
select dbo.[ACM_FN_GET_SSCC](@DocEntryOUT) as SSCC

END

HANA

SQL

CREATE PROCEDURE ACM_GET_SSCC_DATA (DocEntry INT)
LANGUAGE SQLSCRIPT AS
BEGIN 

DECLARE DocEntryOUT varchar(18);
DECLARE RowCount INT;
DECLARE LastNr INT;
DECLARE gs1company NVARCHAR(9);
DECLARE Document NVARCHAR(6);

gs1company := '123456789'; /* Enter GS1 company reference */

-- Prepare DocumentID for SSCC table (Max 6 characters)
SELECT RIGHT('000000' || CAST(:DocEntry AS VARCHAR(100)),6) INTO Document FROM DUMMY;

--Check for existing SSCC value for Delivery Document
SELECT COUNT(*) INTO RowCount FROM ACM_SSCC_PER_RDR WHERE DOCENTRY = :Document;

IF :RowCount = 0
THEN 
    -- No value for document yet, create first value 0
    INSERT INTO ACM_SSCC_PER_RDR VALUES(:Document, 0);
    -- And set variable to 0
    LastNr := 0;
ELSE 
    -- Value for document found, read last number from table
    SELECT IFNULL(LASTNR, 0) INTO LastNr FROM ACM_SSCC_PER_RDR WHERE DOCENTRY = :Document;
END IF;

-- 6 digits for documentID and 2 digits for label nr
SELECT :gs1company || :Document || RIGHT('00' || CAST(:LastNr +1 AS VARCHAR(2)),2)  INTO DocEntryOUT FROM DUMMY;

-- Update existing SSCC value for document
UPDATE ACM_SSCC_PER_RDR SET LASTNR = :LastNr +1 WHERE DOCENTRY = :Document;

-- Output SSCC value including additional check digit through function ACM_FN_GET_SSCC
SELECT ACM_FN_GET_SSCC(:DocEntryOUT) AS "SSCC" FROM DUMMY;

END;

Create Crystal Report

In order to test the number that will be created by a certain document (for example the 1234th document) you can use following statement:

SQL

exec ACM_GET_SSCC_DATA 1234

HANA

SQL

call ACM_GET_SSCC_DATA (1234)

Determine SSCC print event. Use following link for an explanation of the print event functionality: Printing