BPM Stored Procedure

Kinetic ERP
1

Hello

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!

2

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"]);
}
4 Likes
3

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.

Thanks again!

4

Nathan Fillion Reaction GIF

1 Like
5

If you abandon this path, we will happily help you to do it properly.

You will thank us and yourself in the end.

1 Like
6

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?

7

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
8

Can you do it in a table-valued function?

Then you can use that as an External BAQ and access it via the BPM.

1 Like
9

I have tried it but it didn’t work.
I only can run SP as insert or update.

So i think it will not work

10

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.

11

All you need to do is a SELECT statement at end of SP to return a table of results.

1 Like
12

Yeah it right.
And at the start of SP delete all data when we call this SP.
That’s what i did to make it as temp table.
Thanks a lot smith.

1 Like
13

Welcome :slight_smile:

1 Like
14

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