Configurator Server UD method to execute BAQ

Kinetic ERP
,
1

Hello
I am required to execute a BAQ(External) to get some info required by the configurator methods. I am trying a Server UD Method to execute this BAQ but failing completely.

I’ve done this in customization, but it seems I must do it differently in the script editor for this UD methods.

First I tried the usual DynamicQueryAdapter way, but it seems that is not available in Ice.BO.Contracts.DynamicQuery since it says it is not found.

I’ve seen some references using GetService which seems to work, but the BAQ methods from this way of doing it fail because they seem to use Ice.Tablesets.QueryExecutionTableset instead of the common QueryExecutionDataSet.

Then I tried it with Ice.Tablesets but something is different and support is not responding yet, so I though I might try here.

Ice.Contracts.DynamicQuerySvcContract baqService;
baqService = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);

QueryExecutionTableset qets = baqService.GetQueryExecutionParametersByID("gi_ObtenerTiempoLaminadoPza");

So far, good no sintax error

But if I try to clear the ExecutionParameter table and set my parameters,

qets.ExecutionParameter.Clear();
qets.ExecutionParameter.AddExecutionParameterRow("Alto", medMayor, "decimal", false, null, "A");

I get a that ExecutionParameter doesn’t have the AddExecutionParameterRow.

image

If I try directly adding rows, it says the Rows.Add method is not available also.

If I try changing the parameters that are already in the table, I get that I cant’ convert string to int

qets.Tables["ExecutionParameter"].Rows[0].Item["RowMod"] = "A";

image

That comes from the Tables[“ExecutionParameter”] it seems the reference by Name doesn’t work…

So I am completely lost in this, any help would be greatly appreciated.

2

mmm Got farther, but now I get no sintax errors, but when executing this method it say the transaction is completed…

decimal medMenor = 0.0M;
decimal medMayor = 0.0M;
decimal result = 0.0M;

//Just a simple operation to set the larger value in medMayor and the lowest in medMenor
if (Ancho>Alto){
	medMayor = Ancho;
	medMenor = Alto;
}
else{
	medMayor = Alto;
	medMenor = Ancho;
}

Ice.Contracts.DynamicQuerySvcContract baqService;
baqService = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);

QueryExecutionTableset qets = baqService.GetQueryExecutionParametersByID("gi_ObtenerTiempoLaminadoPza");

qets.ExecutionParameter.Clear();
ExecutionParameterRow row = new ExecutionParameterRow();
row[0]="Alto";
row[1]=medMayor.ToString();
row[2]="decimal";
row[3]=false;
row[4]=Guid.NewGuid();
row[5]="A";

qets.ExecutionParameter.Add(row);

row = new ExecutionParameterRow();
row[0]="Ancho";
row[1]=medMenor.ToString();
row[2]="decimal";
row[3]=false;
row[4]=Guid.NewGuid();
row[5]="A";

qets.ExecutionParameter.Add(row);

System.Data.DataSet ds = baqService.ExecuteByID("gi_ObtenerTiempoLaminadoPza", qets);

System.Data.DataRow resRow =ds.Tables["Results"].Rows[0];

result = (decimal)resRow[0];

qets = null;
baqService = null;
return 
result;

My guess is that something is breaking through the execution of the BAQ, may still be related to the parameters or maybe a bad reference in the results? It is hard to debug this in product configurator, even worse when they are nested configurators involved.

Error Detail

Identificación de correlación: 7c93c7ae-99c0-49b4-a888-9da6c4e71081
Mensaje: An error occurred while executing the command definition. See the inner exception for details.
Mensaje de excepción interna: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
Programa: Epicor.ServiceModel.dll
Método: ShouldRethrowNonRetryableException

Stack Trace

at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
at Erp.Proxy.BO.ConfigurationRuntimeImpl.ProcessKeepWhen(ConfigurationSequenceDataSet configurationSequenceDS, ConfigurationRuntimeDataSet configRuntimeDS, PcValueDataSet pcValueDS, String parAltMethod, Boolean checkNextCfg, Boolean& enableNextPage)
at Erp.Adapters.ConfigurationRuntimeAdapter.ProcessKeepWhen(PcValueDataSet pcValueDS, String parAltMetthod, Boolean checkNextCfg, Boolean& enableNextPage)

Inner Exception

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

3

