Skip to main content
Skip table of contents

MSSQL Samples

Example MSSQL - Shipment Document

The following query example is based on the default Shipment_Document custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
--DECLARE @UserID INT = 2
--, @Language NVARCHAR(20) = 'EN'
--, @Documents NVARCHAR(255) = '17-1171,13-1'
--, @ShipToCode NVARCHAR(100) = 'Afleveradres'
--, @TrnspCode INT = 4
--, @PaymentTerms INT = 6

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @TempDocTABLE TABLE(BaseType NVARCHAR(10), DocEntry INT)
DECLARE @CardCode NVARCHAR(30), @CardName NVARCHAR(150), @Address NVARCHAR(100), @ShippingType NVARCHAR(100), @PayTerms NVARCHAR(20), @DocNum INT, @ShipDate DATE , @Remarks NVARCHAR(MAX)
DECLARE @CurBaseType NVARCHAR(10), @CurDocEntry INT
DECLARE @ResultVar NVARCHAR(MAX) = ''

/* CONVERT INPUT VAR @Documents INTO USEABLE DATA (INTO TEMPORARY TABLE) */
INSERT INTO @TempDocTABLE
SELECT PARSENAME(REPLACE(value,'-','.'),2) as BaseType,
       PARSENAME(REPLACE(value,'-','.'),1) as DocEntry
FROM STRING_SPLIT(@Documents, ',')

/* SET STARTING LINE FOR RESULTTEXT VARIABLE */
SET @ResultVar = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)

/* EXTENDS DATA FROM SBO INTO TEMPORARY TABLE */
WHILE (SELECT COUNT(*) FROM @TempDocTABLE) > 0
BEGIN
	SELECT TOP(1) @CurBaseType = BaseType , @CurDocEntry = DocEntry FROM @TempDocTABLE ORDER BY BaseType DESC, DocEntry ASC

	/* READ DATA FROM FIRST RECORD IN TEMPORARY TABLE, EXTEND WITH WHP AND SAP DATA AND PUT DATA INTO VARIABLES */
	SELECT @CardCode = R.CardCode, @CardName = R.CardName, @Address = R.ShipToCode, @ShippingType = R.TrnspName, @PayTerms = R.PymntGroup
		, @DocNum = R.DocNum, @ShipDate = R.DocDueDate, @Remarks = R.Comments
	FROM
	(
		SELECT D.CardCode , D.CardName , D.ShipToCode , ISNULL(S.TrnspName, '') AS TrnspName , ISNULL(C.PymntGroup, '') AS PymntGroup , D.DocNum , D.DocDueDate , ISNULL(D.Comments , '') AS Comments
		FROM @TempDocTABLE T
		INNER JOIN ORDR D WITH(NOLOCK) ON D.DocEntry = T.DocEntry AND D.ObjType = T.BaseType
		LEFT JOIN OSHP S WITH(NOLOCK) ON S.TrnspCode = D.TrnspCode
		LEFT JOIN OCTG C WITH(NOLOCK) ON C.GroupNum = D.GroupNum 
		WHERE T.BaseType = @CurBaseType AND T.DocEntry = @CurDocEntry

		UNION

		SELECT D.CardCode , D.CardName , D.ShipToCode , ISNULL(S.TrnspName, '') AS TrnspName , ISNULL(C.PymntGroup, '') AS PymntGroup , D.DocNum , D.DocDueDate , ISNULL(D.Comments , '') AS Comments
		FROM @TempDocTABLE T
		INNER JOIN OINV D WITH(NOLOCK) ON D.DocEntry = T.DocEntry AND D.ObjType = T.BaseType
		LEFT JOIN OSHP S WITH(NOLOCK) ON S.TrnspCode = D.TrnspCode
		LEFT JOIN OCTG C WITH(NOLOCK) ON C.GroupNum = D.GroupNum
		WHERE T.BaseType = @CurBaseType AND T.DocEntry = @CurDocEntry
	)R

	/* ADD EACH VARIABLE IN READEABLE FORMAT INTO RESULT-VARIABLE */
	SET @ResultVar = @ResultVar 
		+ CASE @Language WHEN 'NL' THEN 'Document: ' ELSE 'Document: ' END  + char(13) 
		+ CASE @CurBaseType	WHEN '17' THEN CASE @Language WHEN 'NL' THEN 'Verkooporder' ELSE 'Sales Order' END  
							WHEN '13' THEN CASE @Language WHEN 'NL' THEN 'Reservefactuur' ELSE 'Reserve Invoice' END  
							END
		+ char(13) + CAST(@DocNum AS NVARCHAR(20)) + char(13) + char(13)
		+ CASE @Language WHEN 'NL' THEN 'Klant: ' ELSE 'Customer: ' END  + char(13) + @CardCode + char(13) + @CardName + char(13) + char(13)
		+ CASE @Language WHEN 'NL' THEN 'Adres: ' ELSE 'Address: ' END  + char(13) + @Address + char(13) + char(13)
		+ CASE @Language WHEN 'NL' THEN 'Verzenddatum: ' ELSE 'Shipping Date: ' END  + char(13) + CONVERT(NVARCHAR(20),@ShipDate,105) + char(13) + char(13)
		+ CASE @Language WHEN 'NL' THEN 'Verzendwijze: ' ELSE 'Shipping Type: ' END  + char(13) + @ShippingType + char(13) + char(13)
		+ CASE @Language WHEN 'NL' THEN 'Betalingsconditie: ' ELSE 'Payment Terms: ' END  + char(13) + @PayTerms + char(13) + char(13)

		IF ISNULL(@Remarks, '') <> ''
		BEGIN
			/* ONLY FILL COMMENTS WHEN NOT EMPTY */
			SET @ResultVar = @ResultVar + CASE @Language WHEN 'NL' THEN 'Opmerkingen: ' ELSE 'Remarks: ' END  + char(13) + @Remarks + char(13) 
		END
	
	/* DELETE HANDLED RECORD TO PREVENT ENDLESS LOOP */
	DELETE FROM @TempDocTABLE WHERE BaseType = @CurBaseType AND DocEntry = @CurDocEntry

	IF (SELECT COUNT(*) FROM @TempDocTABLE) > 0
	BEGIN
		/* ONLY ADD CLOSING LINE WHEN NOT LAST RECORD */
		SET @ResultVar = @ResultVar + char(13) + '---------------------------------' + char(13) + char(13)
	END
END

/* READ END RESULT */
SELECT @ResultVar AS Result

The example above returns the following data in WarehousePro Client:

Example MSSQL - Goods Receipt Document

The following query example is based on the default Goods_Receipt_Document custom details screen.

Tab: Script

SQL 
/* USE ONLY FOR TESTING RESULT IN SQL MANAGEMENT STUDIO */
--DECLARE @Documents NVARCHAR(60)  = '22-562,18-123,22-563'
--DECLARE @language NVARCHAR(60) ='NL'
--DECLARE @UserID INT = 1

/* STARTING SCRIPT*/

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @TEMPDOCS TABLE ( OBJTYPE NVARCHAR(20), DOCENTRY INT )
DECLARE @TEMPRESULT TABLE (DocNum NVARCHAR(10), ObjectTypeDoc NVARCHAR(15), CardName NVARCHAR(100),CardCode NVARCHAR(30) ,DocDueDate DATE,Comments NVARCHAR(MAX)) 
DECLARE @DocNum NVARCHAR(10), @ObjectTypeDoc NVARCHAR(15), @CardName NVARCHAR(100), @CardCode NVARCHAR(30) , @DocDueDate NVARCHAR(12), @Comments NVARCHAR(MAX)
DECLARE @RESULTTEXT NVARCHAR(MAX)

/* CONVERT INPUT VAR @Documents INTO USEABLE DATA (INTO TEMPORARY TABLE) */
INSERT INTO @TEMPDOCS
SELECT	PARSENAME(REPLACE(value,'-','.'),2) AS ObjType , PARSENAME(REPLACE(value,'-','.'),1) AS DocEntry
FROM STRING_SPLIT(@Documents, ',') /* STRING SPLIT IS DEFAULT FUNCTION AS OF SQL SERVER 2012 */

