I am currently upgrading from ERP 10 to Kinetic (App Studio) and I have a stored procedure that I need to trigger after performing certain checks. The stored procedure returns a table, which I need to filter based on the OrderNum and check specific columns within the table.
I attempted to implement this logic in BPM custom code using the following approach:
if (part != null)
** {**
** var result = Db.ExecuteStoreCommand(“EXEC SP_Name”);**
** var relevantRow = result.FirstOrDefault(r => r.OrderNum ==
ds.OrderDtl[0].OrderNum);**
** if (relevantRow != null && (relevantRow.InsuranceSO -
relevantRow.InsuranceSOPay > 0))**
** {**
** //Some Logic**
** }**
** }**
However, I am encountering issues filtering the result set and performing checks on the returned rows. It seems like I am unable to filter the table correctly in the context of the stored procedure output.
Has anyone faced a similar situation or have any recommendations for correctly filtering and working with the table returned from a stored procedure in Kinetic’s BPM custom code?
Any advice or guidance would be greatly appreciated!
I know that’s not what you want to hear, but calling a BAQ instead of a SQL stored procedure would be a better approach IMO. With a BAQ, you can pass filters/parameters and it would be obviously much easier to maintain and upgrade in the future.
Here’s a bit of code I use in epicor functions. You may have to modify a bit to fit in a BPM.
// you may need to add a reference to Ice.Contracts.BO.DynamicQuery.dll if you've got compile errors
DataSet baqTableSet = new DataSet();
this.CallService<Ice.Contracts.DynamicQuerySvcContract>(
bo =>
{
System.String baqName = ("PLANWBResequence"); // BAQ Name
Ice.Tablesets.QueryExecutionTableset qets = bo.GetQueryExecutionParametersByID(baqName);
// Adding a filter to the BAQ call. It is very similar to adding a "Subquery Criteria" on the TOP subquery
Ice.Tablesets.ExecutionFilterRow newFilterRow1 = new Ice.Tablesets.ExecutionFilterRow();
newFilterRow1.DataTableID = "JobOpStatus"; // the table alias you want to filter in your BAQ
newFilterRow1.FieldName = "ResourceGrpID"; // the field to filter
newFilterRow1.CompOp = "="; //Operator, such as =, <>, >, >=, <, <=
newFilterRow1.RValue = this.ResourceGrpID ; // the filter value
qets.ExecutionFilter.Add(newFilterRow1);
baqTableSet = bo.ExecuteByID(baqName, qets);
});
foreach(DataRow baqRow in baqTableSet.Tables[0].Rows)
{
string jobNum = Convert.ToString(baqRow["JobOpStatus_JobNum"]);
int asmSeq = Convert.ToInt32(baqRow["JobOpStatus_AssemblySeq"]);
}
Thank you for your suggestion. I see how a BAQ could be more maintainable and easier to work with, especially when it comes to filtering and passing parameters.
However, the stored procedure I’m working with is more complex, involving multiple UNION statements and updates to certain columns, which makes it difficult to translate directly into a BAQ.
Given the complexity of the logic in my stored procedure, I believe it’s necessary to keep using it, but I’m struggling with how to properly filter the result set.
If you have any recommendations on how to handle this more effectively within BPM custom code, or if you know of any optimizations for working with stored procedures like this, I’d greatly appreciate your advice.
Thank you for the suggestion! I completely understand and appreciate the benefits of using a BAQ, especially in terms of maintainability and compatibility with Epicor’s structure. However, the stored procedure we are using interacts with external application data, not Epicor tables, and involves significant complexity.
Would it still mean recreating the logic of the stored procedure within a BAQ to align with this approach?
I’m not advocating this, and you will need more code to connect properly to your stored procedure. As with @klincecum I would suggest you use EBAQ or something similar, but this might help you access your returned data:
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlcomm;
da.Fill(ds, "resultSetTableName"); // This will execute sp and grabs the return data to table in a dataset
var col1val=ds.Tables["resultSetTableName"].Rows[0]["col1name"]; // get first row value using column name in your result table
var rsTableName=ds.Tables["resultSetTableName"].TableName; // This should be the result set table name
// Hopefully this helps see how you access returned data
I did a similar solution and found a way around it. What I did was create a temporary table in the database where the stored procedure inserts its results. After executing the stored procedure, the data is available in this temp table.
I then created a BAQ (Business Activity Query) on this table, which allowed me to pull the data into a DataView in Epicor.
If you are using the latest GA version of Kinetic then you can convert your existing SQL to a BAQ from the overflow menu and paste in the code, it will also accept parameters