WarehousePro Documentation

It is important to bear in mind that the query in the "Auto Show" tab may only return a single value "Y" (for yes, automatically show) or "N" (for no, do not automatically show).

It is important to bear in mind that the query in the "Script" tab may return a maximum of one value. If multiple data is to be displayed, this will have to be collected in one text field. So first convert all data to text before merging it with other data. A few examples are given below.

This example uses 1 stored procedure "WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE". Here both the automatic display and the script are determined. Via the input parameter "@Trigger" it is indicated which query should be executed.

Stored Procedure:

CREATE PROCEDURE WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE (@Trigger NVARCHAR(1), @AbsEntry INT, @PickEntrys NVARCHAR(2000))
AS
BEGIN
        DECLARE @PickEntryTable TABLE(PickEntry INT)

        INSERT INTO @PickEntryTable
        SELECT CAST(value AS INT) FROM STRING_SPLIT(@PickEntrys, ',')

        IF @Trigger = 'Y'
        BEGIN
                    --Trigger

                    SELECT CASE WHEN A.ItemCode = 'A00001' THEN 'Y' ELSE 'N' END FROM
                    (
                           SELECT DISTINCT L.ItemCode
                           FROM APD_OPKL_1 T WITH(NOLOCK)
                           INNER JOIN (
                                    SELECT 17 AS BaseType, R.DocEntry, R.LineNum, R.ItemCode FROM RDR1 R WITH(NOLOCK)
                                    UNION
                                    SELECT 13 AS BaseType, I.DocEntry, I.LineNum, I.ItemCode FROM INV1 I WITH(NOLOCK)
                           ) L ON T.BaseObject = L.BaseType AND T.OrderEntry = L.DocEntry AND T.OrderLine = L.LineNum
                           WHERE T.AbsEntry = @AbsEntry AND T.PickEntry IN (SELECT * FROM @PickEntryTable)
                    ) A
        END
        ELSE
        BEGIN
                    --Script

                    DECLARE @Data TABLE(ItemCode NVARCHAR(100), ItemName NVARCHAR(100), Comment NVARCHAR(MAX))

                    INSERT INTO @Data
                    SELECT L.ItemCode, L.Dscription, L.Comment
                    FROM APD_OPKL_1 T WITH(NOLOCK)
                    INNER JOIN (

                        SELECT 17 AS BaseType, R.DocEntry, R.LineNum, R.ItemCode, R.Dscription, ISNULL(R.FreeTxt,'') AS Comment FROM RDR1 R 
                        WITH(NOLOCK)

                        UNION

                        SELECT 13 AS BaseType, I.DocEntry, I.LineNum, I.ItemCode, I.Dscription, ISNULL(I.FreeTxt,'') AS Comment FROM INV1 I 
                        WITH(NOLOCK)
                    ) L ON T.BaseObject = L.BaseType AND T.OrderEntry = L.DocEntry AND T.OrderLine = L.LineNum
                    WHERE T.AbsEntry = @AbsEntry AND T.PickEntry IN (SELECT * FROM @PickEntryTable)

        DECLARE @CommentData TABLE(ID INT IDENTITY, Comment NVARCHAR(MAX))
        DECLARE @CommentID INT = 1

        INSERT INTO @CommentData (Comment)
        SELECT T.Comment FROM @Data T WHERE T.Comment != ''

        DECLARE @Result NVARCHAR(MAX)

        SET @Result = 'Artikel = ' + (SELECT TOP(1) T.ItemCode FROM @Data T) + CHAR(10)
        SET @Result = @Result + 'Omschrijving = ' + (SELECT TOP(1) T.ItemName FROM @Data T) + CHAR(10) + CHAR(10)
        SET @Result = @Result + 'Commentaar' + + CHAR(10) + CHAR(10)

        WHILE @CommentID <= (SELECT MAX(D.ID) FROM @CommentData D)
        BEGIN
                    SET @Result = @Result + (SELECT T.Comment FROM @CommentData T WHERE T.ID = @CommentID) + CHAR(10)
                    SET @CommentID = @CommentID + 1;
        END;

        SELECT @Result

    END

