Massive BOM Explosions...

[DBohner-(db)]

Howdy,

Client desires to run a UBE over all Open Orders in the F4211 - and do a Flat BOM Explosion (Every Item - and Explode all it's parts and their parts and... into a Summary table)...

As I've inherited the process, the current flow goes as following

F3002GetNextBOMRecordShell (B3003090)

F3002GetBOMShellCache (B3003450)
If Successfull, Start Loop

Do some additional I/O to
- F4101
- F4102
- F4105
- F41021
- F4311
- A Couple Custom Tables

Insert or Update into Summary Table

F3002GetBOMShellCache (B3003450)
End Loop

This process TAKES FOREVER!...

Recently we attempted to run the existing process over open orders for one Business Unit (14,400 lines) - and we killed it after 18 hours. We estimate that those 14,400 lines should have take over 30 hours to complete.

So - is there a 'quicker' practice to the BOM Explosion - than using the B3003090 and B3003450 'Explosion' process?

I'm looking for the quickest path the read the Open Order Lines from the F4211 and fully explode (flat) the Bill of Materials for Each Line... A 'Summary BOM' table will be cleared, then updated with the 'Summary' information from the explosions.

Note Of Fun:... Being that this is a post for Massive BOM Explosions - I'd expect that everyone reading this post (or, especially responding to it... or authoring it) to be scrutinized by the NSA. So, as you read or reply - make sure you say hi to them...

HI NSA!

(db)

 

[Larry_Jones]

Hi Daniel (and HI NSA!)

I've done something similar, but not the same.

We needed to peg downward from Sales Order Lines to supply Mfg Work Orders, to children WOs, etc all the way down the tree - not just exploding but updating tables (the RORN field among others)as well as creating a "Pegging map" of the complete Parent Child relationship from a SO Line to the bottonmost Work order. Its actually more complicated then a BOM explosion which really only has effectivity to worry about. On the WO side you have to match up the right WOs based on dates and quantities, and ...

Anyway, not that I've established how hard it was -

Looked at it and said - OneWorld toolset can't do this in a reasonable time and its toolset is not well suited to recursion.

So I wrote it in VB - much, much faster.
Below excerpt from Log file shows stats - basically took 30 minutes to process 3166 SO Lines - and I know it could be optimized a lot more if I troubled myself to write db procedures/functions rather than straight SQL and VB.

So net, net I'm saying go outside of the JDE toolset. I bet you could even do the whole thing in PL/SQL

"8/1/2013 11:30:28 PM","Main","Number of Demand Sales Order Lines Processed = 3166"
"8/1/2013 11:30:28 PM","Main","Number of Demand Sales Order Lines On Hold = 71"
"8/1/2013 11:30:28 PM","Main","Number of Demand Sales Order Lines Cancelled = 7"
"8/1/2013 11:30:28 PM","Main","Number of Demand Sales Order Lines Updated = 1676"
"8/1/2013 11:30:28 PM","Main","Number of Supply Work Orders Pegged = 5848"
"8/1/2013 11:30:28 PM","Main","Number of Supply Work Order Parts Pegged = 30962"
"8/1/2013 11:30:28 PM","Main","Number of Supply Purchase Orders Pegged = 947"
"8/1/2013 11:30:28 PM","Main","Number of Multi-Level Pegginging Entries made= 8206"
"8/1/2013 11:30:28 PM","Main","Number of Supply Orders with Multiple Demands= 16737"
"8/1/2013 11:30:28 PM","Main","Number of Demands w/no Matching Work Orders = 543"
"8/1/2013 11:30:28 PM","Main","Number of Demands (Pur Nuk) w/no Matching POs= 25"
"8/1/2013 11:30:28 PM","Main","Maximum Number of Levels Found = 5"
"8/1/2013 11:30:28 PM","**************************************************************************************"

 

[78aspide78]

Daniel,
just another way:
if you are on Oracle DB use jde virtual table + materialized view + start with / connect by prior sql statement.

 

[78aspide78]

If you want to go on with ube solution you can:
1)Run different version in parallel (based on data selection), one ube take one processor so if you have 10 cpu you can speed up the process
2)Cache tables at ube start
3)Ask to DBA to trace sql session and evaluate access plan

 

[Scott Beebe]

I did something similar for a client. They were creating a poor man's ERP system. The process would review all open orders to determine demand and we would calculate available qty against inventory and give a message if we were going to be short on an item or any item in a kit if it was a built item.

I built custom functions to go down the BOM tree. If the item was not a purchased item (F4101.STKT != P) I would look up the item in the F3002. If I found it then I would read each item in the BOM and save it to my table. For each item in the parent BOM I would look in the F3002 to see if the child item had a bom. It would do this for each item following the tree down to the bottom level until it didn't find another BOM. Building it as a recursive function allows you to write a single function to pull the parent BOM and all child BOMs.

This process runs in just a few minutes. I can't tell you how many open lines we run so I don't know how performance will compare.

 

[DBohner-(db)]

I did something similar to what Scott suggested, on another project.

The 'recursive' function (calling itself for each Itm/Level of the BOM0 worked slick.

Just looking to see if there was a more-standard approach.

Ugh!

 

[Olavo Henrique Dias]

I had to do it for a customer some time ago, and the way I found it was using a custom function that would recursively explode the BOM.

The logic was: if there was no inventory avaiable to ship, the program was inquiring the BOM and checking if we had enough inventory to at least build it... once the user hits OK, if we had components with no inventory, email messages were sent to the component planners. (I'v saved the BOM and on hand quantity for each component in cache).

Sadly.. nothing was standard on the BOM explosion.