How to filter GRPO draft documents by reference number in the GRPO process

This note describes how to filter the appearing documents in the GRPO process.

It is possible to generate GRPO from a draft by customization. You can use the Post button to turn it to a GRPO document when the document is draft type (DR).

There is a special query that you have to use in order to load the draft documents as well: bx_mobile_wh9_goodsreceiptpo_query_custom

There are pre defined parameters for this special query that you can use in your sql logic. These are the default filter fields and the employee number. It is not possible to add another parameter, so if we want to pass additional information to our logic, (reference number of the draft document) then we have to create an additional logic. We are going to use the Remark field from the GRPO draft document to store the reference number, because that field automatically appears in the document list in Produmex Scan.

Solution:

We need a UDT in which we can store the employee ID and the data that we will type into the filter field.

A custom field must be defined in the Produmex Scan on this screen.



The old data must be deleted from the UDT based on the employee number, before we are entering a new value. It can be done when we activate the screen. You can use the following query for that:

query name: BXMobileWH9_GoodsReceiptPOScreen_Activate

DELETE FROM "@BXPGRPODRAFT" WHERE "U_EmpID" = $[Employee.EmployeeID]

The next step is to insert the draft number into the UDT. We can use the validate event of the custom field for that. The name of the custom field appears in the name of the query, so you have to replace it with the name of your custom field.

query name:BXMobileWH9_GoodsReceiptPOScreen_Draft_validate
hana version

DO
BEGIN
DELETE FROM "@BXPGRPODRAFT" WHERE "U_EmpID" = $[Employee.EmployeeID];
INSERT INTO "@BXPGRPODRAFT" SELECT (SELECT ISNULL(MAX(CAST("Code" as int)), 0) + 1 FROM "@BXPGRPODRAFT"), (SELECT ISNULL(MAX(CAST("Code" as int)), 0) + 1 FROM "@BXPGRPODRAFT"), $[Employee.EmployeeID], $[Draft] FROM DUMMY;
END

MSSQL version

DELETE FROM "@BXPGRPODRAFT" WHERE "U_EmpID" = $[Employee.EmployeeID];
INSERT INTO "@BXPGRPODRAFT" 
SELECT 
(SELECT ISNULL(MAX(CAST("Code" as int)), 0) + 1 FROM "@BXPGRPODRAFT"), 
(SELECT ISNULL(MAX(CAST("Code" as int)), 0) + 1 FROM "@BXPGRPODRAFT"), 
$[Employee.EmployeeID], 
$[Draft]

The final step is to create the logic into query: bx_mobile_wh9_goodsreceiptpo_query_custom

SELECT
Distinct "DocEntry", "DocType"
FROM (
SELECT '' as "Comments","OPOR"."DocEntry", 22 as "DocType", "CardCode", "DocNum", "DocDueDate", "POR1"."ItemCode" FROM "POR1" LEFT JOIN "OPOR" ON "POR1"."DocEntry" = "OPOR"."DocEntry" WHERE "DocStatus" = 'O'
UNION
SELECT '' as "Comments", "OPCH"."DocEntry", 18 as "DocType", "CardCode", "DocNum", "DocDueDate", "PCH1"."ItemCode" FROM "PCH1" LEFT JOIN "OPCH" ON "PCH1"."DocEntry" = "OPCH"."DocEntry" WHERE "DocStatus" = 'O'
UNION
SELECT "ODRF"."Comments", "ODRF"."DocEntry", 20 as "DocType", "CardCode", "DocNum", "DocDueDate", "DRF1"."ItemCode" FROM "DRF1" LEFT JOIN "ODRF" ON "DRF1"."DocEntry" = "ODRF"."DocEntry" WHERE "ODRF"."ObjType" = '20' AND "DocStatus" = 'O'
) as "DOCUMENTS"
WHERE
(CASE WHEN isnull($[ItemCode], '') = '' THEN 1 ELSE CASE WHEN "DOCUMENTS"."ItemCode" = $[ItemCode] THEN 1 ELSE 0 END END) = 1
AND (CASE WHEN isnull($[DocNum], '') = '' THEN 1 ELSE CASE WHEN "DOCUMENTS"."DocNum" = $[DocNum] THEN 1 ELSE 0 END END) = 1
AND (CASE WHEN isnull($[CardCode], '') = '' THEN 1 ELSE CASE WHEN "DOCUMENTS"."CardCode" = $[CardCode] THEN 1 ELSE 0 END END) = 1
AND (CASE WHEN isnull($[DueDate], '') = '' THEN 1 ELSE CASE WHEN "DOCUMENTS"."DocDueDate" = $[DueDate] THEN 1 ELSE 0 END END) = 1
AND (
CASE WHEN isnull((select "U_DraftNum" from "@BXPGRPODRAFT" WHERE "U_EmpID" = $[EmployeeNo]), '') = ''
THEN 1
ELSE
CASE WHEN "DOCUMENTS"."Comments" = (select "U_DraftNum" from "@BXPGRPODRAFT" WHERE "U_EmpID" = $[EmployeeNo])
THEN 1 ELSE 0
END
END) = 1;

The result of the solution: