How to change the order of the batches/serial numbers in Find Stock in the Picking function

In this article I am going to create a customization in order to change the sort order of the appearing stock in the "Find Stock" function according to the Expiration date and batch number.

When you press the Find Stock button on the batch quantity screen, then a list of the items will appear by default. In case there are multiple batches on the same location, then there is a second screen where you can select the batch number. Unfortunately we cannot change the order of the appearing batch on the second screen.

However there is an option to change the appearing data on the first screen. We can use the bx_mobile_wh9_querystocks_query_custom query to do this. we can create this query on a way that results multiple lines for the multiple batches and at the end we can use our own ORDER BY configuration.

We can use the BXMobileWH9_StockInfoScreen_DataRepeater_InternalDataLoad query to add additional information to the stock such as batch number and expiration date.

Necessary configuration

At first I am going to enable selecting the location and the batch number in Produmex Scan Settings

Then I need an SAP UDT to be able to transfer data between screens. In my example I created two columns, one for the Employee code and one for the selected value.

Creating custom fields

I am going to add two custom field to the list object of the Find Stock screen for the batch number and expiration date. (Open Tools > User-Defined Windows > BXPCUSTFD - CustomizationFields )

Creating SAP queries 

- I am going to use the "Pick" button click event on the item line screen to delete the old value from the  UDT. 
SAP Query name:

BXMobileWH9_PickingLinesScreen_OK_clicked

SAP Query content:

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

- Now we need the query for bx_mobile_wh9_querystocks_query_custom. This script can be found attached. This query is responsible for the appearing rows.

- The next query is the BXMobileWH9_StockInfoScreen_DataRepeater_InternalDataLoad. This query is used to add the additional data to the rows. Unfortunately we do not have any value that can help us to link the lines, so we MUST use the same query logic we used for the bx_mobile_wh9_querystocks_query_custom. If alter something the one of the queries, then you have to make it in both queries.

The script can be found attached. This query has 2 additional columns ("DataRepeater.UIBatch", "DataRepeater.UIExpDate") that will tells to the system that we want to put that information into the custom fields we made earlier.

 - I am going to use the FocusedChanged event to save the selected value to the UDT

SAP Query name:

BXMobileWH9_StockInfoScreen_DataRepeater_FocusedChanged

SAP Query content for HANA:

DO
BEGIN
DELETE FROM "@BXPPICKSELBATCH" WHERE "U_EmpID" = $[Employee.EmployeeID];
INSERT INTO "@BXPPICKSELBATCH" SELECT IFNULL(MAX(Code), 0)+1, IFNULL(MAX(Code), 0)+1, $[Employee.EmployeeID],$[DataRepeater.SelectedUIBatch] FROM "@BXPPICKSELBATCH";
END

SAP Query content for MSSQL:

DELETE FROM "@BXPPICKSELBATCH" WHERE "U_EmpID" = $[Employee.EmployeeID];
INSERT INTO "@BXPPICKSELBATCH" SELECT ISNULL(MAX(Code), 0)+1, ISNULL(MAX(Code), 0)+1, $[Employee.EmployeeID],$[DataRepeater.SelectedUIBatch] FROM "@BXPPICKSELBATCH";

- After selecting the batch number we have to load the data from the UDT and put it into the batch field on the pick item quantity screen

SAP Query name for batch managed item:

BXMobileWH9_PickingLinesPickBatchScreen_Activate

SAP Query content:

select "U_Selected" as "TextBatch" from "@BXPPICKSELBATCH" where "U_EmpID" = $[Employee.EmployeeID]

SAP Query name for serial managed item:

BXMobileWH9_PickingLinesPickSerialScreen_Activate

SAP Query content:

select "U_Selected" as "TextSerialNumber" from "@BXPPICKSELBATCH" where "U_EmpID" = $[Employee.EmployeeID]

The serial item will be automatically added.

After configuring everything you must restart the Produmex Scan application.