/* EXTENDS DATA FROM SBO INTO TEMPORARY TABLE */
INSERT INTO @TEMPRESULT
SELECT CAST (P.DocNum AS NVARCHAR(10)) AS DocNum 
	, CASE P.ObjType	WHEN '18' THEN (CASE @language WHEN 'NL' THEN 'Reservefactuur' ELSE 'Reserve Invoice' END) 
						WHEN '22' THEN (CASE @language WHEN 'NL' THEN 'Bestelling' ELSE 'Sales Order' END) 
		END AS ObjectTypeDoc 
	, P.CardName, P.CardCode, P.DocDueDate, ISNULL (P.Comments, '') AS Comments	
FROM @TEMPDOCS AS A
INNER JOIN OPOR AS P ON P.DocEntry = A.DocEntry AND P.ObjType = A.ObjType
WHERE A.ObjType = '22' /* DOCTYPE PURCHASE ORDER */

UNION 

SELECT CAST (P.DocNum AS NVARCHAR(10)) AS DocNum 
	, CASE P.ObjType	WHEN '18' THEN (CASE @language WHEN 'NL' THEN 'Reservefactuur' ELSE 'Reserve Invoice' END) 
						WHEN '22' THEN (CASE @language WHEN 'NL' THEN 'Bestelling' ELSE 'Sales Order' END) 
		END AS ObjectTypeDoc 
	, P.CardName, P.CardCode, P.DocDueDate, ISNULL (P.Comments, '') AS Comments  
FROM @TEMPDOCS AS A
INNER JOIN OPCH AS P ON P.DocEntry = A.DocEntry AND P.ObjType = A.ObjType
WHERE A.ObjType = '18' /* DOCTYPE RESERVE INVOICE */

/* CLEAR RESULTTEXT VARIABLE */
SET @RESULTTEXT = ''

/* LOOP THROUGH TEMPORARY TABLE (EXTENDED TEMP RESULT) */
WHILE (SELECT COUNT(*) FROM @TEMPRESULT) > 0
BEGIN
	/* READ DATA FROM FIRST RECORD IN TEMPORARY TABLE AND PUT DATA INTO VARIABLES */
	SELECT TOP 1
		@DocNum = DocNum
		, @ObjectTypeDoc = ObjectTypeDoc
		, @CardName = CardName
		, @CardCode = CardCode
		, @DocDueDate = CONVERT(NVARCHAR(12), DocDueDate, 105)
		, @Comments = Comments
	FROM @TEMPRESULT
	ORDER BY DocDueDate

	/* ADD EACH VARIABLE IN READEABLE FORMAT INTO RESULT-VARIABLE */
	SET @RESULTTEXT = @RESULTTEXT
			+ CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
			+ 'Type: ' + @ObjectTypeDoc + CHAR(10)
			+ 'Document: ' + @DocNum + CHAR(10)
			+ CASE @language WHEN 'NL' THEN 'Leverancier: ' ELSE 'Supplier: ' END + @CardName + ' (' + @CardCode  + ')' + CHAR(10)
			+ CASE @language WHEN 'NL' THEN 'Leverdatum: ' ELSE 'Delivery date 'END + @DocDueDate + CHAR(10)
	
	IF @Comments <> '' 
	BEGIN
		/* ONLY FILL COMMENTS WHEN NOT EMPTY */
		SET @RESULTTEXT = @RESULTTEXT 
		+ CASE @language WHEN 'NL' THEN'Commentaar: ' ELSE 'Comments: ' END + CHAR(10)
		+ @Comments + CHAR(10) 
	END

	IF (SELECT COUNT(*) FROM @TEMPRESULT) > 1
	BEGIN
		/* ONLY ADD CLOSING LINE WHEN NOT LAST RECORD */
		SET @RESULTTEXT = @RESULTTEXT + '--------------------------------------' + CHAR(10)
	END
	
	/* DELETE HANDLED RECORD TO PREVENT ENDLESS LOOP */
	DELETE FROM @TEMPRESULT WHERE DocNum = @DocNum  AND ObjectTypeDoc = @ObjectTypeDoc
