@askulte
I’m not sure where I was in development when I made my comment a few months ago but our strategy has evolved a bit. I will share the allocation function below but it is tailored to our specific use case and might not make sense in a different context. Everything we make passes through a final inspection operation where the operator has access to an HTML5/Javascript/React based application that I created. This application integrates various digital inspection instruments and many features of MES that we have recreated using Epicor Functions. When the operation is completed, one of the last steps is firing this Allocation Function for that Lot only. This is possible because everything we do is made to order, thus a demand exists the moment the product is produced. We have worked hard on implementing an IOT pick-to-light system that directs the operator to split up the manufactured material into bins that correspond to orders in a staging area. You will see both inventory transfer and allocation in the function below. This is handled by a separate Epicor Function called from the HTML. Paired with this is a BAQ search in FWB that grabs all the demands fully allocated and ready to ship (with similar filters to what you described). This is necessary because we haven’t figured out how to print the Material Queue sheets automatically, else this would be a scheduled Epicor Function.
Apologies for poor coding, this project evolved rapidly and needs better comments. Hopefully you can pull something useful out of this.
AllocateLot(lotNum, completedQty)
const int PS_MONTHS = 4; // if DoNotShipBefore <= today() + PS_MONTHS material goes to Pre-Shipping Area
try{
this.CallService<OrderAllocSvcContract>(orderAllocBO => {
this.CallService<JobPartSvcContract>(jobPartBO => {
this.CallService<MaterialQueueSvcContract>(materialQueueBO => {
string partNum = this.Db.JobPart.Where(r => r.JobNum == this.lotNum).First().PartNum;
// -------------------- clear material queue of any stk-stk movements to PS zone of this part
this.debug += "starting material queue delete,";
MaterialQueueTableset mtlQueue = materialQueueBO.GetRows("TranType = 'STK-STK'", 0, 0, out bool morePages);
this.debug += "a,";
var mtlQueueQuery = mtlQueue.MtlQueue.Where(x=>x.PartNum == partNum).Join(
this.Db.WhseBin,
mtl => new {mtl.Company, mtl.ToWhse, mtl.ToBinNum},
whse => new {whse.Company, ToWhse=whse.WarehouseCode, ToBinNum=whse.BinNum},
(mtl, whse) => new {mtl.Company, mtl.ToWhse, mtl.ToBinNum, whse.ZoneID, mtl.MtlQueueSeq, mtl=mtl}
)
.Where(x=>x.Company == "OKCCM" && x.ToWhse == "Main" && x.ZoneID == "PS")
.Select(x=>x.mtl);
this.debug += "b,";
foreach (var mtlQueueItem in mtlQueueQuery){
this.debug += "pre deleting " + mtlQueueItem.MtlQueueSeq + " ,";
// SelectedForProcessing
mtlQueueItem.RowMod = "D";
}
materialQueueBO.Update(ref mtlQueue);
this.debug += "end material queue delete,";
// ------------------ create a table set to store output
orderAllocBO.SetCalcPref(true); // set OrderAlloc to auto calculate fullfillment on search
string cMessageText = "";
string oErrorText = "";
string oDemandtype = "";
bool lReleased = false;
bool opFWBLimitedRefresh = false;
this.data = new DataSet("data");
DataTable finalAllocationTable = this.data.Tables.Add("allocations");
finalAllocationTable.Columns.Add("zoneId", typeof(string));
finalAllocationTable.Columns.Add("location", typeof(string));
finalAllocationTable.Columns.Add("binNum", typeof(string));
finalAllocationTable.Columns.Add("orderNum", typeof(Int32));
finalAllocationTable.Columns.Add("qty", typeof(Int32));
finalAllocationTable.Columns.Add("isEntireSingleLine", typeof(bool));
DataTable myAllocationTable = new DataTable("allocations");
myAllocationTable.Columns.Add("zoneId", typeof(string));
myAllocationTable.Columns.Add("location", typeof(string));
myAllocationTable.Columns.Add("binNum", typeof(string));
myAllocationTable.Columns.Add("orderNum", typeof(Int32));
myAllocationTable.Columns.Add("qty", typeof(Int32));
DataTable psBinContentsTable = this.data.Tables.Add("psBinContents");
psBinContentsTable.Columns.Add("binNum", typeof(string));
psBinContentsTable.Columns.Add("orderNum", typeof(Int32));
psBinContentsTable.Columns.Add("orderLine", typeof(Int32));
psBinContentsTable.Columns.Add("partNum", typeof(string));
psBinContentsTable.Columns.Add("qty", typeof(Int32));
// ---------------- get order alloc tableset
string stockBinNum = default;
OrderAllocTableset myOrderAllocTableset = this.EfxLib.InventoryAutomationLib.GenerateOrderAllocTablesetByPart(partNum);
int lineCount = myOrderAllocTableset.OrderAlloc.Count();
var myOrderAllocRows = myOrderAllocTableset.OrderAlloc.Where(r => r.RemainingToReserve > 0); // filter to demands that need material
// for each demand,
foreach(OrderAllocRow myOrderAllocRow in myOrderAllocRows.OrderBy(x => x.ReqDate).ThenBy(x=>x.NeedByDate).ThenBy(x=>x.OrderNum)) // demands are ordered by ReqDate, NeedByDate, OrderNum
{
try{
this.debug += $"\nOrderNum: {myOrderAllocRow.OrderNum} OrderLine: {myOrderAllocRow.OrderLine} RemainingToReserve {myOrderAllocRow.RemainingToReserve}";
//;
myOrderAllocRow.SelectedForAction = true;
myOrderAllocRow.ReleaseCount = 0; // lineCount; //should be the count of records in myOrderAllocTableset.OrderAlloc
myOrderAllocRow.LineCount = 0; //lineCount;
myOrderAllocRow.RowMod = "U";
(myOrderAllocTableset, cMessageText, oErrorText, oDemandtype) = this.EfxLib.InventoryAutomationLib.GetOnHandQtys(myOrderAllocTableset); // get the current on hand quanitites
PartAllocLotRow myPartAllocLot = myOrderAllocTableset.PartAllocLot.Where(r => r.LotNum == this.lotNum && r.AvailableQty > 0).FirstOrDefault(); // find a chunk of unallocated parts
if (myPartAllocLot == null) break;
this.debug += $"\nLotNum: {myPartAllocLot.LotNum} AvailableQty: {myPartAllocLot.AvailableQty}";
//stockBinNum = myPartAllocLot.BinNum;
string fromBinNum = myPartAllocLot.BinNum;
string fromZoneID = this.Db.WhseBin.Where(x=>x.Company==myPartAllocLot.Company && x.BinNum==myPartAllocLot.BinNum && x.WarehouseCode==myPartAllocLot.WarehouseCode).FirstOrDefault().ZoneID;
this.unallocatedBinNum = fromBinNum;
this.unallocatedZoneId = fromZoneID;
string toBinNum = fromBinNum;
string toZoneID = fromZoneID;
string location = "";
string binSizeID = default;
decimal estOrderMass = default;
int boxQty = default;
int count = default;
int orderBoxes = default;
string debug = "";
this.debug += $"\nFromBinNum: {myPartAllocLot.BinNum}";
int transferQty = (int)Math.Max(0, Math.Min(Math.Min(myOrderAllocRow.RemainingToReserve, this.completedQty), myPartAllocLot.AvailableQty));
if (transferQty > 0 && !(myOrderAllocRow.DoNotShipBeforeDate > BpmFunc.AddInterval(BpmFunc.Today(), PS_MONTHS, IntervalUnit.Months))) // determine if material should be transferred to pre-shipping area
{
this.debug += "\nTransfering to Pre-Shipping Area";
DataSet AvailibleBins;
(estOrderMass, binSizeID, toBinNum, toZoneID, AvailibleBins, boxQty, count, orderBoxes, debug) = this.EfxLib.InventoryAutomationLib.GetToBin(myOrderAllocRow.OrderNum, partNum, myOrderAllocRow.ShipOrderComplete, myOrderAllocRow.DoNotShipBeforeDate, myOrderAllocRow.OrderLine, transferQty); //determine the destination bin
/*
// filter out any locations already used Where(Bin.Locations is not in Locations)
AvailibleBins.Except(myAllocationTable.Select(x=>x.location))
myAllocationTable
// loop through the candidates and don't consider
*/
List<string> usedLocations = myAllocationTable.AsEnumerable().Select(x=>(string)x["Location"]).Distinct().ToList();
var binTable = AvailibleBins.Tables["Bins"];
var i = 0;
while (i < binTable.Rows.Count )
{
var binCandidate = AvailibleBins.Tables["Bins"].Rows[i];
if(!usedLocations.Contains((string)binCandidate["Location"])){
// assign toBin
toBinNum = (string)binCandidate["BinNum"];
toZoneID = (string)binCandidate["ZoneID"];
location = (string)binCandidate["Location"];
break;
}
i++;
}
if (String.IsNullOrEmpty(toBinNum)) {
throw new Exception("Invalid Bin");
}
this.debug += $"\nToBinNum: {toBinNum}";
this.debug += $"\nTransferQty: {transferQty}";
this.debug += $"\nRemainingToReserve: {myOrderAllocRow.RemainingToReserve} AvailableQty: {myPartAllocLot.AvailableQty}";
this.EfxLib.InventoryAutomationLib.InventoryTransfer(partNum, toBinNum, this.lotNum, fromBinNum, this.lotNum, transferQty); // move the inventory
(myOrderAllocTableset, cMessageText, oErrorText, oDemandtype) = this.EfxLib.InventoryAutomationLib.GetOnHandQtys(myOrderAllocTableset); // refresh on hand qtys to reflect inventory transfer
this.debug += "\nTransfered Inventory to Pre-Shipping Area";
try
{
myPartAllocLot = myOrderAllocTableset.PartAllocLot.Where(r => r.LotNum == this.lotNum && r.AvailableQty >= 0 && r.BinNum == toBinNum).First(); // should find the inventory in a new bin, if not throw error
}
catch (InvalidCastException e)
{
throw new Exception($"Could not find matching PartAllocLot for BinNum: {toBinNum}, LotNum: {this.lotNum}", e);
}
int allocQty = (int)Math.Max(0,Math.Min(myPartAllocLot.AvailableQty, Math.Min(myOrderAllocRow.RemainingToReserve, this.completedQty))); // allocate all the transfered inventory
this.debug += $"\nAllocQty: {allocQty}";
myPartAllocLot.Allocate = true;
myPartAllocLot.NewAllocatedQty = allocQty + myPartAllocLot.AllocatedQty;
//this.AvailibleQty = this.AvailibleQty - allocQty;
orderAllocBO.AllocateByLotBin(ref myOrderAllocTableset, "", "All", out cMessageText, out lReleased); // perform the allocation
orderAllocBO.GetFWBLimitedRefresh(out opFWBLimitedRefresh);
orderAllocBO.GetFWBLimitedRefresh(out opFWBLimitedRefresh);
var context = Ice.Services.ContextFactory.CreateContext<ErpContext>();
Erp.Internal.Lib.DeferredUpdate libDeferredUpdate = new Erp.Internal.Lib.DeferredUpdate(context);
libDeferredUpdate.UpdPQDemand(); // must process the deferred updates
this.debug += "\nAllocated Inventory";
this.completedQty -= allocQty;
myOrderAllocRow.SelectedForAction = false;
myOrderAllocRow.RowMod = "";
DataRow myAllocationRow = myAllocationTable.NewRow();
myAllocationRow["zoneId"] = toZoneID;
myAllocationRow["binNum"] = toBinNum;
myAllocationRow["location"] = location;
myAllocationRow["orderNum"] = myOrderAllocRow.OrderNum;
myAllocationRow["qty"] = allocQty;
// myAllocationRow["isEntireSingleLine"] = allocQty == lines.First().Qty && lines.Count() == 1 ;
myAllocationTable.Rows.Add(myAllocationRow);
}
else this.debug += "\nSkipping Transfer to Pre-Shipping Area, DoNotShipBeforeDate: {myOrderAllocRow.DoNotShipBeforeDate}";
}
catch(Exception e){
this.debug+=e.Message + ", ";
}
}
// get is single line
var orderIsEntireSingleLine = myAllocationTable.AsEnumerable()
.GroupJoin(
this.Db.OrderDtl.Where(x=>x.Company=="OKCCM"),
a => new {Company="OKCCM", OrderNum=(int)a["orderNum"]},
od => new {od.Company, od.OrderNum},
(a, ods) => new {Allocation=a, IsEntireSingleLine=(ods.Count() == 1 && ods.FirstOrDefault().SellingQuantity == (int)a["qty"])}
);
foreach(var row in orderIsEntireSingleLine){
DataRow finalAllocationRow = finalAllocationTable.NewRow();
finalAllocationRow["zoneId"] = row.Allocation["zoneId"];
finalAllocationRow["binNum"] = row.Allocation["binNum"];
finalAllocationRow["location"] = row.Allocation["location"];
finalAllocationRow["orderNum"] = row.Allocation["orderNum"];
finalAllocationRow["qty"] = row.Allocation["qty"];
finalAllocationRow["isEntireSingleLine"] = row.IsEntireSingleLine;
finalAllocationTable.Rows.Add(finalAllocationRow);
}
// get psBinContents
try
{
var psBinContentsQuery = this.Db.PartAlloc.AsEnumerable()
.Join(
myAllocationTable.AsEnumerable(),
pa => new { binNum=pa.BinNum, orderNum=pa.OrderNum },
a => new { binNum=(String)a["binNum"], orderNum=(int)a["orderNum"] },
(pa, a) => new {
binNum=(String)a["binNum"],
orderNum=(int)a["orderNum"],
orderLine=pa.OrderLine,
partNum=pa.PartNum,
qty=(pa.AllocatedQty + pa.PickingQty)
}
)
.GroupBy(
x => new {x.binNum, x.orderNum, x.orderLine, x.partNum},
(x, xs) => new {x.binNum, x.orderNum, x.orderLine, x.partNum, qty=xs.Sum(y=>y.qty)}
);
foreach(var psBinContentsRecord in psBinContentsQuery)
{
DataRow psBinContentsRow = psBinContentsTable.NewRow();
psBinContentsRow["binNum"] = psBinContentsRecord.binNum;
psBinContentsRow["orderNum"] = psBinContentsRecord.orderNum;
psBinContentsRow["orderLine"] = psBinContentsRecord.orderLine;
psBinContentsRow["partNum"] = psBinContentsRecord.partNum;
psBinContentsRow["qty"] = psBinContentsRecord.qty;
psBinContentsTable.Rows.Add(psBinContentsRow);
}
}
catch(Exception e){
this.debug+=e.Message + ", ";
}
// need to modify to show original bins
if (this.completedQty > 0){
PartAllocLotRow myPartAllocLot = myOrderAllocTableset.PartAllocLot.Where(r => r.LotNum == this.lotNum && r.AvailableQty > 0).FirstOrDefault(); // tryto find the pieces
DataRow finalAllocationRow = finalAllocationTable.NewRow();
finalAllocationRow["zoneId"] = this.Db.WhseBin.Where(x=>x.Company==myPartAllocLot.Company && x.BinNum==myPartAllocLot.BinNum && x.WarehouseCode==myPartAllocLot.WarehouseCode).FirstOrDefault().ZoneID;
finalAllocationRow["binNum"] = myPartAllocLot.BinNum;
finalAllocationRow["orderNum"] = 0;
finalAllocationRow["qty"] = this.completedQty;
finalAllocationRow["isEntireSingleLine"] = false;
finalAllocationTable.Rows.Add(finalAllocationRow);
}
this.unallocatedQty = this.completedQty;
});
});
});
}
catch(Exception e){
this.debug += e.Message;
}
GenerateOrderAllocTablesetByPart(partNum)
this.CallService<OrderAllocSvcContract>(orderAllocBO => {
this.CallService<PartAllocTemplateSvcContract>(partAllocTemplateBO => {
OrderAllocListTableset orderAllocListTableset = orderAllocBO.GetListOfOrders("","","",$"OrderRel.PartNum = '{ PartNum }'","","","","","false","OrderHed.ReservePriorityCode , OrderRel.ReqDate","","",0,0, out bool morePages, "");
this.orderAllocTableset = orderAllocBO.OrderAllocationGetRows(orderAllocListTableset, 0);
PartAllocTemplateTableset partAllocTemplateTableset = partAllocTemplateBO.GetByID("Automation");
PartAllocTemplateRow partAllocTemplateRow = partAllocTemplateTableset.PartAllocTemplate.First();
// Update orderAllocTableset.PartAllocTrans with template
foreach (PartAllocTranRow partAllocTranRow in orderAllocTableset.PartAllocTran)
{
partAllocTranRow.AddHoc = false;
partAllocTranRow.WarehouseCode = partAllocTemplateRow.WarehouseCode;
partAllocTranRow.AllocTemplateID = partAllocTemplateRow.AllocTemplateID;
partAllocTranRow.AllocTempDescAllocTemplateDesc = partAllocTemplateRow.AllocTemplateDesc;
partAllocTranRow.RowMod = "U";
}
});
});
