Convert SQL BAQ to C# directive

Kinetic ERP
, , ,
1

I am trying to set Replenishment Material Queue records FromWhse and FromBin and Qty the PartBin on the records creation, sorted by the lowest quantity first and then sort by the earliest transaction date.

It works but i need to only use specific warehouses.
I have a BAQ i wrote which will give me 1 record with the correct details.
(This is setting a criteria as a specific part code but i need it to be the Part code on the MtlQueue record).

select distinct
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[PartBin].[BinNum] as [PartBin_BinNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[PartBin].[LastTransDate_c] as [PartBin_LastTransDate_c]
from Erp.PartBin as PartBin
inner join Erp.MtlQueue as MtlQueue on
PartBin.Company = MtlQueue.Company
and PartBin.PartNum = MtlQueue.PartNum
and not PartBin.BinNum = MtlQueue.ToBinNum
and PartBin.WarehouseCode = ‘MAIN’
or PartBin.WarehouseCode = ‘TYLR’
where (PartBin.PartNum = ‘04176’ and PartBin.LastTransDate_c is not null)
order by PartBin.OnhandQty, PartBin.LastTransDate_c, PartBin.BinNum

I tried to replicate in C# custom code but it doesnt do as the above does.

var myQueue = ttMtlQueue.FirstOrDefault();
if (myQueue != null) {
string oldestBinNum = Db.PartBin.Where(p =>
p.Company == myQueue.Company &&
p.PartNum == myQueue.PartNum &&
p.WarehouseCode == “MAIN” ||
p.WarehouseCode == “TYLR”)
.OrderBy(p => p.OnhandQty)
.ThenByDescending(p => p.LastTransDate_c.HasValue)
.ThenBy(p => p.LastTransDate_c)
.ThenBy(p => p.BinNum)
.Select(p => p.BinNum).FirstOrDefault() ?? “”;
if (oldestBinNum != “”) {
myQueue.FromBinNum = oldestBinNum;
}
}

2

Why not use DynamicQuery business object to retrieve the data from the baq in your bpm? Probably easier than writing a huge linq statement that will be hard to understand years later

2 Likes
3

@tkoch I am not sure how to do that if i am being honest.

4

Search on this website for DynamicQuerySvc and there will be examples

5

this is all what you need mate

image
image1029×746 32.2 KB 


Ice.Tablesets.QueryExecutionTableset dsQueryExecution = new QueryExecutionTableset();
DataSet GetFields = boDynamicQuery.ExecuteByID("BAQId", dsQueryExecution);


if (GetFields.Tables[0]!= null)
{
foreach (DataRow row in GetFields.Tables[0].Rows)
{
\\do whatever you want with the data, in my example my BAQ enquiring data from OrderDtl and OrderHed tabel
string sOrderNum_LineNum_RelNum = sOrderNum_LineNum_RelNum +"\r\n"+ row["OrderHed_OrderNum"].ToString()+"_"+row["OrderDtl_OrderLine"].ToString()+"_"+row["OrderRel_OrderRelNum"].ToString(); 
}
}


\\if you want to pass parameters then
DynamicQueryAdapter MyBAQ = new DynamicQueryAdapter(this.oTrans);
                DataTable results;
		MyBAQ.BOConnect();
Ice.BO.QueryExecutionDataSet dsBAQ = yourbaq.GetQueryExecutionParametersByID(BAQID);
		dsBAQ.ExecutionParameter[0].ParameterID = "YOURPARAMETERNAME";
		dsBAQ.ExecutionParameter[0].IsEmpty = false;
		dsBAQ.ExecutionParameter[0].ParameterValue = "ParameterValueAsPerItsType";
		dsBAQ.AcceptChanges();
		MyBAQ.ExecuteByID("BAQID", dsBAQ);
if (MyBAQ.QueryResults != null && MyBAQ.QueryResults.Tables.Count > 0)
		{
			results = MyBAQ.QueryResults.Tables["Results"];
		}
else
		{
			results = new DataTable();
		}

HTH

1 Like