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 100+ 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 we 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

More to Explore

PL/SQL

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