Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amit.

Asked: February 16, 2021 - 6:09 pm UTC

Last updated: February 17, 2021 - 7:42 am UTC

Version: 12.0.1.2

Viewed 1000+ times

You Asked

Hi Chris and Connor,

I have a situation where warehouse operations are receiving some Purchase Orders which are pegged to Sales Order post receiving. Here one PO has various parts and there are multiple sales orders with multiple parts.

The whole logic is working fine when warehouse associates are not receiving Purchase Orders in parallel but when received in parallel the pegging cursor is pulling data for all sales orders in different sessions and it screws up quantities to be pegged in sales orders.

Could you suggest to me some logic to handle this situation?

and Connor said...

Well, we probably need to see some test case/test data to comment with any kind of definite advice.

But *at a guess* I'm presuming you might be seeing something along the lines of:

App in Session 1
========
- Get me sales with status = 'READY TO PROCESS'
- Update status to 'IN PROGRESS'
- gets a list, starts work
- commit

Same App in Session 2 (doing parallel work)
========
- Get me sales with status = 'READY TO PROCESS'
- Update status to 'IN PROGRESS'
- gets a list, starts work
- commit

Even after session 1 updates the status to in progress, session 2 will *still* see those orders as 'ready to process' because the commit has not occurred. Thus you get duplicate processing and a mess.

Take a look here at

https://asktom.oracle.com/pls/apex/asktom.search?tag=select-for-update-skip-locked

for potential solutions

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library