END

In this example, the input parameter "@PickEntrys" is first loaded into a temporary table. This is done via the following code:

INSERT INTO @PickEntryTable
SELECT CAST(value AS INT) FROM STRING_SPLIT(@PickEntrys, ',')

By storing the following queries in the Detail Definitions (after saving this stored procedure in the SAP database), this logic can be applied:

Automatically show:

EXECUTE WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE 'Y', @AbsEntry, @PickEntrys

Script:

EXECUTE WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE 'N', @AbsEntry, @PickEntrys

Some MS SQL Server versions do not hot have a built in String_Split function. In this case you could use the following function which will add the same function to these SQL

Server versions:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION STRING_SPLIT
(
    @INPUT_STRING NVARCHAR(MAX),    
    @DELIMITER NVARCHAR(1)
)

RETURNS @SPLIT_VALUES TABLE(VALUE NVARCHAR(1000))

AS
BEGIN

    DECLARE @SPLIT_PART NVARCHAR(MAX) = '';
    DECLARE @POS INT

    WHILE CHARINDEX(',', @INPUT_STRING) > 0

    BEGIN

        SELECT @POS = CHARINDEX(',', @INPUT_STRING)
        SELECT @SPLIT_PART = SUBSTRING(@INPUT_STRING, 1, @POS-1)

        INSERT INTO @SPLIT_VALUES

        SELECT @SPLIT_PART
        SELECT @INPUT_STRING = SUBSTRING(@INPUT_STRING, @POS+1, LEN(@INPUT_STRING)-@POS)

    END

        IF @INPUT_STRING != ''
    
        BEGIN

            INSERT INTO @SPLIT_VALUES
            SELECT @INPUT_STRING

        END

    RETURN

END

GO

This example uses a stored procedure and a function to parse the input parameter of different documents.

