BPM: Can this be combined into one LINQ query?

Epicor ERP 10
,
1

I can’t help thinking I could make it more efficient by combing the code below into one LINQ query. I don’t know it well enough to know for sure. I can get a BAQ to do this in one, but I’ve tried several things in code but everything errors out. As you can gather I’m working on a BPM for a custom Aging Hold process, the stock won’t work for us and with the number of customers we have I want to have the code as efficient as possible.

Any help appreciated

///#1 - Get List of customers with AgingLimits not currently on AgingHold 
var result = (from c in Db.Customer.With(LockHint.UpdLock)
              join ad in Db.AgingCredit.With(LockHint.NoLock) on new { c.Company, c.AgingCreditCode } equals new { ad.Company, ad.AgingCreditCode }
              where c.Company == Session.CompanyID && !c.AgingCreditHold
              select new { c, ad.AgingDays }).ToArray(); 

foreach(var cust in result)
{

///#2 - Get oldest Open AR Invoice for the Customer, ie:  (max(InvcHead.DueDate)
    var maxDue = (from ar in Db.InvcHead.With(LockHint.NoLock)
                  where cust.c.Company == ar.Company && ar.OpenInvoice && ar.Posted
                  && (ar.InvoiceType == "MIS" || ar.InvoiceType == "SHP")
                  && cust.c.CustNum == ar.CustNum
                  select ar.DueDate).OrderBy(x => x).ThenByDescending(d => d).Min();

///#3 - Count the days from Today to maxDue, ie: datediff(day, InvcHead.DueDate, getdate()) )
int aging = ((TimeSpan)(DateTime.Today - maxDue)).Days;


///#4 - Check IF AgingDays > days(maxDue) 
///#4a   IF ture:  Set c.AgingCreditHold to TRUE and (then loop through their open orders **not linked to Jobs or POs** to OH.CreditOverride = FALSE)
2

Why not run the BAQ with a dynamic query? I’ve found that to be very reliable. Once you get the template down for how dynamic query works, it’s very similar every time you use it.

1 Like
3

Not a bad idea I guess, I’ve not yet setup a BPM to call a BAQ before so interesting idea.