jfranken - Thursday, August 18, 2011 10:34 AM:
Hi Guys
I am using Aras version 9.2 and created an excel import module for BOMs. A requirement we had for this was that we maintain the BOM structure exactly in the same order as how it was listed in the spreadsheet. This is the easy part since I have created a GUID (Unique Identifier) to identify all parts within the BOM (1 per upload). The structure of this BOM is then saved in an additional column in the PART_BOM table. This works quite well because the PART_BOM link is specific to the PART generation and gets deleted/re-created when the BOM is updated.
One problem I have experienced is when a sub-component is contained in multiple BOMs theres only one PART_BOM table entry i.s.o one per top level Finished Good.
I was wondering if anyone knows if version 9.3 supports BOM structuring and how it works or have any thoughts on this issue?
Regards
eric_h - Friday, August 19, 2011 4:15 PM:
Your post has me a little confused, but since I have been dealing with BOM uploads I thought I would reply. Every entry in the PART table has a unique GUID assigned to ID. The first iteration (first generation) of any part number gets that same ID assigned to CONFIG_ID. Every new iteration (generation) of that part number gets a new ID but the CONFIG_ID stays constant between generations. The constant CONFIG_ID is how Aras knows these are all generations of the same part.
In the PART_BOM table (which is a relationship type), your SOURCE_ID is the ID from the PART table that matches the parent item. The RELATED_ID is the ID from the PART table that matches a bill of material entry. If I have a part number at generation 1 with an ID of 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', then all rows in the PART_BOM table that have SOURCE_ID set to 'AAAAA..'. are bill of material entries for that generation 1 part. Let's say through whatever mechanism (depends on how you have versioning set up, automatic or manual for example) that generation 2 comes to be for that same part number. A new entry gets created in PART with a new ID, say 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'. As part of the internal Aras process of creating the new part generation, the bill of materials also gets duplicated by creating entries in PART_BOM where SOURCE_ID equals 'BBBBB...', and at this point both the generation 1 and 2 bill of materials match.
I think normal procedure at this point is to hand-alter the bill of materials for the generation 2 part, just altering the differential from generation 1. For our usage case, we want to fully load a new BOM as exported from out design tools. I am not sure if this is best practice, but I have essentially been wiping out the generation 2 BOM in PART_BOM via deleting all entries that have the generation 2 part set as SOURCE_ID. I now have a clean slate to import the new BOM, and can then do a compare with the previous generation via the redline highlighting that is part of the BOM tab (BTW, I have parts set for manually versioning, so ideally each generation represents a new revision.)
As far as sub-assemblies of an assembly goes, only the parent part number of the sub-assembly would be an entry for the assembly BOM in the PART_BOM table. But to find a multi-level BOM, you would just do a recursive BOM explosion (i.e. you check each BOM entry to see if it itself has entries in the PART_BOM table as SOURCE_ID), it just works. At this point you might be getting into the whole 'fixed' vs. 'float' relationship stuff that I have not fully messed with yet. But those ideas I think are not too complicated are really matter of processes in regards to how do you currently handle these relationships. I have to dig a little deeper into this stuff, but it seems not too difficult to grasp. None the less, the basic premise is that a sub-assembly is just a single entry in the assembly BOM, you just need to have the recursive search happen, which I assume is what the BOM Structure (multi-level BOM) tab does in 9.3.
One cool thing would be for Aras, on a single level BOM, to show an icon or Boolean check mark that indicated any parts that had a BOM associated with them. I am considering adding just a thing to some custom reports for single level BOMs. Another idea would be to create a 'has_bom' Boolean property for PART, and then tie some method code that every time you save that part it checks to see if there are any BOM relationships (in PART_BOM) and sets the Boolean flag appropriate to what it finds. Then you could display this field in either the main search grid and/or relationship grids. There are possibilities.
BTW, did you create some custom VBA to allow Excel to talk directly to Aras (if that is what you did)? That would be a cool and easy way to do imports into Aras.
Eric
jfranken - Monday, August 22, 2011 3:50 AM:
Hi Eric
Thanks so much for your reply. I think my post should read "BOM Order" i.s.o strcuture. I have already figured out and got the structure correct by iterating over the items using part and part_bom tables and related_id and source_id as described by you.
What I am trying to achieve is saving the order of the BOM lines internally. Currently I'm doing this by adding a column to the part_bom table to remember the order of the lines after the BOM was uploaded via the spreadsheet.
I did not know the meaning of the config_id so thanks a lot for that. This might come in handy.
Regards
Jaco
eric_h - Monday, August 22, 2011 12:04 PM:
Jaco,
Maybe this will help a little, or at least give you something to work with. One of the things you can define in an 'item type' definition is what property or properties to sort by. This is set by applying a numerical value to the 'Order By' column for a given property in the properties tab. You can set a value for multiple properties if you want, such as 'item_number' = 0 and 'major_rev' = 1 which would translate internal to Aras as an 'ORDER BY item_number, major_rev' clause to whatever database query happens.
Out of the box, the 'Part BOM' has a property called 'sort_order' (labeled as Sequence) that is set as the default 'Order By' property. By default this value is auto-populated by a 'Grid Event' that can be found in the 'Part BOM' RelationshipType. It is a bit of JavaScript that gets triggered anytime a new BOM row entry is added to a part. I don't know for sure, but I think it might also get triggered when you add BOM rows programmaticly (i.e. through code or AML), though I don't have enough experience yet to know when events are triggered or get bypassed. Main thing though is this value without removing the event for the most part gets auto-populated.
You could just populate a value yourself for this property and it would sort based on these values. Or you could pick another property (or multiple properties) to sort by. Let me know if this helps, though I might have missed the mark on what you are looking for.
Eric
jfranken - Thursday, August 25, 2011 3:47 AM:
Hi Eric
Thanks so much for you suggestion. I think this might actually solve my problem. Its exactly what I was looking for. Will give you sme more feedback once implemented.
Many Thanks
Jaco
