How to generate Move Order by scripting

This note describes how to generate custom Replenishment orders

You can create the following move order types by changing the order type :

PmxMoveOrderType.Move;
PmxMoveOrderType.PutAway;
PmxMoveOrderType.Replenish;
PmxMoveOrderType.WarehouseTransfer;
PmxMoveOrderType.PutAwayProduction;

You can automate this task with the following script configured for Produmex Robot tool.
In this example I am going to create a Replenishment order from a specific location (01-B-01-02)

Creating SQL VIEW as an input for the script

First, create an SQL view that contains all source data:

CREATE VIEW PMX_WMS_GENERATE_MOVE_ORDER AS
select 
 PMX_INVT."ItemCode",
 OITM."ItemName",
 PMX_INVT."Quantity",
 PMX_INVT."Uom",
 PMX_INVT."ItemTransactionalInfoKey" as "BatchID",
 PMX_INVT."StorLocCode", 
 PMX_INVT."LogUnitIdentKey",
 PMX_INVT."QuantityUom2", 
 PMX_INVT."QualityStatusCode",
 "LocSource"."PmxWhsCode" as "SourceWH",
 "V_OSSL"."Code" as "DesctLoc" , 
 "LocQuaranti"."PmxWhsCode" as "DesctWH"
from 
 PMX_INVT 
 -- First location where the QUARANTI is configured in OSE>BinLocation>Quality Status
 left join (SELECT TOP 1 * FROM PMX_OSSL where PMX_OSSL."QualityStatusCode" = 'QUARANTI') as "V_OSSL"
 on PMX_INVT."QualityStatusCode" = "V_OSSL"."QualityStatusCode" 
 -- source location code
 left join PMX_OSEL as "LocSource" on PMX_INVT."StorLocCode" = "LocSource"."Code"  
 -- Target location clode
 left join PMX_OSEL as "LocQuaranti" on "V_OSSL"."Code" = "LocQuaranti"."Code"  
 -- ItemName
 left join OITM on OITM."ItemCode" = PMX_INVT."ItemCode"
where 
 PMX_INVT."StorLocCode" = '01-B-01-02'   

Creating the script

Next, save the following script as GenerateReplenishment.cs into folder C:\Program Files\Produmex\Produmex Tools\CsScript\

using System;
using System.Reflection;
using System.Transactions;
using Produmex.Foundation.Data.Sbo;
using Produmex.Foundation.Data.Sbo.Attributes;
using Produmex.Foundation.Data.Sbo.BusinessObjects;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Convertors;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Definitions.Tables;
using Produmex.Foundation.Data.Sbo.Providers;
using Produmex.Foundation.Data.Sbo.Providers.Generics;
using Produmex.Foundation.Data.Sbo.Utilities;
using Produmex.Foundation.Diagnostics;
using Produmex.Foundation.Reflection;
using Produmex.Foundation.Resources;
using Produmex.Foundation.SboGui;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions;
using Produmex.Sbo.Logex.Data.Providers;
using System.Diagnostics;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables;
using System.Text;
using Produmex.Foundation.Data.SqlClient;
using System.Globalization;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Generics;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Definitions;
using Produmex.Foundation.Data.Sbo.BusinessObjects;
using PmxObjectTypes = Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.PmxObjectTypes;
using static Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables.PmxMoveOrderTypeColumnValues;

namespace AddUpdateData
{
class Program
{
private static string CONNECTION_STRING = "ADDYOURCONNECTIONSTRINGHERE";
private static readonly ILog s_log = LogProvider.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);

static void Main(string[] args)
{
try
{
DoSomething();
}
catch (Exception ex) 
{
s_log.Error("", ex);
Console.WriteLine(ex);
Console.ReadLine();
}
}

public static void DoSomething()
{
// OPEN TRANSACTION
using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
{
using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
{
conn.Open();
string query = @"select * from PMX_WMS_GENERATE_MOVE_ORDER order by LogUnitIdentKey";
PmxMoveOrderProvider prov = new PmxMoveOrderProvider(conn);
PmxMoveOrder move = prov.GetNewBO();
int newLineNum = 0;
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
string q_LogUnitIdentKey;
string q_ItemTransactionalInfoKey;
bool addToMove;

move.FromPmxWhsCode = rs1.GetTypedValue<string>("SourceWH");
move.ToPmxWhsCode = rs1.GetTypedValue<string>("DesctWH");
move.Priority = 200;
move.Remarks = "";
move.MoveOrderType = PmxMoveOrderType.Replenish;
while (!rs1.EoF)
{
q_LogUnitIdentKey = " is NULL ";
q_ItemTransactionalInfoKey = " is NULL ";
addToMove = false;
if (rs1.GetTypedValue<int>("LogUnitIdentKey") != 0)
{
q_LogUnitIdentKey = " = " + rs1.GetTypedValue<int>("LogUnitIdentKey").ToString();
}
if (rs1.GetTypedValue<int>("BatchID") != 0)
{
q_ItemTransactionalInfoKey = " = " + rs1.GetTypedValue<int>("BatchID").ToString();
}
query = @"select COUNT(*) as HasMoveOrde from PMX_MOLI where LineStatus = 'O' and ItemCode = '"
+ rs1.GetTypedValue<string>("ItemCode") + "' and Quantity = "
+ rs1.GetTypedValue<double>("Quantity") + " and ItemTransactionalInfoKey " 
+ q_ItemTransactionalInfoKey + " and SrcStorLocCode = '"
+ rs1.GetTypedValue<string>("StorLocCode") + "' and SrcLogUnitIdentKey "
+ q_LogUnitIdentKey;

using (ISboRecordset rs2 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
if (rs2.GetTypedValue<int>("HasMoveOrde") == 0 )
{
addToMove = true;
}
}
if (addToMove)
{
newLineNum++;
PmxMoveOrderLine newAddedLine = prov.GetNewAddedLine(move);
newAddedLine.ItemCode = rs1.GetTypedValue<string>("ItemCode");
newAddedLine.ItemDescription = rs1.GetTypedValue<string>("ItemName");
newAddedLine.OpenQuantity = rs1.GetTypedValue<double>("Quantity");
newAddedLine.Quantity = rs1.GetTypedValue<double>("Quantity"); 
newAddedLine.QuantityPerUom = 1.0;
newAddedLine.SourceStorageLocationCode = rs1.GetTypedValue<string>("StorLocCode"); // in case of PutAway the source must be a dock
newAddedLine.DestinationStorageLocationCode = rs1.GetTypedValue<string>("DesctLoc");
newAddedLine.DestinationStorageLocationCode = "02-dock";
newAddedLine.QualityStatusCode = rs1.GetTypedValue<string>("QualityStatusCode");
if (rs1.GetTypedValue<int>("BatchID") != 0)
{
newAddedLine.ItemTransactionalInfoKey = rs1.GetTypedValue<int>("BatchID");
}
if (rs1.GetTypedValue<int>("LogUnitIdentKey") != 0)
{
newAddedLine.SourceLogisticUnitIdentKey = rs1.GetTypedValue<int>("LogUnitIdentKey");
}
}
rs1.MoveNext();
}
if (newLineNum > 0)
{
prov.AddBO(move);
} 
}
}
scope.Complete();
}
}
}
}

Run the Robot tool with the necessary parameters. Set the connection string in the last parameter (yourconnectionstringhere)

"C:\Program Files\Produmex\Produmex Tools\Produmex.Sbo.Logex.Tools.Robot.exe" /t:csscript /a1:"C:\Program Files\Produmex\Produmex Tools\CsScript\GenerateReplenishment.cs" /cs:yourconnectionstringhere
Pause