Data Directive BPM (Standard) - Call BAQ

Epicor ERP 10
1

Hi

I’ve been using this code on UI customisations with great success:

DynamicQueryAdapter dqa_JobDashboardAvailableLots = new DynamicQueryAdapter(oTrans);

            dqa_JobDashboardAvailableLots.BOConnect();

            QueryExecutionDataSet qedsBAQ2 = dqa_JobDashboardAvailableLots.GetQueryExecutionParametersByID("JobDashboardAvailableLots");

            qedsBAQ2.ExecutionParameter.Clear();

            qedsBAQ2.ExecutionParameter.AddExecutionParameterRow("PartNum", partNum, "string", false, Guid.NewGuid( ), "A" );

            dqa_JobDashboardAvailableLots.ExecuteByID("JobDashboardAvailableLots", qedsBAQ2 );

            grdAvailableLots.DataSource = dqa_JobDashboardAvailableLots.QueryResults.Tables["Results"];

Question – can I do the same in a standard data directive? Looking at the Usings and References, couldn’t see DynamicQueryAdapter in the list.

Many Thanks

Mark

2

Not exactly the same but yes you can run BAQ’s in a directive… However since you are already in the server / back end seems like writing a quick LINQ query may save you some time

1 Like
3

Thanks Jose – you know that you’re amazing and love to demonstrate to people with code examples…… J

This is my BAQ:

select

                [JobHead].[JobNum] as [JobHead_JobNum],

                [JobHead].[PartNum] as [JobHead_PartNum],

                [JobHead].[ProdQty] as [JobHead_ProdQty],

                [JobHead].[QtyCompleted] as [JobHead_QtyCompleted],

                (JobHead.QtyCompleted - JobHead.ProdQty) as [Calculated_QtyVariance],

                ((case when QtyVariance = 0 then '' else (case when QtyVariance > 0 then 'Over' else 'Under'  end)  end)) as [Calculated_OverUnder],

                ((QtyVariance / JobHead.ProdQty) * 100) as [Calculated_PercentVariance]

from Erp.JobHead as JobHead

inner join Erp.Part as Part on

                JobHead.Company = Part.Company

                and JobHead.PartNum = Part.PartNum

                and ( Part.MtlAnalysisCode = @Dept  )

where (JobHead.ReqDueDate = @ProdDate  and not JobHead.JobNum like '"K"')

Can you give me a starter for 10 on how to call this in a data directive BPM (standard), and I then plan to format as an HTML table and send via email. I’ve got code for converting from DataTable to HTML, so if the output of LINQ statement was DataTable that would be ideal…!

Many Thanks

Mark

4

Not specific to your usage, but the names of the tables/fields have been changed to protect the innocent.

var Result = from D in Db.Device   //this just gets results from the temp table parttrans which is the result of this BPM
                                     join P in Db.SysPrinter
                                     on new { D.Company, D.PrinterID }   //link these
                                     equals new { P.Company, P.PrinterID }  // to these
				 where D.WorkStationID == Session.WorkstationID
					select P.NetworkPath;
prntr = Result.FirstOrDefault();
}
1 Like
5

Or LINQ like Jose says (maybe this is valid, untested):

Db.JobHead.Join(
    Db.Part,
    job => job.PartNum,
    part => part.PartNum,
    (job, part) => new { Job = job, Part = part }
)
6

Do you not want to stick this on SSRS and email an Excel sheet instead?
What’s the end goal?

7

I’ve got an interactive dashboard where I’ve built code to be able to Report Quantity and Receipt to Inventory in one button press. Does all the checks and balances on what’s permissible in terms of quantities etc.

This was an intention to add 4 buttons, 1 per department. Once they’ve finished producing the plan for the day, they press the button. I want to work out the plan adherence and send that in an email to production management team, and also customer services so they can see anything that was over/under produced and let customers know. I’ve got a basic email working using the DD bpm, triggered by writing an entry into UD05 from screen customisation. Just need to get that basic data from BAQ into an HTML table.

The data is presented in other places for meetings etc, this is just the early sending of info to relevant people.