E9.1 F3111 Parts List in a Stored Procedure

Mumha

Member
Hi All

I'm not sure if it is possible but I'll ask the question anyway !

The request is, for every F4801 WO, read all corresponding F3111 Parts List records and if any of those records don't have enough available qty, then summarize as "Inventory Short". So the output would be one row per WO with "Inventory Short" or "Inventory Ready for Production" in an availability column. Is that possible ? If it was a UBE, obviously this wouldn't be a problem as it would be put on a Level Break, but a Stored Procedure is essentially one SQL statement.

Any advice gratefully accepted.
 
There are columns on F3111 that show you the inventory isn't there (after you've attached a parts list and committed stock)

does UORG match match QNTA is SOBK GT 0 etc

Is that not a bit simpler?
 
There are columns on F3111 that show you the inventory isn't there (after you've attached a parts list and committed stock)

does UORG match match QNTA is SOBK GT 0 etc

Is that not a bit simpler?
Hi John

I wish it was that simple ;)

In short, for each selected F4801 record, I need to read each matching Parts List record (usually 10-20 items including substitute items), determine qty ordered vs qty available, and if any one of those Parts List records has a negative available qty, then the WO is marked as Inventory short. So it's really a two pass process (or level break in a ube), but I don't believe a Stored Procedure can do that. If someone knows it can, I would happy to find out.
 
Have you committed the LOTN LOCN at this point or is the parts list just a copy of the BOM with blank inventory?

You can do it in a SP it's just I dont' agree doing it that way - as there are BSFNs in E1.
My company is using SQL to get LOTN LOCNs all the time and simply look at the F41021 quantity and commitment values.

Your issue is, if the parts list isn't allocated stock yet, when you let R31410 run it may not actually find the same LOTN LOCNs you initially did in your SQL
If it is populated with LOTN LOCNs then it's not that difficult
 
Here's my question: How do you need to represent the output?
  • In a JDE Application Screen?
  • On a UBE Report?
  • Non-JDE reporting / inquiry Solution?
I've done this all of these in the past. Not difficult just time consuming if you need to present the results in JDE.
In JDE Application/UBE Event Rules you just write the code and summarize into variables. Alternatively create a JDE virtual table that does the data filtering / summarization for you and just present the results in JDE.

If you need to do the summarization/filtering at the database level I would use a database query/view - not a SP.
 
Back
Top