Need to Understand "Record Locking" in Epicor

Kinetic ERP
,
1

Hello.
I need to understand how to programmatically test for a record lock in Epicor ERP. For example, we would like to keep users from accessing a Customer Shipment when its originating Order is being edited. Secondly, we would also like disallow users from editing the same order simultaneously. Any info or reference links would be greatly appreciated.

1 Like
2

Tony… that concept veers away from the entire architecture of how Epicor ERP/Kinetic is designed. Our system is totally designed on the concept of “Optimistic File Locking” and so we don’t lock a record while it is being edited… this of course results in those times when the user gets the famous “record has been modified by another user” error.
You an I both came out of the old world programming with record locks. Personally, I have worked in both environments over the years, and while I got irritated at the new error message, I also realize that the former method was more irritating when all I wanted to do was “look”. Since all our screens are always in “edit mode” there is no way to know if the user is just there to view, or if they plan on editing. Back in the olden days, we used to have an “edit” button which locked the record. Now in the new days, we dont have those screen lockouts, nor do we get into “Deadly Embraces” (some of the new-world programmers probably don’t even know about this problem).
Anyway, I don’t believe that there is an easy answer to your request. Our systems don’t check for locks until they are being written. That is the design.
You can read more about this database - Optimistic vs. Pessimistic locking - Stack Overflow.

5 Likes
3

@timshuwy I was taught that in those rare cases where I think I have to write to or even. read from the DB I should use the LockHint.NoLock filter when initially populating my dataset. Is that not the purpose of that check?

Or am I (again) showing my ignorance and playing with sharp tools I really don’t understand?

4

yes, you should do that, but doing the lockhint thing is totally different then the pessimistic locking that we all used to do. Back in the olden days, we would lock a record when someone called a part or Order to a screen, and we would not unlock it until the person cleared the record from the screen. This had the net effect of popular parts or customers being locked for long periods of time. you could also cause things like MRP or Invoice runs to hang because someone left a screen open.

1 Like
5

NoLock hint can sometimes lead to issues because the no lock hint shows you uncommitted dirty rows and transactions so keep that in mind

The data you return on the query may not be there when you run the query again if it was a dirty row and the record didn’t get committed

1 Like
6

Clearly, you have a painful business problem here. The closest thing in ERP/Kinetic that exists are the various posting groups in finance areas or Engineering WB. These are application “locks” vs database locks, but they come with their issues too. Often a group gets locked and the person who locked it is not available to unlock it.

You might be able to simulate it with the BPM Hold functionality. The challenge will be to reliably remove the hold.

1 Like
7

Thanks everyone for your experience and insights. Since it looks like code is not the answer, I’m thinking of suggesting a different workflow to handle such a situation.

Couldn’t we just cancel the release to keep logistics from processing the shipment?

8

Have you considered putting the sales order on hold? I believe that stops shipment entry.

2 Likes
9

So… to make sure that we are answering the right question (see XY Problem) , lets go back to your original problem… what is the business problem you are attempting to solve? Are you having a problem where the shipping department is shipping things before the entire order is entered (I have seen this before)? or is this some other issue going on?

3 Likes
10

I can definitely appreciate the XY problem, but I also think that an end-user presenting ‘Y’ shows that they have already invested time and effort into solving the problem. In my case, ‘Y’ was a dry well! I’m pretty sure I put some decent ‘X’ info, but I’m also trying not to cloud the post with unnecessary details. In fact, I may intentionally try to simplify the initial post by asking very basic questions up front, then elaborating as requested by those who respond.

So, that being said, we do in fact get orders from our customers, and 15 minutes later they call back to make changes. By that time, logistics has already started picking the product and are getting ready to seal the box. Customer Service has asked if it was possible to keep shipping from completing that order. We’re presently looking info the ideas posted earlier today, and I’m hopeful that we can come up with a relatively simple solution.

11

I stumbled across something, and thought it worthwhile to ask about. While building the CustShip Method Directive, I mistakenly typed “OrderHed” instead of my variable “OrdHed” and hit Ctrl-Space to invoke the helper. To my astonishment, I saw that “FindFirstBySysRowIDWithUpdLock” was a valid option. Has anyone come across this before? This looks a little too good to be true, so I’m not going to celebrate just yet.

12

Tony…
One thing that I had one customer do to help solve the problem you relate above… They made a BPM on Sales Order entry that checked to see if there were any open Packslips (or Pick Tickets if using fulfillment workbench)… if there was one, then they throw the error right away because the order is already being packed… changing the order now is too late, and they need to call the shipping dept to “Stop the shipment”. But if there is no packslip, then it is ok to change the order.

1 Like
13

This is exactly what we do. We have a BPM that checks if the order has any lines in picking and if so a message is displayed to sales to contact shipping.

1 Like
14

This is just a default entity framework query, part of all entities. It is basically a method which takes a SysRowID, retrieves that record from the database and locks it until released.
This means if someone else tries to update that OrderHed record, it will wait until you release it, it will wait a predefined amount of time before timing out and throwing an error for that separate process/user.

But this only works at the record level, you would still be able to do anything else as long as it doesn’t involve a change to the OrderHed record.

15

Thanks Jonathan. Google doesn’t have very many results explaining this method!

16

Thanks Kristine. Would you be willing to share this BPM?

17

No answer, I’m just reminded of a site that was having issues with unwanted activity related to sales order entry… and that they ended up adding several modifications.

Something like this (off the top of my head…)

Order Entry Form

  • added a new custom checkbox “Entry Complete” to their order entry screen, default false.
  • on exiting order entry, if false, then prompt user to verify/update its state
  • if an existing order is edited, then reset “Entry Complete” to false

Reports…

  • Do not to display any order lines where “Entry Complete” is false
  • e.g. Sales Order Picklist

BPMs

  • New transactions e.g. Mfg, purchasing or shipping, lookup order status and alert as needed

MRP and Generate PO Suggestions.

  • I believe they ended up scheduling MRP “Net Change”
  • overnight and lunchtime - to help keep suggestions synced with order changes.

Also reminds me of an issue with some sales reps who used to go directly to order entry as a “worksheet” instead of using quotes… which would result in some very “interesting” records/activity downstream. Sales did finally come around to using quotes for estimating but… took a while.