END

/* READ END RESULT */
SELECT @RESULTTEXT

The example above returns the following data in WarehousePro Client:

Example MSSQL - Orderpicking Line

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.

Tab: Automatic show and Script

Stored Procedure:

TSQL 
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 = 'Item = ' + (SELECT TOP(1) T.ItemCode FROM @Data T) + CHAR(10)
        SET @Result = @Result + 'Description = ' + (SELECT TOP(1) T.ItemName FROM @Data T) + CHAR(10) + CHAR(10)
        SET @Result = @Result + 'Comments' + + 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:

T-SQL 
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:

TSQL 
EXECUTE WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE 'Y', @AbsEntry, @PickEntrys

Script:

T-SQL 
EXECUTE WHP_CUSTOM_DETAILS_ORDER_PICKING_LINE 'N', @AbsEntry, @PickEntrys

Example MSSQL - Order Picking Document

The following query example is based on the default Order Picking_Document custom details screen.

Tab: Script

This example is only applicable in case of one document.

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
DECLARE @UserID INT = 1
DECLARE @Language NVARCHAR(4) = 'NL'
DECLARE @AbsEntry INT = 2167

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @PickDate DATE, @Remarks NVARCHAR(max)
DECLARE @CurBaseType NVARCHAR(100) 
DECLARE @CardName NVARCHAR(100), @CardCode NVARCHAR(30)
DECLARE @ResultVAR NVARCHAR(MAX)

/* RETREIVE TEMPORARY DATA */
SELECT @PickDate = Pickdate, @Remarks = Remarks FROM APD_OPKL WITH(NOLOCK) WHERE AbsEntry = @AbsEntry 
SELECT @CardCode = CardCode, @CardNAme = CardName FROM APD_OPKL_1 WITH(NOLOCK) INNER JOIN ORDR O on OrderEntry = O.DocEntry WHERE AbsEntry = @AbsEntry
SELECT @CurBaseType = BaseObject FROM APD_OPKL_1 WITH(NOLOCK) WHERE AbsEntry = @AbsEntry


/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Picklijst: ' ELSE 'Picklist: ' END  + char(13) + CAST(@AbsEntry AS NVARCHAR(10)) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Soort: ' ELSE 'Type: ' END + char(13) + CASE @CurBaseType WHEN '17' THEN CASE @Language WHEN 'NL' THEN 'Verkooporder' ELSE 'Sales Order' END  
                            WHEN '13' THEN CASE @Language WHEN 'NL' THEN 'Reservefactuur' ELSE 'Reserve Invoice' END  
                            END + char(13) + char(13) 
+ CASE @Language WHEN 'NL' THEN 'Klant: ' ELSE 'Customer: ' END + char(13) + @CardName + ' (' + @CardCode  + ')' + CHAR(10) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Pickdatum: ' ELSE 'Pick Date: ' END + char(13) + CONVERT(NVARCHAR(20) , @PickDate , 105) + char(13) + char(13) 

/* READ END RESULT */
SELECT @ResultVAR AS Details

The example above returns the following data in WarehousePro Client:

Example MSSQL - Stock Transfer Document

The following query example is based on the default Stock_Transfer_Document custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
--DECLARE @AbsEntry INT = 25694
--DECLARE @Language NVARCHAR(4) = 'NL'
--DECLARE @UserID INT = 1

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @PickDate DATE , @FromWHSCode NVARCHAR(25), @ToWHSCode NVARCHAR(25), @FromWHSName NVARCHAR(150), @ToWHSName NVARCHAR(150), @Remarks NVARCHAR(max)
DECLARE @ResultVAR NVARCHAR(MAX)

