Kinetic Question: Is there a way to execute custom SQL code?

Kinetic ERP
, , , ,
1

Hello!

As the post title says, I’m wondering: is there a way within Kinetic to execute custom SQL code? I know that within functions, you can define and run custom C# code - but we’re looking for a way to specifically execute custom SQL code. Does anyone perhaps have any knowledge to share on that?

For some context:
We have a frequent issue with our job closing process that results in the lovely error we see oh so often:

image
image463×156 6.4 KB

This presumably happens because we need to manually modify the Serial Numbers since we’re frequently reworking the same numbers into different part numbers / statuses. There usually are no numbers to assign in Serial Number Assignment, or if there are - it does not accurately reflect in that error message either.

The only solution we have is to very frequently request Data Fixes from EpicCare, and I’m not joking when I say I’ve had to close hundreds of jobs this way. I’ve asked what changes are being made ‘behind-the-scenes’ with these data fixes, and I was given the following response:

This program will update the JobHead record and set closeddate and jobcompletiondate to the value(s) provided as parameter(s). It will also update the JobHead record
and set jobclosed to TRUE and jobcomplete to TRUE.

The program selects jobhead records where jobhead.jobclosed = 0 based on company and jobnum provided as parameters to the fix.

No transactions are created.

So… definitely doable with SQL, right? We ideally are trying to just make a function within Kinetic that will run the custom SQL code to close whatever job we’re inputting, as a last-ditch effort as opposed to waiting for a data fix every time. Any and all help is appreciated!

2

Why not just use the function to update those fields?
Doing anything to SQL is a huge red flag and this scenario does not look like a justification to use it.

3 Likes
3

I won’t lie… I personally just don’t have much any experience with C# and have been looking at this through a SQL angle only. Is updating those fields easily doable using the custom C# code within a function?

Edit: I should mention that we’ve tried to update these fields using an Updateable BAQ and, if I recall correctly, it prevented us from doing so. The only way we’ve been able to in the past is through SQL code (we did this just once, Epicor, I promise!) and then a bunch of the Data Fixes.

4

Oh yeah, super doable.
You would have to enable Read/Write and updateable on the table.
Then you should be able to directly update the record without using the BO (which is a good use-case in this instance, otherwise I would advocate for using the BO).

image
image1066×625 28.1 KB

image
image1066×625 22.1 KB

I have a meeting to be at in a minute, but can help you write out the C# after if you need help with that. (Or someone else can if they happen to see this)

3 Likes
5

Here ya go: EpiUsers.efxb (2.0 KB)

Give it a job–it closes ze job.

image
image1066×681 26 KB

try
{
  var jobRecord = (
    from jh in Db.JobHead
    where jh.Company == Session.CompanyID &&
          jh.JobNum == this.JobNum
    select jh).FirstOrDefault();
    
  if( jobRecord != null )
  {
    jobRecord.ClosedDate = DateTime.Today;
    jobRecord.JobCompletionDate = DateTime.Today;
    jobRecord.JobClosed = true;
    jobRecord.JobComplete = true;
    
    Db.SaveChanges();
  }
  
  Success = true;
}
catch(Exception e)
{
  ErrorMsg = e.Message.ToString();
  Success = false;
}
5 Likes
6

Outside of the easy (for me) code-around-your-problems, I’m curious why you’re closing jobs without a serial number when it seems to be set up to require them. If you assign the serial numbers, then I imagine the jobs would close just fine.

1 Like
7

While I can’t give a 100% definite answer as this is an issue that I kind of… joined into, this issue seems to only occur with our rework jobs, where we don’t have ‘Serial Number Assignment’ as part of the process.

  1. Problematic serial numbers are identified where they need to be reworked from Part A (SN Status: Inventory) to Part B (SN Status: Consumed).
  2. We then create a rework job for Part B
  3. We issue the material (Part A) using Issue Material, and then (this is what causes issue, or so EpicCare agents have implied):
  4. We have to use the Serial Number menu to manually change the Serial Number Status of Part B from Consumed to WIP, as well as change the Job field to the JobNum. We also change Part A from SN Status WIP (after being issued to the job) to Consumed.
  5. We use Serial Matching to match the material serial number with the parent one.
  6. Then we use Time Entry, and that should be it (with auto receive checked on the job level, we don’t use job receipt to inventory)
    When we try to close the job, we get met with that serial number assignment error. If we use Serial Number Assignment, it gives us no SNs to pull (and we need not create any).

This process, outside of not being a closeable job, does produce serial numbers with the proper SN Statuses, as well as a proper transaction trail.

1 Like