Help with BOM!?

[kdm3]

Hi all,

A problem I have, and after much thought have still been unable to rectify - hence I ask for some impartial advice and suggestions...

I am writing a pseudo-MRP application, and need to store info. on products to be produced - hence the BOM. However, the company is a design-to-order company, and all orders (pretty much) are unique. Additionally, I want to store details of both processing instructions and purchased items.

Due to the unique nature of the parts the intermediate stages of the products, between the purchased parts and final item, do not have part numbers assigned to them.
Hence my idea was to use the order number as the basis for identifying each stage of the BOM, and then for each level down add further fields to identify the structure. e.g.

ID
ID-01
ID-01-01
ID-01-02
ID-02
etc.

And store each level in a seperatre table, so that each transitory part would be uniquely identified, but would also make sense when stored - making the BOM structure obvious. So there would be the main order table with the main info. customer ref, order date etc. then linked tables relating to the lower levels, with info on the processing steps, processing time, machine etc.
However, some items are purchased items and have different info. related to them than the processing details, e.g. purchasing part number.
Which would then require another set of tables to the processing tables.

Is there an easier way than this (surely), or will I just have to try to construct something which makes sense in code, using alot of queries?

I'm stumped, so any suggestions welcomed.
Plus, sorry this might not make too much sense, I'm not the best at explaining things!

 

[KenHigg]

Cool db.

How about something like this:

Define the part (model or class):
1. a table for products. (t1)
2. a table for assembly step (analogous to sub-assem's) (t2)
3. t2 would have an assem order fld.
4. a table for pc parts related to t2
5. a table for assem instruct related to t2

Track each product
6. a table for product instances. (t3)(When the work starts on product, you make an entry here) This table relates back to t1
7. a table for instance steps (t4) When a new t3 occurs a new set records are appended here which are based on t2
8. As the product sub assemblies work their way through the process, they are tarcked through t4


This is just a start but do you think it may be on the right track?

ken

 

[Len Boorman]

pseudo MRP. Hmmmm impressed.

A few statements to be sure we are both talking the same language

A Part number may be either a Parent or a Child of a Parent.

A Part number may be used as purchased or modified as part of the manufacturing process

So herein lies your solution I believe.

You do not need to identify parts through each stage of the manufacturing process unless you particularly want to capture costs for work in progress at particular stages

Your End Product will have a structure of various parts lets say 3 parts A,B and C for simplicity. It takes 30 mins to assemble at Work Centre Charlie

Part A is as Purchased but is paint so the Qty is 0.5 and the Unit of Measure is Litre.

Part B Is a purchased part that is modified at Work Centre Fred and takes 15 mins

Part C is an Assembly that comprises of other parts

So
The Hard Bit is going to be calculating the costs because it must be from the bottom up.

You will need to decided how many structure levels you are going to handle. It cannot be open ended. I have constructed FMECA analysis applications that work from a BOM and whilst it is easy to set up an everydeepening BOM it is a dog to handle from a calculation/analysis basis

You will also need to indicate if the item in the structure is a Component or an assembly (or sub-assy)

So you set up your structures... no problem
So you set up your Routings How things are made ... no problem

You calculate costs from bottom up

Cost =((Purchase cost x Useage rate) +(Routing Cost per 1 off)) x BOM Qty

Sum these for cost of Parent and then calculate Parent Conversion costs

etc
etc
up the BOM

You need to be very focused when constructing the calculation bit and come up one level at a time.

You also need to consider what is going to happen when you change something. You need to recalculate from that level up !!!.

So my advise would be always to work at BOM levels and the only way out of your application is up to top level and then out otherwise you will have unrecalculated cost changes.

The alternative is to do a recost on demand. Thais way you recalculate everything from bottom level up for everything

MRPII are complex systems, pseudo is not a lot easier in my opinion.

Good luck (Do not book any holidays)

len B

 

[kdm3]

Thanks for the advice...

Thanks for the advice guys, I has given me alot of food for thought!

Finally, with respect to my description of a pseudo-MRP application, I say this because it isn't a classical MRP structure. Due to reasons I won't bore you with, the application has a hybrid MRP/FOQ structure - essentially small requirements are fulfilled from stock, only large orders are handled using an MRP approach.