Generating Custom Replenishment Material Queue

Epicor ERP 10
, ,
1

First I want to say this site and group has been a great place so thanks to all contributors. I’m sharing my code here.

I needed a way to automatically generate material queues for replenishment. The built-in replenishment function does this for the most part but the order in which it took inventory is either the supply bin, primary bin or by bin sequence. We needed to replenish based on other data. In our case by lot number which is a date code so inventory is used in FIFO.

I decided to create a Data Directive BPM on PartBinInfo table since that’s where we define the replenishing bins then monitor OnhandQty change. So here is my working code. It basically creates a STK-STK queues with priority 1. I couldn’t find a way to create RAU-STK or RMN-STK queue nor control priority number. I’m no expert so any improvement suggestions are welcome.

Thanks

/*	Generate Replenishment Queue	*/
decimal vOldQty = 0;
decimal vNewQty = 0;
decimal vMaxQty = 0;
decimal vMinQty = 0;
decimal vSafeQty = 0;
decimal vFillQty = 0;
decimal vAllocQty = 0;
string vPartNum = string.Empty;
string vToBinNum = string.Empty;

// get replenishing bininfo settings
foreach (var tt in (from row in ttPartBinInfo where row.Company == Session.CompanyID select row))
{
		if (tt.RowMod == "")		// old value
		{
				vOldQty = tt.OnhandQty;
		}
		if (tt.RowMod == "U")		// new value
		{
				vNewQty = tt.OnhandQty;
				vPartNum = tt.PartNum;
				vMaxQty = tt.MaximumQty;
				vMinQty = tt.MinimumQty;
				vSafeQty = tt.SafetyQty;
				vFillQty = tt.MaximumQty-tt.OnhandQty;
				vToBinNum = tt.BinNum;
		}
}

if (vFillQty > 0)			// need to replenish
{
		if (vOldQty >= vMinQty && vNewQty < vMinQty)		// crossed minimum	/* add || (vOldQty >= vSafeQty && vNewQty < vSafeQty) for crossed safety
		{
				// create MoveRequest handle
				Erp.Contracts.MoveRequestSvcContract hMoveRequest = null;
				hMoveRequest = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.MoveRequestSvcContract>(this.Db);

				if (hMoveRequest != null)
				{
						// clear any existing material queue records for this part with matching ToBinNum
						// create MaterialQueue handle
						Erp.Contracts.MaterialQueueSvcContract hMaterialQueue = null;
						hMaterialQueue = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.MaterialQueueSvcContract>(this.Db);

						// get a list of existing queues
						var QueueList = (from mq in Db.MtlQueue where mq.Company == Session.CompanyID && mq.PartNum == vPartNum && mq.ToBinNum == vToBinNum && mq.Priority == 1 && mq.TranStatus == string.Empty select new {mq.MtlQueueSeq}).ToList();
						if (QueueList.Count > 0)
						{
								// delete existing queues
								foreach (var ql in QueueList)
								{
										Erp.Tablesets.MaterialQueueTableset tsMQ = hMaterialQueue.GetByID(ql.MtlQueueSeq);
										tsMQ.MtlQueue[0].RowMod = "D";
										hMaterialQueue.Update(ref tsMQ);
								}
						}

						// get a list of bins order by lotnum, binnumm
						var BinList = (from row in Db.PartBin where row.Company == Session.CompanyID && row.PartNum == vPartNum /*&& row.BinNum.Substring(0,1) == "1"*/ orderby row.LotNum,row.BinNum select new {row.PartNum,row.WarehouseCode,row.BinNum,row.LotNum,row.OnhandQty,row.DimCode}).ToList();

						// go through bins and calculate qty to move
						foreach (var bl in BinList)
						{
								if (vFillQty > bl.OnhandQty)
								{
										vAllocQty = bl.OnhandQty;
								}
								else
								{
										vAllocQty = vFillQty;
								}
								vFillQty = vFillQty - bl.OnhandQty;

								// add a STK-STK material queue record
								Erp.Tablesets.MoveRequestTableset ts = new Erp.Tablesets.MoveRequestTableset();
								hMoveRequest.GetNewMoveRequest("MI",ref ts);
								hMoveRequest.OnChangePartNum(bl.PartNum,ref ts);
								hMoveRequest.OnChangeFromWhse(bl.WarehouseCode,ref ts);
								hMoveRequest.OnChangeFromBin(bl.BinNum,ref ts);
								hMoveRequest.OnChangeToWhse(bl.WarehouseCode,ref ts);
								hMoveRequest.OnChangeToBin(vToBinNum,ref ts);

								ts.MoveRequest[0].RequestQty = vAllocQty;
								ts.MoveRequest[0].LotNumber = bl.LotNum;

								hMoveRequest.ProcessRequest(ref ts);

								if (vFillQty <= 0) 
								{
										break;
								}
						}
				}
		}
}
1 Like