An Exploded Bill of Materials, sometimes referred to as a Parts Explosion, is a report of all of the parts of parts used to produce an assembly. Typically the tables which define such relationships link back to themselves. Neither SQL nor XENTIS is particularly suited to this problem, but this report discusses how XENTIS can be used to successfully tackle the problem..
A saveset with the dictionary, data files and reports discussed in this report are available from GrayMatter Software on request.
In a Parts Explosion, an assembly is composed of parts. The parts may (or may not) in turn be assemblies, which are also composed of parts, and so on. The two challenges which will be encountered are:
The approach used in this example is to start with a list of parts of interest, basically to answer the question "how many of these do we need to make one of those?". This is a "bottom up" approach: we know what part(s) we're interested in, and we need to crawl up the tree to see if they are used in an assembly, and if so how many times... and then if that assembly is used in an assembly, and if so how many times.
Some things are known about the database:
Bear in mind that these postulates are not the only circumstances in which a bill of materials for a parts explosion can be produced; rather, they define the topology of the database, and certain nuances of the reporting strategy must be changed when the topology changes.
Three files are utilized:
This is our file of parts of interest. It is a sequential file.
field description PART Part number of a part of interest.
This file cross-references part numbers and part names..
field description PART_NO Part number of the part (unique). PART_DESC The part name.
This file links back to itself, and describes which parts comprise assemblies.
field description COMPONENT Component part number. Represents an instance of this part as a component of an assembly. PARENT Part number of the assembly which this instance of the component becomes part of. QUANTITY Number of units of the part (COMPONENT) which are required to produce the assembly (PARENT).
Rather than reproduce the files and keys sections of the report here, the data is presented in a tabular form in the hopes that it will be more understandable in that format.
file number/letter file name fields to build key 1/A PARTS_LIST not applicable 2/B PART_MASTER PART_NO = A:PART 3/C BILL_OF_MATERIALS COMPONENT = A:PART 4/D PART_MASTER PART_NO = C:PARENT 5/E BILL_OF_MATERIALS COMPONENT = C:PARENT 6/F PART_MASTER PART_NO = E:PARENT 7/G BILL_OF_MATERIALS COMPONENT = E:PARENT 8/H PART_MASTER PART_NO = G:PARENT 9/I BILL_OF_MATERIALS COMPONENT = G:PARENT
All files are joined with Multiple records "N" and Replace missing data with zeros/nulls "Y". Notice how the BILL_OF_MATERIALS file refers to itself.
In the previous section we replaced missing data with zeros and nulls. This means that when we get to the "top level", even if it's not at file I, we'll still get the records which were in the tree up to that point. It also plays into our calculation which multiplies the quantities to determine the total number of parts of interest used in the assembly of interest.
B_QUANTITY = C:QUANTITY B_QUANTITY = 1 IF (C:QUANTITY = 0) D_QUANTITY = E:QUANTITY D_QUANTITY = 1 IF (E:QUANTITY = 0) F_QUANTITY = G:QUANTITY F_QUANTITY = 1 IF (G:QUANTITY = 0) H_QUANTITY = I:QUANTITY H_QUANTITY = 1 IF (I:QUANTITY = 0) PART_QTY = B_QUANTITY * D_QUANTITY * F_QUANTITY * H_QUANTITY
The final piece of the puzzle is that
we can use the following selection logic:
O ! Type of selection logic is "OR" C:PARENT = 2 ! Selection expression 1 E:PARENT = 2 ! Selection expression 2 G:PARENT = 2 ! Selection expression 3 I:PARENT = 2 ! Selection expression 4