/* RETREIVE TEMPORARY DATA */
SELECT @PickDate = PickDate, @Remarks = Remarks FROM APD_OPKL WITH(NOLOCK) WHERE AbsEntry = @AbsEntry
SELECT @FromWHSCode = D.Filler, @ToWHSCode = D.ToWhsCode FROM OWTQ D WITH(NOLOCK) WHERE D.DocEntry = (SELECT OrderEntry FROM APD_OPKL_1 WITH(NOLOCK) WHERE AbsEntry = @AbsEntry AND BaseObject = '1250000001')
SELECT @FromWHSName = W.WhsName FROM OWHS W WITH(NOLOCK) WHERE W.WhsCode = @FromWHSCode
SELECT @ToWHSName = W.WhsName FROM OWHS W WITH(NOLOCK) WHERE W.WhsCode = @ToWHSCode

/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Document: ' ELSE 'Document: ' END  + char(13) + CAST(@AbsEntry AS NVARCHAR(10)) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Pickdatum: ' ELSE 'Pick Date: ' END + char(13) + CONVERT(NVARCHAR(20) , @PickDate , 105) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Vanaf magazijn: ' ELSE 'From Warehouse: ' END + char(13) + @FromWHSName + ' (' + @FromWHSCode + ')' + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Naar magazijn: ' ELSE 'To Warehouse: ' END + char(13) + @ToWHSName + ' (' + @ToWHSCode + ')' + char(13) + char(13)

/* ONLY ADD REMARKS WHEN AVAILABLE */
IF ISNULL(@Remarks, '') <> ''
BEGIN
    SET @ResultVAR = @ResultVAR + char(13)  + CASE @Language WHEN 'NL' THEN 'Opmerkingen: ' ELSE 'Remarks: ' END + char(13) + ISNULL(@Remarks, '') + char(13) + char(13)
END

/* READ END RESULT */
SELECT @ResultVAR AS Details

The example above returns the following data in WarehousePro Client:

Example MSSQL - Stock Transfer Line Get

The following query example is based on the default Stock_Transfer_Line_Get custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
--DECLARE @UserID INT = 2
--DECLARE @Language NVARCHAR(4) = 'NL'
--DECLARE @AbsEntry INT = 25694
--DECLARE @PickEntrys NVARCHAR(255) = '0,1'

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @TEMPENTRYS TABLE (PickEntry INT)
DECLARE @ItemCode NVARCHAR(50), @ItemName NVARCHAR(150), @Quantity NUMERIC(19,6) , @UoM NVARCHAR(50)
DECLARE @ResultVAR NVARCHAR(MAX)

/* CONVERT INPUT VAR @PickEntrys INTO USEABLE DATA (INTO TEMPORARY TABLE) */
INSERT INTO @TEMPENTRYS
SELECT value AS PickEntry
FROM STRING_SPLIT(@PickEntrys, ',') /* STRING SPLIT IS DEFAULT FUNCTION AS OF SQL SERVER 2012 */


/* RETREIVE TEMPORARY DATA */
SELECT @ItemCode = I.ItemCode , @ItemName = I.ItemName , @Quantity = SUM(PL.PrevReleas) , @UoM = I.InvntryUom
FROM APD_OPKL_1 PL WITH(NOLOCK) 
INNER JOIN APD_OPKL P WITH(NOLOCK) ON P.AbsEntry = PL.AbsEntry
INNER JOIN WTQ1 TQ WITH(NOLOCK) ON TQ.DocEntry = PL.OrderEntry AND TQ.LineNum = PL.OrderLine
INNER JOIN OITM I WITH(NOLOCK) ON I.ItemCode = TQ.ItemCode
WHERE PL.AbsEntry = @AbsEntry AND PL.PickEntry IN (SELECT PickEntry FROM @TEMPENTRYS)
GROUP BY I.ItemCode, I.ItemName, I.InvntryUom


/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Document: ' ELSE 'Document: ' END  + char(13) + CAST(@AbsEntry AS NVARCHAR(10)) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Artikel: ' ELSE 'Item: ' END + char(13) + CONVERT(NVARCHAR(20) , @ItemCode , 105) + char(13) + @ItemName + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Aantal: ' ELSE 'Quantity: ' END + char(13) + CAST(CAST(@Quantity AS Float) AS NVARCHAR(10)) + ' ' + @UoM + char(13) + char(13)

