BPM Code Failure - Need extra eyes

CasterConcepts · October 26, 2020, 1:37pm

Had my code working, creating POs for any jobs cut using Order Job Wizard but realized I need to put in a validation to make sure we’re not creating POs more than once. My code fails with an index out of bounds message, and I have no idea where it’s coming from. Hoping a second set of eyes can spot something obvious or might even offer a better solution, I’m a noob with C#. What I’m looking to do, enumerate through jobs, find related POs, if a PO is found, stop, if no PO exists, create one and add lines.

Erp.Contracts.POSvcContract hPOApproveHandle = null;

if(hPOApproveHandle == null)
{
hPOApproveHandle = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.POSvcContract>(Db);
}
      
foreach (var ttJWOrderRel_Row in (from row in ttJWOrderRel select row))
{

  var JobProd = (from jp in Db.JobProd
                where jp.Company == Session.CompanyID
                && jp.OrderNum == ttJWOrderRel_Row.OrderNum
                && jp.OrderLine == ttJWOrderRel_Row.OrderLine
                && jp.OrderRelNum == ttJWOrderRel_Row.OrderRelNum
                select new {jp.Company, jp.OrderNum, jp.OrderLine, jp.OrderRelNum, jp.JobNum}).ToArray();
                
if (JobProd != null)
{

    foreach(var prod in JobProd)
    {
          
       string JobNum = prod.JobNum;
          
       var JobOper = (from jo in Db.JobOper
                    where jo.Company == Session.CompanyID
                    && jo.JobNum == JobNum
                    && jo.SubContract == true
                    && jo.VendorNum == 497
                    select new {jo.JobNum, jo.AssemblySeq, jo.OprSeq, jo.PartNum}).ToArray();
                    
if (JobOper != null)
{


      foreach(var oper in JobOper)
      {
      
      string Job = oper.JobNum;
      int Asm = oper.AssemblySeq;
      int Opr = oper.OprSeq;
      
      var PORel = (from por in Db.PORel
          where por.Company == Session.CompanyID
          && por.JobNum == Job
          select new {por.PONum}).ToArray();

      if (PORel != null) 
      {
      
        foreach(var PO in PORel)
        {
        
        POnumber = PO.PONum;
      
        this.PublishInfoMessage("PO already exists!" + "\n" + "\nPO: " + POnumber, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
        }
        
      if (PORel == null)
        {
        
      var POtable = new Erp.Tablesets.POTableset();
      
      hPOApproveHandle.GetNewPOHeader(ref POtable);
      var poHeader = POtable.POHeader[0];
            
      poHeader.OrderDate = DateTime.Today;
      
      hPOApproveHandle.ChangeVendor("REACT1", ref POtable);
      
      hPOApproveHandle.Update(ref POtable);
      
      intPONum = poHeader.PONum;
      
        foreach(var op in JobOper)
        {
        
        hPOApproveHandle.GetNewPODetail(ref POtable, intPONum);
        hPOApproveHandle.ChangeDetailTranType("PUR-SUB", ref POtable);
        hPOApproveHandle.ChangeDetailJobNum(Job, ref POtable);
        hPOApproveHandle.ChangeDetailAssemblySeq(Asm, ref POtable);
        hPOApproveHandle.ChangeDetailJobSeq(Opr, ref POtable);
        hPOApproveHandle.CheckBeforeUpdate(out str2, out str3, out str4, "PODetail", POtable);
        hPOApproveHandle.Update(ref POtable);
        
        }
        }
        }
        }
        }
        }
        }
        
        if(POtable != null)
        {
        
        POtable.POHeader[0].RowMod= "U";
  
        hPOApproveHandle.ValidateAcctForGLControl(intPONum, out str1);
  
        hPOApproveHandle.ChangeApproveSwitch(true,out ViolationMsg, ref POtable);
      
        hPOApproveHandle.CheckBeforeUpdate(out str2, out str3, out str4, "POHeader", POtable);
  
        hPOApproveHandle.Update(ref POtable);
        
        this.PublishInfoMessage("PO: " + intPONum, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");

        }

}

Doug.C · October 26, 2020, 2:54pm

I would change:

 var PORel = (from por in Db.PORel
          where por.Company == Session.CompanyID
          && por.JobNum == Job
          select new {por.PONum}).ToArray();

to

 var PORel = (from por in Db.PORel
          where por.Company == Session.CompanyID
          && por.JobNum == Job
          select new {por.PONum}).FirstOrDefault();

Otherwise I don’t think PORel will ever be null.

timshuwy · October 26, 2020, 5:23pm

My thought is that you are looping through many records to get to the actual record you need… each loop is a separate query. This could be combined into one joined query.
loop 1: OrderRel to find related:
…Loop 2: JobProd - to find related:
…loop 3: JobOper(s) - to find related:
…loop 4: PORel(s) - to find related:
…loop 5: POHead
The above will at a minimum do 5 separate queries. BUT if there is more than one jobprod record, it will continue to repeat the lower queries… more than one operation? More PORel queries… This constant hammering of the database will make this slow.

Instead, you could create one JOINED query that retrieved the entire set of data you want with one database query.

CasterConcepts · October 26, 2020, 5:26pm

I was afraid I was going to get this answer… Guess I’m going to need to study up a little more and see if I can make this happen with a joined query.

CasterConcepts · October 26, 2020, 6:28pm

Okay, I’ve managed to eliminate the half dozen loops and use a joined query to get my job data, but now I’m running into another issue when trying to create the PO lines. Any clues here?

Erp.Contracts.POSvcContract hPOApproveHandle = null;

if(hPOApproveHandle == null)
{
hPOApproveHandle = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.POSvcContract>(Db);
}
      
foreach (var ttJWOrderRel_Row in (from row in ttJWOrderRel select row))
{

  var query = (from jp in Db.JobProd
              join jo in Db.JobOper 
              on new {jp.Company, jp.JobNum} equals new {jo.Company, jo.JobNum} 
              
              join por in Db.PORel
              on new {jo.Company, jo.JobNum} equals new {por.Company, por.JobNum} into porj from por in porj.DefaultIfEmpty()
              
              where 
                   jp.Company == Session.CompanyID
                && jp.OrderNum == ttJWOrderRel_Row.OrderNum
                && jp.OrderLine == ttJWOrderRel_Row.OrderLine
                && jp.OrderRelNum == ttJWOrderRel_Row.OrderRelNum
                && jo.JobNum == jp.JobNum
                && jo.SubContract == true
                && jo.VendorNum == 497
                && por.PONum == null
                
                select new{jo.JobNum, jo.AssemblySeq, jo.OprSeq}).ToArray();
                
    
if(query != null)
{

      var POtable = new Erp.Tablesets.POTableset();
      
      hPOApproveHandle.GetNewPOHeader(ref POtable);
      var poHeader = POtable.POHeader[0];
            
      poHeader.OrderDate = DateTime.Today;
      
      hPOApproveHandle.ChangeVendor("REACT1", ref POtable);
      
      hPOApproveHandle.Update(ref POtable);
      
      intPONum = poHeader.PONum;
      
      foreach(var row in query)
      {
      
        Job = row.JobNum;
        Asm = row.AssemblySeq;
        Opr = row.OprSeq;
      
        hPOApproveHandle.GetNewPODetail(ref POtable, intPONum);
        hPOApproveHandle.ChangeDetailTranType("PUR-SUB", ref POtable);
        hPOApproveHandle.ChangeDetailJobNum(Job, ref POtable);
        hPOApproveHandle.ChangeDetailAssemblySeq(Asm, ref POtable);
        hPOApproveHandle.ChangeDetailJobSeq(Opr, ref POtable);
        hPOApproveHandle.CheckBeforeUpdate(out str2, out str3, out str4, "PODetail", POtable);
        hPOApproveHandle.Update(ref POtable);
        
        this.PublishInfoMessage("PO: " + intPONum, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
      }
                
}
}

Doug.C · October 26, 2020, 6:56pm

When you’re assigning var query = (...).ToArray(); you will get an object back. This object will be at minimum query.Length = 0, it will never be null.

If your query is supposed to return 1 row then I’d use FirstOrDefault() instead. Otherwise use DefaultIfEmpty()

Or change your conditional statement to:

if(query.Length != 0)

from

if(query != null)

CasterConcepts · October 26, 2020, 7:22pm

I appreciate the explanation @Doug.C, I did change the IF statement to use (query.Length != 0), but I think my issue is that I’m not backing out of the loop, before creating the PO, and using a global variable for query, then doing a loop to create the lines.

I’m having trouble initializing the query variable at the beginning of my code

CasterConcepts · October 26, 2020, 10:03pm

Got it! Rather than trying to use a global variable, I just nulled the POtable from the start and used an IF statement to create the PO if it didn’t exist, making it not null, them adding the lines in my loop. Approving the PO at the end. Seems to be working perfect now.

Thank you @timshuwy and @Doug.C for pointing me in the right directions!

Erp.Contracts.POSvcContract hPOApproveHandle = null;

if(hPOApproveHandle == null)
{
hPOApproveHandle = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.POSvcContract>(Db);
}

POtable = null;

foreach (var ttJWOrderRel_Row in (from row in ttJWOrderRel select row))
{

 var query = (from jp in Db.JobProd
              join jo in Db.JobOper 
              on new {jp.Company, jp.JobNum} equals new {jo.Company, jo.JobNum} 
              
              join por in Db.PORel
              on new {jo.Company, jo.JobNum} equals new {por.Company, por.JobNum} into porj from por in porj.DefaultIfEmpty()
              
              where 
                   jp.Company == Session.CompanyID
                && jp.OrderNum == ttJWOrderRel_Row.OrderNum
                && jp.OrderLine == ttJWOrderRel_Row.OrderLine
                && jp.OrderRelNum == ttJWOrderRel_Row.OrderRelNum
                && jo.JobNum == jp.JobNum
                && jo.SubContract == true
                && jo.VendorNum == 497
                && por.PONum == null
                
                select new{jo.JobNum, jo.AssemblySeq, jo.OprSeq}).ToArray();         
    
if(query.Length != 0)
{
      
      foreach(var row in query)
      {
      
           if(POtable == null)
            {
            POtable = new Erp.Tablesets.POTableset();
            hPOApproveHandle.GetNewPOHeader(ref POtable);
            var poHeader = POtable.POHeader[0];
            poHeader.OrderDate = DateTime.Today;
            hPOApproveHandle.ChangeVendor("REACT1", ref POtable);
            hPOApproveHandle.Update(ref POtable);
            intPONum = poHeader.PONum;
            }
            
        Job = row.JobNum;
        Asm = row.AssemblySeq;
        Opr = row.OprSeq;        
      
        hPOApproveHandle.GetNewPODetail(ref POtable, intPONum);
        hPOApproveHandle.ChangeDetailTranType("PUR-SUB", ref POtable);
        hPOApproveHandle.ChangeDetailJobNum(Job, ref POtable);
        hPOApproveHandle.ChangeDetailAssemblySeq(Asm, ref POtable);
        hPOApproveHandle.ChangeDetailJobSeq(Opr, ref POtable);
        hPOApproveHandle.CheckBeforeUpdate(out str2, out str3, out str4, "PODetail", POtable);
        hPOApproveHandle.Update(ref POtable);
        
        //this.PublishInfoMessage("PO: " + intPONum + "\nJob: " + Job, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
      
      }
   }
 }
   
   if (POtable != null) 
   {

        POtable.POHeader[0].RowMod= "U";
  
        hPOApproveHandle.ValidateAcctForGLControl(intPONum, out str1);
  
        hPOApproveHandle.ChangeApproveSwitch(true,out ViolationMsg, ref POtable);
      
        hPOApproveHandle.CheckBeforeUpdate(out str2, out str3, out str4, "POHeader", POtable);
  
        hPOApproveHandle.Update(ref POtable);
        
        //this.PublishInfoMessage("PO: " + intPONum, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");
}

timshuwy · October 27, 2020, 2:44am

Much better… but depending on where you have this code running, you MIGHT have TWO temporary records in the ttJWOrderRel table… one with RowMod = “” and the other with RowMod = “U”. You probably only want the updated one. Right now you are running it for every row found.
You might check to make sure that you only run this one time.

CasterConcepts · October 27, 2020, 12:24pm

This is on a post-processing method directive, OrderJobWiz.CreateJobs. I think I’m only getting the rows I need, if I display a message to show the row details after my query I’m only picking up the specific lines I’m working with. So far so good, we’re going to test in production today.

Topic		Replies	Views
BPM Creating PO Errors On Second Record Epicor ERP 10 create-po-bpm	3	670	August 24, 2020
Auto-Approve PO using BPM Epicor ERP 10 e10 , bpm	34	4340	October 14, 2020
Foreach criteria - BPM Code Epicor ERP 10 bpm , bpm-code	13	2420	October 22, 2020
Method Directive on Capable To Promise - Intercompany PO Automation Epicor ERP 10 e10 , bpm-code	1	921	October 19, 2020
BPM More Than One Record Error - Help Needed Epicor ERP 10 e10 , bpm	21	5774	December 21, 2020

BPM Code Failure - Need extra eyes

Related Topics