Creating my first function

[tmat (Tracy Smith)]

Good Morning. I am trying to create my first function which I want to schedule to run through all records in a BAQ and any results returned should then be checked out to a specific Engineering Work Group, however I am getting an error when I try and run the function.

This is the error:
Exception executing library ‘PartRevAutoCheckOut’ function ‘AutoCheckOut’:
System.InvalidOperationException: Top level subquery is not defined in ‘Parts-RevAutoCheckOut’ query
at Ice.Services.BO.DynamicQuerySvc.ExecuteInternal(DynamicQueryTableset queryDefinition, QueryExecutionTableset executionParams, Boolean updateExtSecurity) in C:_releases\ICE\ICE4.2.200.12\Source\Server\Services\BO\DynamicQuery\DynamicQuery.Impl.cs:line 384
at Ice.Services.BO.DynamicQuerySvc.ExecuteByID(String queryID, QueryExecutionTableset executionParams) in C:_releases\ICE\ICE4.2.200.12\Source\Server\Services\BO\DynamicQuery\DynamicQuery.cs:line 103
at Ice.Services.BO.DynamicQuerySvcFacade.ExecuteByID(String queryID, QueryExecutionTableset executionParams) in C:_releases\ICE\ICE4.2.200.12\Source\Server\Services\BO\DynamicQuery\DynamicQuerySvcFacade.cs:line 145
at EFx.PartRevAutoCheckOut.Implementation.AutoCheckOutImpl.<>c__DisplayClass6_0.<A002_CustomCodeAction>b__0(DynamicQuerySvcContract dq) in AutoCheckOut.cs:line 75
at Epicor.Functions.FunctionBase3.CallService[TService](Action1 action) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.Plugins.cs:line 63
at EFx.PartRevAutoCheckOut.Implementation.AutoCheckOutImpl.A002_CustomCodeAction() in AutoCheckOut.cs:line 70
at EFx.PartRevAutoCheckOut.Implementation.AutoCheckOutImpl.RunStep(Int32 workflowStep) in AutoCheckOut.cs:line 41
at Epicor.Functions.FunctionBase3.Run() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 92 at Epicor.Functions.FunctionBase3.Run(TInput input) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.cs:line 75
at EFx.PartRevAutoCheckOut.Implementation.AutoCheckOutImpl.AdapterRun(Object input) in AutoCheckOut.adapter.cs:line 20
at Epicor.Functions.FunctionBase`3.Epicor.Functions.IFunctionAdapter.Run(Object input) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Lib\Epicor.Functions.Core\FunctionBase.Adapter.cs:line 18
at Ice.Internal.Task.ScheduledFunction.ExecuteFunction.RunFunction(IFunctionAdapter functionAdapter, Object parameters) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Task\ScheduledFunction\ExecuteFunction.cs:line 128
at Ice.Internal.Task.ScheduledFunction.ExecuteFunction.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Task\ScheduledFunction\ExecuteFunction.cs:line 57

The BAQ has no parameters, and this is the function - it uses the custom code below. Is anyone able to advise what I may have missed? Or point me in the direction of some technical guides to work through? Thanks

 // Checks out all Part Revisions that have not been approved within the last 6 months (183 days) or invoiced in the system or within the same date range

Ice.Diagnostics.Log.WriteEntry("zBPM:  Function AutoCheckOutRev");

string baqName = "Parts-RevAutoCheckOut";  // BAQ Name

CallService<DynamicQuerySvcContract>(dq =>
{

  Ice.Tablesets.QueryExecutionTableset qets = new Ice.Tablesets.QueryExecutionTableset(); 
  
  DataSet ds = dq.ExecuteByID(baqName, qets);

Ice.Diagnostics.Log.WriteEntry("zBPM:  Function AutoCheckOut BAQ executed");

using (var txScope = IceContext.CreateDefaultTransactionScope())
{
  //Erp.Contracts.EngWorkBenchSvcContract engWB = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.EngWorkBenchSvcContract>(this.Db, true);
  
    this.CallService<Erp.Contracts.EngWorkBenchSvcContract>(engWB =>
    {
  bool opPromptForPassword = false;

  if(ds != null)
  {
    if(ds.Tables.Count > 0)
    {    
      foreach(DataRow drRow in ds.Tables[0].Rows)
      {
    
        Ice.Diagnostics.Log.WriteEntry("zBPM:  Function AutoCheckOutRev - PartNum: " + drRow["Part_PartNum"].ToString());
        Ice.Diagnostics.Log.WriteEntry("zBPM:  Function AutoCheckOutRev - RevisionNum: " + drRow["PartRev_RevisionNum"].ToString());
        
        string ipGroupID = "Hold-NotShipped6m";
        string ipPartNum = drRow["Part_PartNum"].ToString();
        string ipRevisionNum = drRow["PartRev_RevisionNum"].ToString();
        string ipAltMethod = String.Empty;
        string ipProcessMfgID = "";
        System.DateTime ipAsOfDate = DateTime.Now;
        bool ipCompleteTree = false;
        bool ipValidPassword = false;
        bool ipReturn = true;
        bool ipGetDatasetForTree = true;
        bool ipUseMethodForParts = false;
        string ipAuditText = "BPM Auto Check Out";
        string opMessage = "";
        string opResultString = "";
        string opCheckedOutRevisionNum = "";
        string altMethodMsg = "";
        bool altMethodFlg = false;
        engWB.PromptForPassword(out opPromptForPassword);
        ipValidPassword = true;
        ipReturn = false;
        engWB.CheckOut(ipGroupID,ipPartNum,ipRevisionNum,ipAltMethod,ipProcessMfgID,ipAsOfDate,ipCompleteTree,ipValidPassword,ipReturn,ipGetDatasetForTree,ipUseMethodForParts,out opCheckedOutRevisionNum,out altMethodMsg,out altMethodFlg);
    
      }
    }
 
    txScope.Complete(); 
  }
});


}

});

[aarong (Aaron Gulley)]

Your BAQ is the issue here. Let’s have a look at this.

[tmat (Tracy Smith)]

Thanks Aaron. BAQ attached - it seems to run fine for me but you may need to review the filters.
Parts-RevAutoCheckOut.baq (60.9 KB)

[jwphillips (Jonathan Phillips)]

@tmat

System.InvalidOperationException: Top level subquery is not defined in ‘Parts-RevAutoCheckOut’ query

It looks like your BAQ does not have a subquery defined as TopLevel… but I don’t think a BAQ can be saved without a top level subquery…
Have you worked this out?

[tmat (Tracy Smith)]

Hi Jonathan. The BAQ runs fine (I attached it on my reply to Aaron) - this is theSubQueryList

[tmat (Tracy Smith)]

I was getting a different error when I tried to add a widget for the server log but sorted that. I have changed the BAQ to test for one with just a Top Level query but am still getting the same message. No problem running either the original or this basic version in the BAQ screen so is it something I am missing in the function?

[jwphillips (Jonathan Phillips)]

I was going to suggest making a slight change to your BAQ and saving (to see it there was some defect in the way it was saved before) but you did that already.

Have you tried calling a different BAQ? to verify whether it is the BAQ or the function that is the issue here…

[tmat (Tracy Smith)]

I will create a brand new BAQ in the morning and try that - Thank you.

[NateS (Nate Schoonover)]

@tmat

bool ipValidPassword = false;

I haven’t worked this in a function myself, but I do have a UBAQ with a BPM that checks part revs out. When I do this, I have been using the widgets. I do use a PromptForPassword method, and pass that value into ipValidPassword. My ipReturn is also set to false (not sure why). Also, my ipAltMethod is set to “” not String.Empty. I am not sure if any of this will help, but its worth a shot. Good luck!

[tmat (Tracy Smith)]

Thanks Nate. I decided to start right at the beginning and review the code step by step as well. First thing - how far am I getting with the function:

Custom Code with just log message - System Monitor shows complete but no message in Server Log

Remove Custom Code and use Log Message widget - System Monitor shows error

Wrote a brand new basic BAQ (not what I want but just to test) with PartRev table only and still get the same error about the BAQ:

Think I am going to have to give up and go back to scheduling BAQ exports and triggering from there with a method directive

[klincecum (Kevin Lincecum)]

I think you have something wrong with your system.

The one where it is just code, is that a custom code function, or a widget function with code?

[NateS (Nate Schoonover)]

The BAQ looks ok, it does have the top level in place, and when I removed the search word criteria I got good results from my test system. I suspect there is something in the way you made the function.

Backing up… I recently made my first functions. I would suggest to try to make a simpler function for your first one. Working with the engineering workbench has lots of little gotchyas to look out for.

Can you show us more about the way you setup the function? Like Kevin said, where is that custom code?

I am also curious about the eco group you are checking out to, and security levels for your user account.

Checkout parts will fail if the part is already checked out. There are likely some other reasons a checkout would fail.

Have you run a trace on the checkout process when you run it manually? Make sure to reproduce all the calls you see in the trace.

[tmat (Tracy Smith)]

@klincecum It was a widget with custom code.

I have attached the function too - it is basically just one widget with all the custom code

PartRevAutoCheckOut.efxb (20.7 KB)

I would have started with a simpler function but this request was new and I wanted to take advantage of the ‘Schedule Epicor Function’. Thanks

[klincecum (Kevin Lincecum)]

Make a pure code function in a new library with just that log message.

[tmat (Tracy Smith)]

Looks better but no message in Server Log so I guess something is wrong somewhere

[tmat (Tracy Smith)]

I just wanted to update on here that I now have this working. I am still having problems with server log messages displaying in Live and Test and have an open case with Epicor for that but I now have a function that will use a BAQ to identify which parts we want to check out and then a scheduled function to action it.

It really helped finding out that the Kinetic screen has a Mass Check Out option in the Engineering Workbench which we have never used before. The main help came from this forum post:

Mass Checkout Efx

So i am working on a application that will pull in all of the parent parts that a user will want to update a single mtl on ie pull as view as data on the parts. I have created a user interface for them to query and review the changes that they will make. Once that is done they check the check boxes they need to update ie pullas. see screenshot for UI. I am using a BAQ to feed the UI and a function in the background same baq. My issue is related to using the Mass Checkout BO in a function. …

And this is my code

// Checks out all Part Revisions that have not been approved within the last 6 months (183 days) or invoiced in the system or within the same date range


string baqName = "Parts-RevAutoCheckOut1";  // BAQ Name

CallService<DynamicQuerySvcContract>(dq =>
{
  Ice.Tablesets.QueryExecutionTableset qets = new Ice.Tablesets.QueryExecutionTableset(); 
  DataSet ds = dq.ExecuteByID(baqName, qets);
  
  if(ds != null)
  {
  
  if(ds.Tables.Count > 0)
  {
        
    this.CallService<Erp.Contracts.EngWorkBenchSvcContract>(engWBSVC =>
    {
      //declare tablesets
      //var engDS = new Erp.Tablesets.EngWorkBenchTableset();
      var engDSMass = new Erp.Tablesets.MassCheckoutTableset();
      
      try
      {
            
        foreach(DataRow drRow in ds.Tables[0].Rows)
        {
                    
          var massCheckoutTS = (Erp.Tablesets.MassCheckoutRow)engDSMass.MassCheckout.NewRow();
          massCheckoutTS.Company = this.BaqConstants.CurrentCompany;
          massCheckoutTS.CreateNewRev = false;
          massCheckoutTS.GroupID = "Hold-NotShipped6m";
          massCheckoutTS.PartNum = drRow["Part_PartNum"].ToString();
          massCheckoutTS.RevisionNum = drRow["PartRev_RevisionNum"].ToString();
          massCheckoutTS.SourcePartNum = drRow["Part_PartNum"].ToString();
          massCheckoutTS.SourceRevisionNum = drRow["PartRev_RevisionNum"].ToString();;
          massCheckoutTS.RevShortDesc = "Import Rev";
          massCheckoutTS.SysRowID = Guid.NewGuid();
          massCheckoutTS.RowMod = "A";
          
          engDSMass.MassCheckout.Add(massCheckoutTS);
          
        }
      
        engWBSVC.ProcessMassCheckout(ref engDSMass);
        
      }
        catch
        {
        }      
      
      });
    }
  }
});