Well I dug this out of a dusty file folder on the server:
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
EXPLODING BILL OF MATERIALS REPORTS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Bill of Materials (BOM) applications have something in common with
agricultural and biological applications that track animal blood
lines. Both require a method for managing what's called the
exploding
parts (population) problem.
Products are made up of components that are in turn made up of other
parts, materials, or components. An animal produces offspring that
in
turn produce their own offspring. Both situations can create
explosion
diagrams similar to this one:
The January 1989 "R:BASE EXCHANGE" showed you how to use a database
design and an R:BASE for DOS program--using SET POINTER in a WHILE
loop--to explode the parts in a bill of materials report.
Now, this article shows you how to do the same thing using SQL and
the
same database design. You'll see a single complex SELECT command do
in
R:BASE 3.1 what it took an entire program to do in R:BASE for DOS.
Diagram of an Explosion
The diagram below shows a parts explosion. This example has four
levels. The product (PROD1) explodes into its components, and each
component explodes into the materials that comprise it, and one of
the
materials (M1) is made up of submaterials.
Level One: Product
| Level Two: Component
| | Level Three: Material
| | | Level Four: Submaterial
| | | |
| | | |
| | | |
| | | +---- Sub1
| | +----- M1 --|
| | | +---- Sub2
| +------ A --|----- M2
| | |----- M3
| | +----- M4
| |
| | +---- Sub1
Prod1 -----| +----- M1 --|
| | +---- Sub2
|------ B --|----- M2
| +----- M3
|
|
+------ C -------- M5
The Database Design
"""""""""""""""""""
To produce the explosion diagram from the data, you need a database
design that can accommodate any number of levels. The database needs
to be flexible enough to adapt to many different kinds of explosions
that use different kinds of components, materials, and submaterials.
Microrim recommends that you use the concept of parent-child
relationships to build the database.
For example, look at the explosion diagram shown above. Think of
each
element in the diagram as a parent of zero, one, or more other
elements (children) and as a child of one or more parents.
Design and Create the BOM
To represent these relationships in your database, create a table
(BOM) with two columns: PARENT (TEXT 10), and CHILD (TEXT 10).
CREATE TABLE bom (parent TEXT 10 +
NOT NULL, child TEXT 10 NOT NULL)
This database design works with an unlimited number of levels, and
because all the BOM relationships are in one table, it's easier to
track a path through the explosion--relationship by relationship.
Each
path through the diagram is unique. There's only one PROD1-B-M3 path
and only one PROD1-B-M1-SUB2 path.
Load the BOM
""""""""""""
Load BOM with a row for every relationship. For example, enter the
following lines at the R> prompt to load rows for the four-level BOM
diagram shown above.
LOAD bom USING parent, child
PROD1 A
PROD1 B
PROD1 C
A M1
A M2
A M3
A M4
B M1
B M2
B M3
C M5
M1 SUB1
M1 SUB2
M2 'The End'
M3 'The End'
M4 'The End'
M5 'The End'
SUB1 'The End'
SUB2 'The End'
END
You need a row for every possible path, including the parents that
have no children.
The SQL Explosion
"""""""""""""""""
Now with the database built and loaded, you can use the SQL SELECT
command (EXPLODE.PAR or EXPLODE.KID) to weave a path through the
explosion. That is, you can specify a parent and see all the
children,
or specify a child and see all the parents.
You can use the SELECT to display the explosion, or you can use it
to
specify the rows for a DECLARE CURSOR path, to create a view, or
with
the INSERT command to specify the rows that you want to add to
another
table.
See a Parent's Children
"""""""""""""""""""""""
To specify a parent part and see all the children parts, run
EXPLODE.PAR:
*( EXPLODE.PAR--given a parent, find the children.)
DIALOG 'Enter the name of the parent part: ' vparent vkey 1
SELECT t1.parent, t1.child, t2.child, t3.child, t4.child +
FROM bom t1, bom t2, bom t3, bom t4 +
WHERE t1.parent=.vparent AND t1.child=t2.parent +
AND t2.child=t3.parent AND t3.child=t4.parent +
UNION SELECT t1.parent, t1.child, t2.child, t3.child, ' ' +
FROM bom t1, bom t2, bom t3 +
WHERE t1.parent=.vparent AND t1.child=t2.parent +
AND t2.child=t3.parent AND (t3.child NOT IN +
(SELECT t4.parent FROM bom t4)) +
UNION SELECT t1.parent, t1.child, t2.child, ' ', ' ' +
FROM bom t1, bom t2 +
WHERE t1.parent=.vparent AND t1.child=t2.parent +
AND (t2.child NOT IN (SELECT t3.parent FROM bom t3)) +
UNION SELECT t1.parent, t1.child, ' ', ' ', ' ' +
FROM bom t1 +
WHERE t1.parent=.vparent +
AND (t1.child NOT IN (SELECT t2.parent FROM bom t2))
See a Child's Parents
"""""""""""""""""""""
To specify a child part and see all the parent parts, change all the
CHILD column references to PARENT and all the PARENT column
references
to CHILD, as in EXPLODE.KID.
*( EXPLODE.KID--given a child, find the parents.)
DIALOG 'Enter the name of the child part: ' vchild vkey 1
SELECT t1.child, t1.parent, t2.parent, t3.parent, t4.parent +
FROM bom t1, bom t2, bom t3, bom t4 +
WHERE t1.child=.vchild AND t1.parent=t2.child +
AND t2.parent=t3.child AND t3.parent=t4.child +
UNION SELECT t1.child, t1.parent, t2.parent, t3.parent, ' ' +
FROM bom t1, bom t2, bom t3 +
WHERE t1.child=.vchild AND t1.parent=t2.child +
AND t2.parent=t3.child AND (t3.parent NOT IN +
(SELECT t4.child FROM bom t4)) +
UNION SELECT t1.child, t1.parent, t2.parent, ' ', ' ' +
FROM bom t1, bom t2 +
WHERE t1.child=.vchild AND t1.parent=t2.child +
AND (t2.parent NOT IN (SELECT t3.child FROM bom t3)) +
UNION SELECT t1.child, t1.parent, ' ', ' ', ' ' +
FROM bom t1 +
WHERE t1.child=.vchild +
AND (t1.parent NOT IN (SELECT t2.child FROM bom t2))
How Does EXPLODE.PAR Work?
""""""""""""""""""""""""""
You don't have to understand how EXPLODE.PAR works in order to use
it,
but you do need to understand it if you plan on modifying it.
By using correlation names (T1, T2, T3, and T4), you can select data
from the same table four times--just as if the four were different
tables. Essentially, you're joining the BOM table with itself to
make
as many copies as you have levels of children. In this example,
there
are four levels of parents, so four copies (T1, T2, T3, and T4) of
the
BOM table are used in the SELECT command. There are four SELECT
commands in one.
The First SELECT
""""""""""""""""
The first SELECT in EXPLODE.PAR finds all the rows that go all the
way
through all four levels. The WHERE clause steps through the
explosion
like this:
o Find all the rows in T1 that match the parent part requested.
o Take the child parts from those T1 rows and find all rows in T2
where those child parts are parent parts.
o Take the child parts from those T2 rows and find all the rows
in
T3 where those child parts are parent parts.
o Take the child parts from those T3 rows and find all the rows
in
T4 where those child parts are parts in level four.
If you had more levels, you'd add more to the WHERE clause until you
reach the final level.
This first SELECT finds the rows that have parts that are
represented
in all four levels, and it finds only these rows. If a part is a
child
in T2 but not a parent in T3 the row will not be found in this first
SELECT. It will, however, be found later by one of the three UNION
SELECTs. These other three SELECT clauses find the rows that stop
after three, two, and one levels.
The Second SELECT
"""""""""""""""""
Next, the UNION operator joins the rows found by the first SELECT
with
the rows found by the second SELECT. This second SELECT finds rows
that go through the first three levels only. Since you won't be
finding a part on level four, you need to place a constant (a blank
enclosed in single quotation marks) in the SELECT list and only
select
rows from three copies of the BOM table. The final condition on the
WHERE clause for this second SELECT is a sub-SELECT that finds the
rows that are children in level three but not a parent in level
four.
The Third & Fourth SELECTs
""""""""""""""""""""""""""
The third and fourth SELECTs finish the job. The third SELECT gets
the
child parts that are not parents in level three, and the fourth gets
the parts that are not parents in level two--that is, these parts
exist only in the first level (T1).
Each time the new set of rows that are selected by the SELECT clause
are appended to the previously selected rows by the UNION operator.
If you had fewer levels, you'd need fewer UNION SELECT groups, or
missing levels would be left blank. If you had more levels, you'd
need
more UNION SELECT groups.
The SELECT command assumes that for every parent part entered there
is
an entry in the child column. In other words, it assumes that there
are no null values in either column.
Outer and Inner Joins Are Used
""""""""""""""""""""""""""""""
This technique uses both outer joins (the UNION operator joins
together SELECT clauses) and inner joins (each SELECT command
selects
rows using more than one copy of the BOM table).
By studying this example, you can become more proficient at using
complex SELECT commands, but remember that the more inner and outer
joins you do, the more time it will take the computer to do the job.
In fact, joins this complex can take days to complete depending on
how
big the BOM table is.
This is only a beginning. Here you learned how to design a database
and write a complex SELECT statement to explode a product into its
component parts and materials. But remember that the exploding parts
problem is complex. This database design and UNION SELECT solution
won't solve every exploding parts problem. It's a beginning.
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Sent: Wednesday, November 25, 2015 11:13 AM
To: [email protected]
Subject: [RBASE-L] - Re: BOM
I would use a temporary reporting table for this. The columns to
report
would be as follows or both Assy/Sub part numbers and descriptions. I
find that system easier than using report variables.
Assembly
Assembly description
Sub Level 1
Description Level 1
Sub Level 2
Description Level 2
Sub Level 3
Description Level 3
Load the table with a stored procedure, repeating the values in each
column and adding the subs as needed.
Each column would be reported with repeated values hidden. That should
give you what you need.
Albert
On 2015-11-25 8:57 AM, Jim Belisle wrote:
> For years we have costed our products with a simple report just
throwing together all parts making up a product not worrying about
using an indented BOM.
> We would like to go to an indented BOM.
> Right now it is just
> Model# then all parts making up the model.
>
> We want something like this:
> Level 1: Model#
> Level 2: Assemblies and parts we ship
> Level 3: Sub assemblies (or parts if no subs)
making up the assemblies.
> Level 4: Parts making up the sub
assemblies.
>
> I can get it so all assemblies are together and then all subs
together by assigning a BOMtype to them.
> So it will be:
> Assy
> Assy
> Assy
> Sub assy
> parts
> Sub assy
> parts
> Sub assy
> Parts
>
> That is not however what we want. I just can't seem to figure the
proper way to handle this.
> Any blues clues would be appreciated.
>
> James Belisle
>
> Making Information Systems People Friendly Since 1990
> [cid:[email protected]]
>
>
