This note describes how to make a picklist status to “Ready” by scripting-
This script is useful in case the status of multiple picklists must be changed to “Ready”, and it can be done as a background process.
You can automate this task with the following script configured for Produmex Robot tool. In this example we will set the status to Ready for those Picklists where the DocDueDate of the sales order is older than yesterday
Creating SQL VIEW as an input for the script
First, create an SQL view that contains all source data:
MSSQL
CREATE VIEW PMX_WMS_PICKLISTS_TO_MAKE_READY AS
SELECT
DISTINCT "PMX_PLLI"."DocEntry"
FROM
"PMX_PLLI"
left join "PMX_PLPH" on "PMX_PLLI"."BaseEntry" = "PMX_PLPH"."DocEntry" AND "PMX_PLLI"."BaseType" = 'PMX_PLPH'
left join "PMX_PLHE" on "PMX_PLLI"."DocEntry" = "PMX_PLHE"."DocEntry"
WHERE "PMX_PLPH"."DocEntry" in
(
SELECT
DISTINCT "PMX_PLPL"."DocEntry"
FROM
"PMX_PLPL" left join "ORDR" ON "PMX_PLPL"."BaseEntry" = "ORDR"."DocEntry" AND "PMX_PLPL"."BaseType" = 17
WHERE
"DocDueDate" <= DATEADD(day,1,CAST(GETDATE() as date))
AND "ORDR"."DocStatus" in ('O'))
AND "PMX_PLHE"."PickListStatus" in ( 'A' , 'N')
HANA
CREATEVIEW PMX_WMS_PICKLISTS_TO_MAKE_READY AS
SELECT
DISTINCT "PMX_PLLI"."DocEntry"
FROM
"PMX_PLLI"
leftjoin "PMX_PLPH" on "PMX_PLLI"."BaseEntry" = "PMX_PLPH"."DocEntry" AND "PMX_PLLI"."BaseType" = 'PMX_PLPH'
leftjoin "PMX_PLHE" on "PMX_PLLI"."DocEntry" = "PMX_PLHE"."DocEntry"
WHERE "PMX_PLPH"."DocEntry" in
(
SELECT
DISTINCT "PMX_PLPL"."DocEntry"
FROM
"PMX_PLPL" leftjoin "ORDR" ON "PMX_PLPL"."BaseEntry" = "ORDR"."DocEntry" AND "PMX_PLPL"."BaseType" = 17
WHERE
"DocDueDate" <= ADD_DAYS(NOW(), -1)
AND
"ORDR"."DocStatus" in ('O'))
AND "PMX_PLHE"."PickListStatus" in ( 'A' , 'N');
Creating the script
Next, save the following script as makepicklistready.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.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;
namespace AddUpdateData
{
class Program
{
private static string CONNECTION_STRING = "";
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();
}
}
private static void DoSomething()
{
// Make picklists ready that are NOT ready yet
// - query picklists to make ready
// - make PL ready
// OPEN TRANSACTION
using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
{
using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
{
conn.Open();
Console.WriteLine("Connection is open");
PmxPickListProvider plProv = new PmxPickListProvider(conn);
// PmxPickListProvider PickingLineInfo
// create query to get picklists to make ready
string query_for_picklist = @"select * from PMX_WMS_PICKLISTS_TO_MAKE_READY";
// run query and set result in record set
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query_for_picklist, conn))
{
Console.WriteLine("Run query OK");
// run trough picklist headers
while (!rs1.EoF)
{
// make PL ready
Console.WriteLine("Make 1 PL ready");
plProv.MakePicklistReadyFromDocEntry(rs1.GetTypedValue<int>("DocEntry"));
Console.WriteLine("READY OK");
// next picklist
rs1.MoveNext();
Console.WriteLine("NEXT picklist");
}
}
Console.WriteLine("OUT OF query using");
//Complete transaction
scope.Complete();
}
}
}
}
}
Run the Robot tool, for example with a bat file with the content below. 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\makepicklistready.cs" /cs:yourconnectionstringhere
Pause
Comments
4 comments
Hi Robert,
Great article!
What's the format of the CONNECTION_STRING under the C# code?
Thanks,
Rafael
Dear Rafael,
the connection string must be the alias that is configured for the connection in the ProdumexSuiteInstaller.
regardsm
Robert
Thanks, Robert!
Dear Robert,
Is there a script to change the pick lists status from Packed to Closed?
I have a customer that doesn't use the shipping flow from PMX after picking, so I need to close the pick lists to release the inventory locking.
Thanks for your help.
Please sign in to leave a comment.