/* READ END RESULT */
SELECT @ResultVAR

The example above returns the following data in WarehousePro Client:

Example MSSQL - Stock Transfer Line Put

The following query example is based on the default Stock_Transfer_Line_Put custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
--DECLARE @UserID INT = 2
--DECLARE @Language NVARCHAR(4) = 'NL'
--DECLARE @AbsEntry INT = 25694
--DECLARE @PickEntrys NVARCHAR(255) = '0,1'

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @TEMPENTRYS TABLE (PickEntry INT)
DECLARE @ItemCode NVARCHAR(50), @ItemName NVARCHAR(150), @Quantity NUMERIC(19,6) , @UoM NVARCHAR(50)
DECLARE @ResultVAR NVARCHAR(MAX)

/* CONVERT INPUT VAR @PickEntrys INTO USEABLE DATA (INTO TEMPORARY TABLE) */
INSERT INTO @TEMPENTRYS
SELECT value AS PickEntry
FROM STRING_SPLIT(@PickEntrys, ',') /* STRING SPLIT IS DEFAULT FUNCTION AS OF SQL SERVER 2012 */


/* RETREIVE TEMPORARY DATA */
SELECT @ItemCode = I.ItemCode , @ItemName = I.ItemName , @Quantity = SUM(PL.PrevReleas) , @UoM = I.InvntryUom
FROM APD_OPKL_1 PL WITH(NOLOCK) 
INNER JOIN APD_OPKL P WITH(NOLOCK) ON P.AbsEntry = PL.AbsEntry
INNER JOIN WTQ1 TQ WITH(NOLOCK) ON TQ.DocEntry = PL.OrderEntry AND TQ.LineNum = PL.OrderLine
INNER JOIN OITM I WITH(NOLOCK) ON I.ItemCode = TQ.ItemCode
WHERE PL.AbsEntry = @AbsEntry AND PL.PickEntry IN (SELECT PickEntry FROM @TEMPENTRYS)
GROUP BY I.ItemCode, I.ItemName, I.InvntryUom


/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Document: ' ELSE 'Document: ' END  + char(13) + CAST(@AbsEntry AS NVARCHAR(10)) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Artikel: ' ELSE 'Item: ' END + char(13) + CONVERT(NVARCHAR(20) , @ItemCode , 105) + char(13) + @ItemName + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Aantal: ' ELSE 'Quantity: ' END + char(13) + CAST(CAST(@Quantity AS Float) AS NVARCHAR(10)) + ' ' + @UoM + char(13) + char(13)

/* READ END RESULT */
SELECT @ResultVAR

The example above returns the following data in WarehousePro Client:

Example MSSQL - Inventory Counting Document

The following query example is based on the default Inventory_Counting_Document custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
DECLARE @UserID INT = 1
DECLARE @Language NVARCHAR(4) = 'NL'
DECLARE @Document_ID INT = 15
DECLARE @List_ID INT = 1

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @Document INT, @List INT, @Date NVARCHAR(12), @Remarks NVARCHAR(max)
DECLARE @ResultVAR NVARCHAR(MAX)

/* RETREIVE TEMPORARY DATA */
SELECT @Document = Document_ID, @List = List_ID, @Date = CONVERT (NVARCHAR(12), CreateDate, 105) 
FROM APD_DOCUMENT_INVENTORY_COUNTING_LIST 
WHERE Document_ID = @Document_ID AND List_ID = @List_ID

SELECT @Remarks = Remarks 
FROM APD_DOCUMENT_INVENTORY_COUNTING_LIST 
WHERE Document_ID = @Document_ID AND List_ID = @List_ID

/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ 'Document: ' + CAST(@Document AS NVARCHAR(12)) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Tellijst: ' ELSE 'Counting list: ' END + CAST(@List AS NVARCHAR(12)) + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Datum: ' ELSE 'Date: ' END + char(13) + @Date + char(13)

