Considerations for changing Primary Location via SQL

We have a large number of items that are included on open purchase orders. Since we import from overseas, our POs typically stay open for months.

We are making some big changes to our warehouse, so next week we will need to change the Primary designation on a large number of items. Management is determined to do this using SQL, since it cannot be done in the software (because of the open POs). I would like to know how foolish this is. Is it just a matter of making the change to PBIN in the F41021? Are there other considerations?

*Cross posting from the MOS Community*

Yes, there are definitely other considerations. The location is stored on the PO Detail record (as well as WO parts list and SO detail), and you'll probably want to change these to your new primary location. In addition, there are numerous quantity buckets in the F41021 table that would need to have their values moved to the new primary location's record. There may be other considerations, but these are the ones that we have run into in our environment.

The location will stay the same. It will just go from a primary to a secondary, or vice versa.

So if quantities are soft committed to Location 123, which is a primary, they will stay committed to 123, but that location will now be a secondary.

I see. Then I think you should be in pretty good shape just flipping the flag. My 2 cents.

Hi

You can change the PBIN field by SQL, but:

1. Have no users online while doing the change
2. Run R43990, R3190 and R42995 after the change in order to recommit to the new primary location

we have done this last year (on XE), and all worked fine

also, test this first in your PY environment

good luck

Hello,
I've done this before within JDE OMW. We created a UBE that update Primary Location in the necessary files and then ran some standard "Repost" UBEs to recalculate quantities and commitments.

You can accomplish it safely by a mix of SQL + some "repost" jobs to recalculate open commitments - if you prefer SQL (I know I do)


We customized P41024 so the Row Exit -> Change Primary launched our custom UBE instead of the normal operation.

The tables you need to update are: F41021 (Item Summary), F3111 (Work Orders), F4211 (Sales Orders) and F4311 (Purchase Orders)

For F41021: Zero out all the commitment quantity fields (see attachment) for the Primary Location. We will run some repost UBE's later to repopulate those values. Leave On-Hand Qty alone.

*EDITED*: After zeroing out commitments, update F41024 to set your old 'P'rimary Location (PBIN, I think) to a 'S'econdary location and also update to set your new 'P'rimary location.

F3111: Only update "open" records (status may exist in header in 4801)
- TRQT = Transaction Qty
- WHERE TRQT = 0

For F4211: Only update "open" records
- Could do based upon status (WHERE Next Status NXTR <= 560 has not been ship confirmed, is still open)
- Update SDLOCN & SDLOTN

For F4311: Only update quantities that are "Open"
- WHERE NXTR Status <= 400 = still open AND PDUOPN != 0 (open quantity)
- Update PDLOCN & PDLOTN with new Primary LOCN and LOTN

After all of these updates, you will want to execute the following jobs:

R42995 - Repost Active Sales Orders - Use Data Selection for Item + Branch Plant
R43990 - Repost Open Purchase Orders - Use Data Selection for Item + Branch Plant
R3190 - Repost Open Work Orders - Again, Item + Branch Plant



Attached are my painstakingly detailed notes: I was being trained by a (very good) instructor on how to make this work using 100% OMW tools to get it done. So the notes are a bit noisy with general details on what I was doing within OMW itself.

Thank you for sharing this. We ended up changing the commitments, not just the PBIN, and we were mostly successful. There were 1-2 minor problems we are still looking at.

Now that we are beyond the emergency stage, we're looking forward in an effort to incorporate this with a PO repost, so this will be helpful. Thanks for sharing it!

Hi

beware only that if user is using configurated items, now F3215 have commitment information coming from sales and from WO

Hi,
Very good info. Thank you all for sharing.
Rafael

...