CREATE PROCEDURE "SCHEMA"."WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE" (IN Trigger NVARCHAR(1), IN AbsEntry INT, IN PickEntrys NVARCHAR(2000)
LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE Result NVARCHAR(6000) := '';
    DECLARE CommentID INT := 1;
    DECLARE MaxCommentID INT := 0;
    DECLARE ItemCode NVARCHAR(150) := '';
    DECLARE ItemName NVARCHAR(150) := '';
    DECLARE Remarks NVARCHAR(400) := '';

    CREATE LOCAL TEMPORARY TABLE #PickEntryTable ("PickEntry" INT);
    CREATE LOCAL TEMPORARY TABLE #Data ("ItemCode" NVARCHAR(100), "ItemName" NVARCHAR(100), "Comment" NVARCHAR(4000));
    CREATE LOCAL TEMPORARY TABLE #CommentData ("ID" INT, "Comment" NVARCHAR(4000));
    
    INSERT INTO #PickEntryTable
    SELECT CAST("OUTPUT_SPLIT" AS INT) FROM "SCHEMA"."STRING_SPLIT"(:PickEntrys, ',');

    IF :Trigger = 'Y' THEN
            --Trigger
    
            SELECT CASE WHEN A."ItemCode" = 'A00001' THEN 'Y' ELSE 'N' END AS Result FROM
            (
                    SELECT DISTINCT L."ItemCode"
                    FROM "SCHEMA"."APD_OPKL_1" T
                    INNER JOIN (
                        SELECT 17 AS "BaseType", R."DocEntry", R."LineNum", R."ItemCode" FROM "SCHEMA"."RDR1" R
                        UNION
                        SELECT 13 AS "BaseType", I."DocEntry", I."LineNum", I."ItemCode" FROM "SCHEMA"."INV1" I
                    ) L ON T."BaseObject" = L."BaseType" AND T."OrderEntry" = L."DocEntry" AND T."OrderLine" = L."LineNum"
                    WHERE T."AbsEntry" = :AbsEntry AND T."PickEntry" IN (SELECT * FROM #PickEntryTable)
            ) A;
    ELSE
            --Script

            INSERT INTO #Data
            SELECT L."ItemCode", L."Dscription", L."Comment"
            FROM "SCHEMA"."APD_OPKL_1" T
            INNER JOIN (
                    SELECT 17 AS "BaseType", R."DocEntry", R."LineNum", R."ItemCode", R."Dscription", IFNULL(R."FreeTxt",'') AS "Comment" FROM "SCHEMA"."RDR1" R
                    UNION
                    SELECT 13 AS "BaseType", I."DocEntry", I."LineNum", I."ItemCode", I."Dscription", IFNULL(I."FreeTxt",'') AS "Comment" FROM "SCHEMA"."INV1" I
            ) L ON T."BaseObject" = L."BaseType" AND T."OrderEntry" = L."DocEntry" AND T."OrderLine" = L."LineNum"
            WHERE T."AbsEntry" = :AbsEntry AND T."PickEntry" IN (SELECT * FROM #PickEntryTable);

            INSERT INTO #CommentData
            SELECT ROW_NUMBER() OVER (ORDER BY T."Comment"), T."Comment" FROM #Data T WHERE T."Comment" != '';
            
            SELECT MAX(D."ID") INTO MaxCommentID FROM #CommentData D;
            SELECT TOP 1 T."ItemCode" INTO ItemCode FROM #Data T;
            SELECT TOP 1 T."ItemName" INTO ItemName FROM #Data T;

            Result := 'Artikel = ' || :ItemCode || CHAR(10);
            Result := :Result || 'Omschrijving = ' || :ItemName || CHAR(10) || CHAR(10);
            Result := :Result || 'Commentaar' || CHAR(10) || CHAR(10);

            WHILE :CommentID <= :MaxCommentID
            DO
                        SELECT T."Comment" INTO Remarks FROM #CommentData T WHERE T."ID" = :CommentID;
                        
                        Result := :Result || :Remarks || CHAR(10);
                        CommentID := :CommentID + 1;
            END WHILE;
            
            SELECT :Result AS Result FROM DUMMY;

    END IF;

    DROP TABLE #CommentData;
    DROP TABLE #Data;
    DROP TABLE #PickEntryTable;

END;

When a variable (such as "@Documents" as an example in this document) contains multiple data, a multi-split function is required.

Here are examples for MSSQL and SAP HANA.

MSSQL:

DECLARE @CombiData NVARCHAR(MAX) = '17-1-1,17-1-2,13-3-4,13-4-1'

DECLARE @Documents TABLE(BaseType INT, DocEntry INT, LineNum INT)

INSERT INTO @Documents
SELECT PARSENAME(REPLACE(value,'-','.'),3) as BaseType,
       PARSENAME(REPLACE(value,'-','.'),2) as DocEntry,
       PARSENAME(REPLACE(value,'-','.'),1) as LineNum
FROM STRING_SPLIT(@CombiData, ',')

SELECT * FROM @Documents

SAP HANA Multisplit

CREATE PROCEDURE WHP_EXAMPLE_MULTI_SPLIT (IN DocumentLines NVARCHAR(2000))
LANGUAGE SQLSCRIPT AS
BEGIN
    
        CREATE LOCAL TEMPORARY TABLE #EntryTable ("BaseType" INT, "DocEntry" INT, "LineNum" INT);

        INSERT INTO #EntryTable
        SELECT CAST(SUBSTRING_REGEXPR('[^-]+' IN "OUTPUT_SPLIT" FROM 1 OCCURRENCE 1) AS INT) AS "BaseType",
               CAST(SUBSTRING_REGEXPR('[^-]+' IN "OUTPUT_SPLIT" FROM 1 OCCURRENCE 2) AS INT) AS "DocEntry",
               CAST(SUBSTRING_REGEXPR('[^-]+' IN "OUTPUT_SPLIT" FROM 1 OCCURRENCE 3) AS INT) AS "LineNum"
        FROM STRING_SPLIT(:DocumentLines, ',');

        SELECT * FROM #EntryTable;

        DROP TABLE #EntryTable;
END;