Execute Dynamic Query within BPM Data Directive

Epicor ERP 10
1

Business case: We need 2 jobs to have the last operation due on the same date. If for some reason, one of these jobs gets rescheduled and the due date for the last op gets changed, we want a message top pop up informing the person who rescheduled the job that the last op due date does match the corresponding jobs last op due date.

We can have the jobs reference each other by using the JobHead.JobCode field - no problem
I can get the Last OprSeq from the JobAsmbl table - no problem

I tried to use a condition in the Data Directive where number of rows in designed query >= 1 but the query editor there does not lend itself for this complex of a query. I do have a BAQ that works flawlessly. I am hopelessly lost trying to convert the SQL from the working BAQ into Linq so I am searching for some code to invoke my BAQ and pass into it the JobNum from the ttJobOper table in the BPM and check to see if it returned any rows.

Can anyone share some code on calling a dynamic query from BPM?

Bernie.

2

Hi Bernie,
The easiest thing here is to use the custom code condition to do your check using LINQ. What does your SQL look like?

3 
select 
	[JobOper].[JobNum] as [JobOper_JobNum],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	[JobOper].[DueDate] as [JobOper_DueDate],
	[LinkedJobOper].[JobNum] as [LinkedJobOper_JobNum],
	[LinkedJobOper].[OprSeq] as [LinkedJobOper_OprSeq],
	[LinkedJobOper].[DueDate] as [LinkedJobOper_DueDate]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on 
	JobHead.Company = JobAsmbl.Company
And
	JobHead.JobNum = JobAsmbl.JobNum

inner join Erp.JobOper as JobOper on 
	JobAsmbl.Company = JobOper.Company
And
	JobAsmbl.JobNum = JobOper.JobNum
And
	JobAsmbl.AssemblySeq = JobOper.AssemblySeq
And
	JobAsmbl.FinalOpr = JobOper.OprSeq

inner join Erp.JobHead as LinkedJob on 
	JobHead.Company = LinkedJob.Company
And
	JobHead.JobCode = LinkedJob.JobNum

inner join Erp.JobAsmbl as LinkedJobAsmbl on 
	LinkedJob.Company = LinkedJobAsmbl.Company
And
	LinkedJob.JobNum = LinkedJobAsmbl.JobNum

inner join Erp.JobOper as LinkedJobOper on 
	LinkedJobAsmbl.Company = LinkedJobOper.Company
And
	LinkedJobAsmbl.JobNum = LinkedJobOper.JobNum
And
	LinkedJobAsmbl.AssemblySeq = LinkedJobOper.AssemblySeq
And
	LinkedJobAsmbl.FinalOpr = LinkedJobOper.OprSeq
 and ( not LinkedJobOper.DueDate = JobOper.DueDate  )

 where (JobHead.JobNum = ttJobOper.JobNum)
4

Please excuse any odd formatting from the copy-paste:

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

if (tQuery != null)
{		
Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("BAQ");		
if (dsQuery != null)										
{													
Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);								dsBAQ.ExecutionParameter[0].ParameterID = "Key1";	
dsBAQ.ExecutionParameter[0].IsEmpty = false;
dsBAQ.ExecutionParameter[0].ParameterValue =inputstring;					
DataSet results = tQuery.Execute(dsQuery, dsBAQ); 
if (results != null && results.Tables.Count > 0 && results.Tables[0].Rows.Count > 0)
{
DataRow row = results.Tables[0].Rows[0];					
row["RowMod"] = "U";
…										
try									
{
				tQuery.Update(dsQuery, results);
			}												
catch (Exception e)										
{													
string emsg2 = "usefule info " + e.Message;							Ice.Diagnostics.Log.WriteEntry(emsg2);							
}											
}												
dsBAQ = null;										
}
dsQuery = null;											
tQuery.Dispose();
}
5 Likes
5

Thanks - got it working

6

Thank you for you code example.

7

Bumping an old topic - but has anyone run into issues with this when your database is on a different server? This works flawlessly on DEV, with DB and Appserver on the same VM, but on PROD the transaction is aborted and the inner exception includes

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool

The most seemingly relevant info I can find appears to be this:

Although it doesn’t specify my Windows Server 2016 version.

My IPSEC guy isn’t thrilled, mostly because he’s not familiar with it. I’m not thrilled because the solution works like a charm on DEV and I don’t see a lot of people having this problem on PROD and not DEV, although it does seem to make sense.

The business case is a forecasting implementiation to improve our ability to buy in components but sell in finished goods that we manufacture from components, without forecasting finished goods and having a flood of unfirm jobs clogging our system. When we get it working I’ll post it on this site.

1 Like
8

Coincidentally I ran into this yesterday, and while it’s hard to prove, I’m almost certain this error comes up when the query runs repeatedly too quickly. In my case I found it happened when used within a BPM that fired each time for EVERY changed line instead of once for the set of changes.

Backing out and finding a different trigger to use solved the issue for me.

1 Like