Custom Detail screens
Introduction
WarehousePro includes (as of version 3.1.3.0) the possibility to present additional information about documents or rules via custom / freely configurable detail screens.
For this, a "query" can be written per task type to retrieve the desired data. Each task type has a specific range of "input parameters" with which this query can be executed.
This document explains how this functionality works and some examples will be given that can be used on SQL and / or HANA environments. This document does not contain a detailed explanation of the possibilities on these database platforms.
Settings
The settings for determining the data can be found via the menu path Administration > Setup > WarehousePro Settings > Configuration > Details Definitions.
If no definition is defined in this screen for a specific task, an internal standard is used in which fixed fields are used to display fixed information.
Even when an error is recognized in the query during its execution, WarehousePro will automatically fall back on the fixed information.
Detail Definitions
When a Detail screen needs to be adjusted, the relevant function can be added via the New button at the bottom of the screen.
A new "moment" appears where three tabs can be filled: "Display Indicators", "Show automatically" and "Script". A SQL script is expected in all tabs. The SQL syntax must correspond to the database platform on which the SAP Business One administration is located.
In all cases, input parameters are available to base a query. They do not all have to be used. The parameter (place holder) is replaced when the query will be executed. For each parameter, additional information is shown about how it will be filled. Hereby a short explanation about the parameters from the screen print above.
Display Indicator parameters
@BaseType
Based on the Document types 22 = OPOR (Purchase Order table) and 18 = OPCH (A/P Invoice table).
@DocEntry
Based upon certain DocEntries for Purchase Order and A/P invoice.
SQL Result
For Document type events it is required to return "Icon", 'SAPUI5_Icon" and "SAPUI5_Icon_Color" fields.
For Line type events it is required to return “Document“, “Line“, "Icon", 'SAPUI5_Icon" and "SAPUI5_Icon_Color" fields.
The “Icon“ field is going to be used for the Deprecated WarehousePro Client.
The “SAPUI5_Icon" and "SAPUI5_Icon_Color" fields are going to be used by the current WarehousePro Web Client.
Icon button
When this button is pressed, the WHP, SAP and color of the Icon can be configurated for use in the query. A short example is imported which includes data to be used in a Custom SQL Query command.
Automatically show and Script parameters
@UserID
Based on the additional explanation "APD_TERMINAL_USERS.User_ID" we can conclude that the parameter 1 contains a value and that more information can be found and retrieved in the table "APD_TERMINAL_USERS".
@Language
This parameter returns the language code of the current session. This could be used to display information in the relevant language to the user.
@Documents
This parameter has a somewhat more complex structure. The (,) indication in the explanation indicates that multiple values can be available in the parameter. This can occur, for example, when multiple purchase orders are being received together. The data in the parameter is separated by a comma. In addition, the value consists of the data BaseType and DocEntry (this time separated by the “-” sign. An example: 22-20012,22-20089. In order to be able to use the data from both documents, this parameter must first be parsed in the custom query before this is possible. Sample queries are available in this document.
Automatically show
With this option it is possible to configure that (Custom Details) information is "Automatically shown" when opening a task.
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).
Script
Here it is possible to write a query to create a Custom Detailed information screen for specific tasks.
It is important to bear in mind that the query in the "Script" tab may return a maximum of one value / one single row. 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.
Examples
In these examples, the detail screen is automatically displayed when the item number is “A00001”.
The detail screen itself is filled with the information that is built up in variable "Result". The screen displays item code, description and the "free text" field of all relevant document lines.
MSSQL String Split for older SQL Server versions
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
GOSTRING_SPLIT (SAP HANA)
When a variable (such as "@PickEntrys" ) contains multiple data, a string-split function is required.
Here's how to create this function for SAP HANA environments.
CREATE FUNCTION "SCHEMA"."STRING_SPLIT" ( INPUT_STRING VARCHAR(5000), DELIMITER NVARCHAR(1) )
RETURNS TABLE
(
"OUTPUT_SPLIT" VARCHAR(5000)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
DECLARE FIRST_PART NVARCHAR(5000) := '';
SELECT SUBSTR_BEFORE(:INPUT_STRING,:DELIMITER) INTO FIRST_PART FROM DUMMY;
SPLIT_VALUES = SELECT CASE WHEN :FIRST_PART = '' THEN :INPUT_STRING ELSE :FIRST_PART END AS SINGLE_VAL FROM DUMMY;
IF :FIRST_PART != '' THEN
SELECT SUBSTR_AFTER(:INPUT_STRING,:DELIMITER) || :DELIMITER INTO INPUT_STRING FROM DUMMY;
WHILE( LENGTH(:INPUT_STRING) > 0 )
DO
SPLIT_VALUES = SELECT SINGLE_VAL FROM :SPLIT_VALUES
UNION
SELECT SUBSTR_BEFORE(:INPUT_STRING,:DELIMITER) SINGLE_VAL FROM DUMMY;
SELECT SUBSTR_AFTER(:INPUT_STRING,:DELIMITER) INTO INPUT_STRING FROM DUMMY;
END WHILE;
END IF;
RETURN
SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES;
ENDMultisplit (MSSQL and SAP HANA)
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 @DocumentsSAP 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;