This article describes how to update continuously the item default location per warehouse.
This feature would be usefull in a dynamic picking organization, where picking location are not strictly allocated to an item and can switch from one article to the other very frequently.
As a consequence, it is very important to update the default bin location the item to ensure efficiency during put away flows and replenishment calculation.
NB : you can find another article that explains how to manage replenishment for those default location.
That script should be triggered every night to insert new item default location, or to update existing item default location in PMX_ITWL which accept ONE default location per item and per warehouse.
The first step is to defined the right query that will allow to build a proper list of item
- with stock on a pick location
- with no default location OR with an empty default location
Worst case scenario would be to have an item with several picking locations, and one of them is actually its default location. The script would not be allow to change it until that default location become empty.
Then second and final step would be to insert or to update the data related to the item.
Looking at the technical key of PMX_ITWL, we can apply the simple rule :
- if there is no key in PMX_ITWL => INSERT
- if there is already a key in PMX_ITWL => UPDATE
Save the attached script under your script folder.
using Produmex.Foundation.Data.Sbo;
using Produmex.Foundation.Data.Sbo.BusinessObjects;
using Produmex.Foundation.Data.Sbo.Utilities;
using Produmex.Foundation.Diagnostics;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.Providers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
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()
{
using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
{
using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
{
conn.Open();
PmxItemWarehouseDefaultLocationProvider pmxItemWarehouseDefaultLocationProvider = new PmxItemWarehouseDefaultLocationProvider(conn);
// 1 - Find Items that needs Update OR an Insert
string query = @"SELECT DISTINCT STOCK.""PmxWhsCode""
, STOCK.""ItemCode""
, STOCK.""StorLocCode""
, DEF_LOC.""InternalKey""
, DEF_LOC.""DefaultLocationZone""
, DEF_LOC.""PmxOnHand""
FROM
(SELECT PMX_INVT.""ItemCode"", ""SEQUENCE"", PMX_INVT.""StorLocCode"", PMX_OSEL.""PmxWhsCode""
FROM PMX_INVT
INNER JOIN PMX_OSSL ON PMX_INVT.""StorLocCode"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
INNER JOIN PMX_OSEL ON PMX_INVT.""StorLocCode"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN'
GROUP BY PMX_INVT.""ItemCode"", ""SEQUENCE"", PMX_INVT.""StorLocCode"", PMX_OSEL.""PmxWhsCode""
) AS STOCK
LEFT JOIN (SELECT PMX_ITWL.""InternalKey"",PMX_ITWL.""ItemCode"", PMX_ITWL.""DefaultLocationZone"", PMX_ITWL.""PmxWhsCode"", SUM(PMX_INVT.""Quantity"") AS ""PmxOnHand""
FROM PMX_ITWL
LEFT JOIN PMX_OSSL ON PMX_ITWL.""DefaultLocationZone"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
LEFT JOIN PMX_OSEL ON PMX_ITWL.""DefaultLocationZone"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN' AND PMX_OSEL.""PmxWhsCode""=PMX_ITWL.""PmxWhsCode""
LEFT JOIN PMX_INVT ON PMX_INVT.""StorLocCode""=PMX_ITWL.""DefaultLocationZone"" AND PMX_INVT.""ItemCode""=PMX_ITWL.""ItemCode""
GROUP BY PMX_ITWL.""InternalKey"",PMX_ITWL.""ItemCode"", PMX_ITWL.""DefaultLocationZone"", PMX_ITWL.""PmxWhsCode""
) AS DEF_LOC ON DEF_LOC.""ItemCode"" = STOCK.""ItemCode"" AND DEF_LOC.""PmxWhsCode"" = STOCK.""PmxWhsCode""
WHERE 1=1
AND STOCK.""SEQUENCE"" = (SELECT MIN(""SEQUENCE"")
FROM PMX_INVT
JOIN PMX_OSSL ON PMX_INVT.""StorLocCode"" = PMX_OSSL.""Code"" AND PMX_OSSL.""IsPickLoc""='Y'
JOIN PMX_OSEL ON PMX_INVT.""StorLocCode"" = PMX_OSEL.""Code"" AND PMX_OSEL.""TypeCode""='BIN'
WHERE 1=1
AND PMX_INVT.""ItemCode"" = STOCK.""ItemCode""
AND PMX_OSEL.""PmxWhsCode"" = STOCK.""PmxWhsCode""
)
--Only Location with a sequence (filter production input and output bin, dock, ...)
AND STOCK.""SEQUENCE"" > 0
--Only Items if stock on default location = 0
AND ISNULL(DEF_LOC.""PmxOnHand"",0)=0
";
// Run upper Query
using (ISboRecordset rs1 = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
while (!rs1.EoF)
{
var key = rs1.GetTypedValue<int>("InternalKey");
// INSERT Records if no existing InternalKey in PMX_ITWL
if (key == 0)
{
PmxItemWarehouseDefaultLocation entry = pmxItemWarehouseDefaultLocationProvider.GetNewBO();
entry.ItemCode = rs1.GetTypedValue<string>("ItemCode");
entry.PmxWhsCode = rs1.GetTypedValue<string>("PmxWhsCode");
entry.DefaultLocationZone = rs1.GetTypedValue<string>("StorLocCode");
pmxItemWarehouseDefaultLocationProvider.AddBO(entry);
}
// UPDATE existing Records using InternalKey in PMX_ITWL
else
{
PmxItemWarehouseDefaultLocation entry = pmxItemWarehouseDefaultLocationProvider.GetBO(rs1.GetTypedValue<int>("InternalKey"));
entry.DefaultLocationZone = rs1.GetTypedValue<string>("StorLocCode");
pmxItemWarehouseDefaultLocationProvider.UpdateBO(entry, false, false);
}
rs1.MoveNext();
}
}
}
scope.Complete();
}
}
}
}
Then you need to use that script in a .bat file that can be triggered manually, by a windows task, by the Notification Listener or through a button in SAP or on the fat client.
"C:\Program Files\Produmex\Produmex Tools\Produmex.Sbo.Logex.Tools.Robot.exe" /t:csscript /a1:"C:\ProgramFiles\Produmex\ProdumexTools\CsScript\UpdateOrInsertItemDefaultLocation.cs" /cs:yourconnectionstring
Pause
Comments
0 comments
Please sign in to leave a comment.