How to make custom bin location list in AdHoc move

This note describes how to make a custom target location list by SelectLocationForAdHocMovesHookScript

Creating SQL VIEW as an input for the script

First, create an SQL view that contains all source data. In my example I will join the WMS stock table to the query, and I am going to replace the description of the location by the on-hand quantity

MSSQL

CREATE PROCEDURE[dbo].[SP_EXAMPLE_SELECTLOCATIONFORADHOCMOVES]
       @listOfItemCode nvarchar(max),
       @warehouse nvarchar(50),
       @listOfLUIDs nvarchar(max)
AS
BEGIN
       SELECT
              DISTINCT "PMX_OSSL"."Code" AS "Code",
              PMX_OSSL.Name + ' (OnHand=' + isnull(CAST(S.OnHand as nvarchar(20)), '') + ')' AS Description  ,
              "PMX_OSEL"."PmxWhsCode" AS "WarehouseCode",
              "PMX_OSWH"."Name" AS "WarehouseName",
              "PMX_OSSL"."Name" AS "BlockMoveWhenNotEmpty",
              1 AS "Sequence"
       FROM
              "PMX_OSSL"
              INNER JOIN "PMX_OSEL" ON "PMX_OSEL"."Code" = "PMX_OSSL"."Code"
              INNER JOIN "PMX_OSWH" ON "PMX_OSEL"."PmxWhsCode" = "PMX_OSWH"."Code"
              INNER JOIN "OWHS" ON "OWHS"."WhsCode" = "PMX_OSWH"."SboWhsCode"
              INNER JOIN "PMX_INVT" ON "PMX_INVT"."StorLocCode" = "PMX_OSSL"."Code"
              LEFT JOIN
              (
                     select
                           StorLocCode,
                           sum(Quantity) as OnHand
                     from
                           PMX_INVT
                     where 
                           ItemCode IN ( @listOfItemCode )
                     group by StorLocCode) as S on PMX_OSSL.Code = S.StorLocCode 
       WHERE
              "PMX_OSSL"."IsActive" = 'Y' AND
              "PMX_OSEL"."PmxWhsCode" = @warehouse AND
              "PMX_INVT"."ItemCode" IN ( @listOfItemCode )
       ORDER BY "Sequence"
END

HANA

CREATE PROCEDURE "SP_EXAMPLE_SELECTLOCATIONFORADHOCMOVES"
(
p_listOfItemCode nvarchar(5000),
p_warehouse nvarchar(50),
p_listOfLUIDs nvarchar(5000)
)
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT
DISTINCT "PMX_OSSL"."Code" AS "Code",
"PMX_OSSL"."Name" 
/// || ' (OnHand=' || ifnull(CAST("S"."OnHand" as nvarchar(20)), '') || ')' 
AS "Description" ,
"PMX_OSEL"."PmxWhsCode" AS "WarehouseCode",
"PMX_OSWH"."Name" AS "WarehouseName",
"PMX_OSSL"."Name" AS "BlockMoveWhenNotEmpty",
1 AS "Sequence"
FROM
"PMX_OSSL"
INNER JOIN "PMX_OSEL" ON "PMX_OSEL"."Code" = "PMX_OSSL"."Code"
INNER JOIN "PMX_OSWH" ON "PMX_OSEL"."PmxWhsCode" = "PMX_OSWH"."Code"
INNER JOIN "OWHS" ON "OWHS"."WhsCode" = "PMX_OSWH"."SboWhsCode"
INNER JOIN "PMX_INVT" ON "PMX_INVT"."StorLocCode" = "PMX_OSSL"."Code"
LEFT JOIN
(
select
"StorLocCode",
sum("Quantity") as "OnHand"
from
"PMX_INVT"
where 
"ItemCode" IN ( p_listOfItemCode )
group by "StorLocCode") as "S" on "PMX_OSSL"."Code" = "S"."StorLocCode" 
WHERE
"PMX_OSSL"."IsActive" = 'Y' AND
"PMX_OSEL"."PmxWhsCode" = p_warehouse AND
"PMX_INVT"."ItemCode" IN ( p_listOfItemCode )
ORDER BY "Sequence";
END

Changing the Workflow script

