This note describes how to generate a picklist proposal and a picklist from a Sales Order.
In this example, a picklist is generated from a specific Sales Order. The Sales Order is stored in a variable defined at the beginning of the script, with its value assigned directly in the code.
The next step is to close any existing picklists and picklist proposals, as the provider cannot generate a new proposal if an open one already exists. To do this, the PmxPickListProposalProvider and PmxPickListProvider are used to load open documents into variables of type PmxPickListProposal and PmxPickList via the GetBO method. These documents are then closed using the CloseDocument method.
After that, a new picklist proposal is created using GetNewBO method of the PmxPickListProposalProvider. The header data is filled up from the result of the query that loads the line and header data of the Sales Order.
Next, the GeneratePickListmethod of the PickListProposalToPickListGenerator is used to generate a new picklist. The result is then loaded into a variable of type PmxPickListProposal.
Change the PMX transaction notification
Add the following logic to the stored proceduer: PMX_SP_TransactionNotificationDetail after section:
--Test object type (this can be found in the SDK 'BoObjectTypes Enumeration'
IF (@object_type = 'PMX_PLPH' and @transaction_type = 'A') BEGIN
UPDATE P
SET P.Dscription = R.Dscription
FROM PMX_PLPL P
INNER JOIN OITM O ON P.ItemCode = O.ItemCode
INNER JOIN RDR1 R on P.BaseType = 17 AND P.BaseEntry = R.DocEntry AND P.BaseLine = R.LineNum
WHERE P.DocEntry = @list_of_cols_val_tab_del
ENDCreating the script
Next, save the following script as GeneratePLP_PL.cs into folder c:\Produmex\Scripts\
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.DataObjects;
using Produmex.Foundation.Data.Sbo.Providers;
using Produmex.Foundation.Data.Sbo.Utilities;
using Produmex.Foundation.Data.SqlClient;
using Produmex.Foundation.Diagnostics;
using Produmex.Foundation.Drawing;
using Produmex.Foundation.Reflection;
using Produmex.Foundation.Resources;
using Produmex.Foundation.SboGui;
using Produmex.Foundation.Workflows.Parameters;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables;
using Produmex.Sbo.Logex.Data.DataObjects;
using Produmex.Sbo.Logex.Data.Generators.PickListController;
using Produmex.Sbo.Logex.Data.Generators.PickListGenerators;
using Produmex.Sbo.Logex.Data.Providers;
using System;
using System.Collections.ObjectModel;
using System.Diagnostics;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Transactions;
using static Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables.PmxPickListTable.Columns;
using static Produmex.Sbo.Logex.Data.ViewObjects.Definitions.Views.PmxPickedLUIDInStockViewDefinition.Columns;
namespace ProdumexWMSScriptEnvironment
{
/* **********************************************************************************************************************************************************************
Add the following logic to the stored proceduer: PMX_SP_TransactionNotificationDetail
after section:
--Test object type (this can be found in the SDK 'BoObjectTypes Enumeration'
IF (@object_type = 'PMX_PLPH' and @transaction_type = 'A') BEGIN
UPDATE P
SET P.Dscription = R.Dscription
FROM PMX_PLPL P
INNER JOIN OITM O ON P.ItemCode = O.ItemCode
INNER JOIN RDR1 R on P.BaseType = 17 AND P.BaseEntry = R.DocEntry AND P.BaseLine = R.LineNum
WHERE P.DocEntry = @list_of_cols_val_tab_del
END
************************************************************************************************************************************************************************/
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()
{
using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
{
using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
{
conn.Open();
Console.WriteLine("Connection is open");
// Parameters
string pmxWhsCode = "01";
string objType = "17";
int SalesOrderDocEntry = 16;
string TargetDock = "D001";
string PlType = "S";
// PMX Provides
PmxPickListProposalProvider plpProv = new PmxPickListProposalProvider(conn);
PmxPickListProvider pickListProv = new PmxPickListProvider(conn);
PmxWaveProvider waveProv = new PmxWaveProvider(conn);
Collection<int> generatedPickListProposals;
PmxPickList pickList;
// Close Picklis - load the open picklist of Sales Order
string query = @"
select DISTINCT PMX_PLHE.""DocEntry""
from
PMX_PLLI
LEFT JOIN PMX_PLHE on PMX_PLHE.DocEntry = PMX_PLLI.DocEntry
LEFT JOIN PMX_PLPL ON PMX_PLLI.BaseEntry = PMX_PLPL.DocEntry AND PMX_PLLI.BaseLine = PMX_PLPL.LineNum
LEFT JOIN PMX_PLPH on PMX_PLLI.""BaseEntry"" = PMX_PLPH.""DocEntry"" AND PMX_PLLI.""BaseType"" = 'PMX_PLPH'
WHERE
""PickListStatus"" in ('N', 'R')
AND PMX_PLPL.""BaseType"" = '17'
AND PMX_PLPL.""BaseEntry"" = " + SalesOrderDocEntry.ToString();
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
while (!rs1.EoF)
{
PmxPickList pl = pickListProv.GetBO(rs1.GetTypedValue<int>("DocEntry"));
pl.PickPackRemarks = "Closed by script";
pickListProv.CloseDocument(pl);
rs1.MoveNext();
}
}
// Close Picklis Proposal - load the open picklist proposal of Sales Order
query = @"
select DISTINCT PMX_PLPH.DocEntry
from
PMX_PLPL
LEFT JOIN PMX_PLPH on PMX_PLPL.""DocEntry"" = PMX_PLPH.""DocEntry""
WHERE
""DocStatus"" in ('O')
AND PMX_PLPL.""BaseType"" = '17'
AND PMX_PLPL.""BaseEntry"" = " + SalesOrderDocEntry.ToString();
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
while (!rs1.EoF)
{
PmxPickListProposal plp = plpProv.GetBO(rs1.GetTypedValue<int>("DocEntry"));
plpProv.CloseDocument(plp);
rs1.MoveNext();
}
}
// Load items from Sales Order
bool hasHeadData = false;
query = @"
select
CardCode, CardName, RDR1.ShipToCode, RDR1.""ShipToDesc"", ORDR.""DocDueDate"", ORDR.""ObjType"", ORDR.""DocEntry""
, RDR1.""LineNum"", RDR1.""ItemCode"", RDR1.""Quantity"", RDR1.""UomCode""
from
RDR1 left join ORDR on ORDR.""DocEntry"" = RDR1.""DocEntry""
where ORDR.""DocEntry"" = " + SalesOrderDocEntry.ToString();
PmxPickListProposal pickListProposal;
PmxPickListProposalLine proposalLine;
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
pickListProposal = plpProv.GetNewBO();
while (!rs1.EoF)
{
if (!hasHeadData)
{
pickListProposal.CardCode = rs1.GetTypedValue<string>("CardCode");
pickListProposal.CardName = rs1.GetTypedValue<string>("CardName");
pickListProposal.ShipToCode = rs1.GetTypedValue<string>("ShipToCode");
pickListProposal.ShipToAddress = rs1.GetTypedValue<string>("ShipToDesc");
pickListProposal.DestinationStorageLocation = TargetDock;
pickListProposal.DueDate = rs1.GetTypedValue<DateTime>("DocDueDate");
pickListProposal.PickPackRemarks = null;
pickListProposal.RouteLineDocEntry = null;
pickListProposal.IsCustomerCollect = false;
pickListProposal.PickListType = PlType;
pickListProposal.MoveToLocationCode = null;
pickListProposal.MoveToWarehouse = null;
hasHeadData = true;
}
proposalLine = plpProv.GetNewAddedLine(pickListProposal);
proposalLine.BaseType = rs1.GetTypedValue<string>("ObjType");
proposalLine.BaseEntry = rs1.GetTypedValue<int>("DocEntry");
proposalLine.BaseLine = rs1.GetTypedValue<int>("LineNum");
proposalLine.ItemCode = rs1.GetTypedValue<string>("ItemCode");
proposalLine.Quantity = rs1.GetTypedValue<double>("Quantity");
if (rs1.GetTypedValue<string>("UomCode") != "Manual")
{
proposalLine.Uom = rs1.GetTypedValue<string>("UomCode");
}
proposalLine.QuantityPerUom = 1.0;
proposalLine.QualityStatusCode = "RELEASED";
proposalLine.InventoryLockingLevel = PmxInventoryLockingLevel.ItemBatchNumberBestBeforeDate;
proposalLine.ForceBatch = false;
rs1.MoveNext();
}
if (hasHeadData)
{
plpProv.AddBO(pickListProposal);
PickListProposalToPickListGenerator pickListGenerator = new PickListProposalToPickListGenerator(conn);
int? entry = pickListGenerator.GeneratePickList(pickListProposal.DocEntry.Value);
}
}
}
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:\Produmex\Scripts\GeneratePLP_PL.cs" /cs:yourconnectionstringhere
Pause
Comments
0 comments
Please sign in to leave a comment.