What info are you attempting to get? For what purpose? I think you might be attempting something simply done using the Db context and a LINQ query. Most of the time I use simple server side UD methods to gather data from UD tables or PriceLists depending on the need the only time I used BAQ was to quick fill my combo boxes as this is already a simple setup inside the configurator.

Something like this:

image
image636×698 20.3 KB

or this (against the Db context):

List<string> ListCodes = 
							(((from cust in Db.Customer
              where cust.CustID == Context.CustomerID && cust.Company == Context.CompanyID
              join cpl in Db.CustomerPriceLst on new {cust.CustNum, STID=ShipToID} equals new {cpl.CustNum, STID=cpl.ShipToNum} into cpls
              from cpl in cpls
							where cpl.Company == Context.CompanyID
              join pls in Db.PriceLst on cpl.ListCode equals pls.ListCode
              where (pls.StartDate <= PricingDate && pls.EndDate >= PricingDate) || (pls.StartDate <= PricingDate && pls.EndDate == null)
              select new
              {
									ListCode =(string)cpl.ListCode, Seq=(cpl.SeqNum)
              }))
							.Concat(
							(from cust in Db.Customer
              where cust.CustID == Context.CustomerID && cust.Company == Context.CompanyID
              join cpg in Db.CustGrupPriceLst on cust.GroupCode equals cpg.GroupCode into cpgs
              from cpl in cpgs
							where cpl.Company == Context.CompanyID
              join pls in Db.PriceLst on cpl.ListCode equals pls.ListCode
              where (pls.StartDate <= PricingDate && pls.EndDate >= PricingDate) || (pls.StartDate <= PricingDate && pls.EndDate == null)
              select new
              {
                  ListCode =(string)cpl.ListCode, Seq=(100+cpl.SeqNum)
              })))
							.OrderBy(r=>r.Seq)
							.Select(r =>(string)r.ListCode).ToList();

Without more information on what you are doing it’s difficult to provide direction as there are usually better ways to accomplish a task

1 Like
4

Hi Clint

The problem is that the data I need to pull is in another server on another aplication database. So I think the DB context and Linq is not available, is it?

Hence the external BAQ to access this, I don’t know if there is a better way to access that external data.

If I can’t pull this off, I’ll have to save that data with an interface into QuoteDtl(all through Epicor, nothing directly to DB I must add) and then use this on the configurator rule methods, no big deal but wanted to avoid that because if the user changes some parameters in the quoteline that value won’t be refreshed(unless I set up customization to check this every time which is already a slow process for us)

Another option would be to set up a UD table with this data, and then pulling with Linq as you suggest, but then we’ll end up with two copies to mantain for both applications and we are talking about matrix data of several thousand nodes so mistakes will be present for sure.

All this is to get Production Standard Time, this specific case(matrix data) requires ranges of measures of the configurated piece(i.e.45.32" x 70") to set this parameter and the add several other time extras(but just a yes/no question to set) in order to obtain just the time for one operation.

5

Several way you could do that. Create views or using SQL connect directly using query, but you said a BAQ, so assumption was you had data accessible using Epicor hence the Db context should be viable.

6

I did write BAQ(External) but I guess I could have explained more throughly to avoid confusion. If I create a view(I assume you mean to create a linked server in SQL and then create the view through it) would that be available in the Db context? I would think that the Db context would only apply to Epicor tables/views.

Your other suggestion to query directly through SQL is viable in Script Editor? I thought that was a huge no no in Epicor, hence the need to use external BAQs. Do you mean somehow adding references to Net.SqlCient and then use sqlCommand or sqlAdapter or am I getting it wrong?

7

Well it would technically be a huge nono querying against Epicor as that is what the Db context is for, but querying against another DB server for data seems up to you. You can get the data you are looking for several ways, but best would be either an import to UD table (easiest to access as I already showed you 2 different methods, and I can provide access to UD tables in a server side UD method as well. Others would be stored procedure or function returning a dataset/table of data I don’t know your end goal and parameters you are working within so hard to says what would be best case for this.

8

Yes, the simple way is to replicate the matrix data into a UD table, but the maintenance required to have that thousands of rows in both applications is an issue.
Probably will have to create an app to delete that UD table and insert again the whole data everytime they modify on the base application. I wish I could get that services working, but it seems it will take too much time to keep trying, I’ll keep it on the back of my mind since it opened a whole new possibilites for what configurator might achieve in our case.

Thank you for your input, Clint

1 Like