Next, open the script of SelectLocationForAdHocMovesHookScript from Organizational Structure > Company object > Workflows tab, and replace the content of the script by the script below. The Mobile Client application must be restarted.

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Globalization;
using System.Reflection;
using System.Linq;
using Produmex.Foundation.Data.DbClient;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Definitions.Tables;
using Produmex.Foundation.Data.Sbo.DataObjects;
using Produmex.Foundation.Data.SqlClient;
using Produmex.Foundation.Diagnostics;
using Produmex.Foundation.SlimScreen;
using Produmex.Foundation.SlimScreen.Interfaces.Definitions.KnownDataSets;
using Produmex.Foundation.Workflows;
using Produmex.Foundation.Workflows.Parameters;
using Produmex.Foundation.Wwf.Sbo.LocalServices;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables;
using Produmex.Sbo.Logex.Data.DataObjects;

// PmxWorkflowExecutionTypes.HOOK_FLOW

namespace WorkflowScript_SelectLocationForAdHocMovesHookScript {

/// <summary> 
/// This is a hook script, meant to be customized on a "per project" basis 
/// </summary>

public class WorkflowScript_SelectLocationForAdHocMovesHookScript : WorkflowInstanceScriptBase
{
private static readonly ILog s_log = LogProvider.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);
// Input parameters * do not change *
public ReadOnlyBinder<CultureInfo> DefaultCultureInfo;
public ReadOnlyBinder<PmxOseCompany> PmxOseCompany;
public ReadOnlyBinder<List<MoveItemInfo>> MoveItemInfos;
public ReadOnlyBinder<List<int>> LUIDs;
public ReadOnlyBinder<string> PmxDestinationWarehouse;
// Output parameters * do not change *
public ReadWriteBinder<string> LocationQuery;
// Sub-flows
public WorkflowScript_SelectLocationForAdHocMovesHookScript(WorkflowInstanceBase parent, WorkflowInstanceFactory factory) : base(parent, factory)
{
} 

#region WorkflowInstanceScriptBase Members

protected override void Execute()
{
// Parameters in scope
Session session = GetScopeParameter("Session") as Session;
ISboProviderService sboProviderService = GetScopeParameter("<WwfService>ISboService") as ISboProviderService;
List<string> itemCodes = null;
List<MoveItemInfo> moveItemInfos = null;
if (MoveItemInfos != null)
{
moveItemInfos = MoveItemInfos.Get();
if (moveItemInfos != null)
{ 
itemCodes = moveItemInfos.Select(s => s.ItemCode).Distinct().ToList(); 
} 
} 

List<int> luids = null; 
if (LUIDs != null) luids = LUIDs.Get(); 
string destWarehouse = null; 
if (PmxDestinationWarehouse != null) destWarehouse = PmxDestinationWarehouse.Get(); 
LocationQuery.Set(BuildQuery.GetPossibleStorageLocations(itemCodes, luids, destWarehouse, sboProviderService.GetDbTool())); 
} 

#endregion 

#region QUERY 
/// <summary> 
/// Static class grouping our native queries 
/// </summary> 

private class BuildQuery 
{ 
public static string GetPossibleStorageLocations(List<string> itemCodes, List<int> luids, string warehouseCode, DbTool dbTool) 
{ 
DbQueryBuilder strBuilder = dbTool.GetQueryBuilder(); 
strBuilder.Append("EXEC SP_EXAMPLE_SELECTLOCATIONFORADHOCMOVES ");
strBuilder.Append(SqlCommandHelper.FormatAndEscapeColumnValue(itemCodes, CultureInfo.InvariantCulture)); 
strBuilder.Append(", " + SqlCommandHelper.FormatAndEscapeColumnValue(warehouseCode, CultureInfo.InvariantCulture));
strBuilder.Append(", " + SqlCommandHelper.FormatAndEscapeColumnValue(luids, CultureInfo.InvariantCulture)); 
return strBuilder.ToString(); 
/// FOR HANA
///
/// DbQueryBuilder strBuilder = dbTool.GetQueryBuilder(); 
/// strBuilder.Append("CALL SP_EXAMPLE_SELECTLOCATIONFORADHOCMOVES (");
/// strBuilder.Append(SqlCommandHelper.FormatAndEscapeColumnValue(itemCodes, CultureInfo.InvariantCulture)); 
/// strBuilder.Append(", " + SqlCommandHelper.FormatAndEscapeColumnValue(warehouseCode, CultureInfo.InvariantCulture));
/// strBuilder.Append(", " + SqlCommandHelper.FormatAndEscapeColumnValue(luids, CultureInfo.InvariantCulture)); 
/// strBuilder.Append(")");
/// return strBuilder.ToString(); 
} 
} 

#endregion 

} 
}