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
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
@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?
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
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
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
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?
Have you considered putting the sales order on hold? I believe that stops shipment entry.
2 Likes
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
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.
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.
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
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
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.
Thanks Jonathan. Google doesnāt have very many results explaining this method!
Thanks Kristine. Would you be willing to share this BPM?
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.