/* ONLY ADD REMARKS WHEN AVAILABLE */
IF ISNULL(@Remarks, '') <> ''
BEGIN
    SET @ResultVAR = @ResultVAR + char(13)  + CASE @Language WHEN 'NL' THEN 'Opmerkingen: ' ELSE 'Remarks: ' END + char(13) + ISNULL(@Remarks, '') + char(13) + char(13)
END

/* READ END RESULT */
SELECT @ResultVAR AS Details

The example above returns the following data in WarehousePro Client:

Example MSSQL - Put Away Document

The following query example is based on the default PutAway_Document custom details screen.

Tab: Script

T-SQL 
/* USE ONLY FOR TESTING RESULT IN MANAGEMENT STUDIO */
DECLARE @UserID INT = 1
DECLARE @Language NVARCHAR(4) = 'NL'
DECLARE @AbsEntry INT = 2167

/* STARTING SCRIPT */

/* PREPARATIONS FOR TEMPORARY DATA */
DECLARE @Warehouse NVARCHAR(10), @BinCode NVARCHAR(20), @Descr NVARCHAR(max)
DECLARE @ResultVAR NVARCHAR(MAX)

/* RETREIVE TEMPORARY DATA */
SELECT @Warehouse = WhsCode, @BinCode = BinCode FROM OBIN WITH(NOLOCK) WHERE AbsEntry = @AbsEntry
SELECT @Descr = Descr FROM OBIN WITH(NOLOCK) WHERE AbsEntry = @AbsEntry


/* ADD EACH VALUE INTO READABLE FORMAT INTO RESULT-VARIABLE */
SET @ResultVAR = CASE @Language WHEN 'NL' THEN 'VOORBEELD QUERY: ' ELSE 'SAMPLE QUERY: ' END + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Magazijn: ' ELSE 'Warehouse: ' END + char(13) + @warehouse + char(13) + char(13)
+ CASE @Language WHEN 'NL' THEN 'Locatie: ' ELSE 'Location: ' END + char(13) + @BinCode + char(13)

/* ONLY ADD DESCRIPTION WHEN AVAILABLE */
IF ISNULL(@Descr, '') <> ''
BEGIN
    SET @ResultVAR = @ResultVAR + char(13) + CASE @Language WHEN 'NL' THEN 'Omschrijving: ' ELSE 'Description: ' END + char(13) + ISNULL(@Descr, '') + char(13) + char(13)
END

/* READ END RESULT */
SELECT @ResultVAR AS Details

The example above returns the following data in WarehousePro Client:

Example MSSQL - Goods Receipt Line

The following query examples are based on a indicator on Goods Receipt - Lines custom details screen.

Tab: Display Indicators

Example #1 - Simple example

SQL 
SELECT
    @DocEntry AS [Document]
    , POR1.LineNum AS [Line]
    , CASE
        WHEN POR1.ItemCode LIKE('A%') THEN 'Blue'
        WHEN POR1.ItemCode LIKE('B%') THEN 'Gray'
        WHEN POR1.ItemCode LIKE('C%') THEN 'Green'
        WHEN POR1.ItemCode LIKE('D%') THEN 'Red'
        WHEN POR1.ItemCode LIKE('I%') THEN 'Yellow'
        ELSE 'None'
    END AS [Icon] --Icon is used by deprecated WarehousePro Client
    , 'sap-icon://circle-task-2' AS [SAPUI5_Icon]
    , CASE
        WHEN POR1.ItemCode LIKE('A%') THEN '#0000ff'
        WHEN POR1.ItemCode LIKE('B%') THEN '#808080'
        WHEN POR1.ItemCode LIKE('C%') THEN '#1fa800'
        WHEN POR1.ItemCode LIKE('D%') THEN '#ff0f0f'
        WHEN POR1.ItemCode LIKE('I%') THEN '#f0ec00'
        ELSE '#ffffff' 
    END AS [SAPUI5_Icon_Color]
    
FROM OPOR
INNER JOIN POR1 ON OPOR.DocEntry = POR1.DocEntry

WHERE
    OPOR.DocEntry = @DocEntry

The example above displays the following indicators in WarehousePro Client:

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close