Products   Support   Download   Contact

Producing an Exploded Bill Of Materials

return to contents or index

Abstract

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.

Discussion

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.

The 3 Files

Three files are utilized:

PARTS_LIST

This is our file of parts of interest. It is a sequential file.

field description
PART Part number of a part of interest.

PART_MASTER

This file cross-references part numbers and part names..

field description
PART_NO Part number of the part (unique).
PART_DESC The part name.

BILL_OF_MATERIALS

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).

The Report: Files and Key Definitions

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.

The Report: Calculations

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 Report: Selection Logic

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
© 2013 GrayMatter Software Corporation