The external program SONRAW is invaluable: it takes a list of parts and builds
an 'exploded' bill of materials for each part. Unfortunately, there are also a
few very important 'gotchas' of which we have to be aware. These arise due to
what might be considered a poor decision by the original system developers:
the output of SONRAW is stored in the table PARTARC, which is the same table
in which are stored the default BOM data.
A brief explanation
Putting this another way, the 'normal' table PARTARC stores a parent part and
its direct sons, i.e. to a depth of 1. Let's say that there is a parent part A
and has sons B, C and D: there will be three rows in the PARTARC table where
the parent is A. But B, C and D might have sons of their own (eg B1, B2, B3,
C1, etc). In this case there will be three rows in PARTARC where the parent is
B (the sons will be B1, B2 and B3). If it's not yet clear, a part can appear
both in the 'part' field of PARTARC, when it is a parent, and in the 'son'
field, when it is a son.
What does SONRAW do? If there are three rows, A/B, A/C and A/D, and another
three rows B/B1, B/B2 and B/B3, the resulting linked PARTARC table will also
contain six rows, but these will be A/B, A/B1, A/B2, A/B3, A/C and A/D
although not necessarily in this order. There is a field in PARTARC that
stores the depth of the son; for A/B, the depth will be 1, but for A/B1, the
depth will be 2. This data enables one to write a relatively simple
report that displays the recursive tree as a list.
How does one use SONRAW?
One can use SONRAW as a separate procedure step (see procedure PARTTREE,
stage 40) or one can call SONRAW within an SQLI step; it's slightly more than
a matter of preference - I find it easier to call SONRAW within an SQLI step.
Whichever way, there must be five parameters passed; these are
- a table linked to PART - this will contain the parent parts
-
a date - I think that this is useful when installations have different
versions of BOMs. I don't have this requirement and so always pass today's date
- a table linked to PARTARC - this will contain the output
-
an integer between 0 and 4 - this indicates which option to use (look at
PARTTREE, stages 10-30). I normally use 0; 2 will include phantoms in the
output (?)
-
a flag that can either be empty or '-N'; the 'N' probably stands for
'normal'. Leaving the flag empty will cause the output to be partial; see
PARTTREE stage 10 for 'an explanation'
My normal code for using this is as follows
LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK PARTARC TO :$.ARC;
ERRMSG 1 WHERE :RETVAL <= 0;
/* the parameter 2 means show phantoms as well as standard parts */
EXECUTE SONRAW :$.PRT, SQL.DATE8, :$.ARC, 2, '-N';
What comes after this depends on the application. One can unlink the tables, then pass PARTARC to a report that prints out the tree. I often have a cursor that runs after the SONRAW code in order to extract only purchase parts (and writing this now, I realise that the fourth parameter to the call above is '2'; according to PARTTREE, this means that only R parts will be included in the output).
So where are the pitfalls?
There are several fields in the PARTARC table that appear (unsurprisingly) in the PARTARC screen (and also PARTARCONE) that SONRAW trashes. Prior to SONRAW, the field PART contains the internal part number of a given parent (e.g. B in the simple example about), SCRAP will contain the percentage scrap of part B1 (the calculation of the scrap is not straightforward, but that's another story), SONREVNAME contains the internal number of the son revision chosen for this line, and SONQUANT contains the quantity of B1 needed for one unit of B. But after SONRAW, PART contains the internal part number of the top part (i.e. A), COEF will contain the quantity of B1 needed for one unit of A, and SONQUANT will contain the internal part number of the original parent (i.e. B). SCRAP and SONREVNAME contain values but they're not what they were prior to running SONRAW (and I don't know what they are).
PARTARC.SONACT also gets trashed but in this case, the new value after SONRAW is very useful: it holds a sequential index to the lines in PARTARC (i.e. 1, 2, 3). This gets used in any report based on PARTARC as it presents the lines in the correct, hierarchical, order. But this index has added value: it can be used as an index into an auxiliary table (e.g. STACK4) that holds additional data for each line in the exploded tree.
In the past, I discovered that I have to write some obscure code in order to obtain the scrap and revision number, as follows.
DECLARE C3 CURSOR FOR
SELECT PARTARC.SON, PARTARC.COEF, PARTARC.VAR, PARTARC.SONACT,
ROUND (PARTARC.SONQUANT)
FROM PARTARC, PART ORIG
WHERE PARTARC.SON = ORIG.PART
AND PARTARC.PART = :PART
AND PARTARC.SON > 0
ORDER BY 4;
OPEN C3;
GOTO 300 WHERE :RETVAL <= 0
;
LABEL 210;
FETCH C3 INTO :SON, :COEF, :VAR, :SONACT, :SQ;
GOTO 299 WHERE :RETVAL <= 0;
:SCRAP = 0.0;
:REV = 0;
SELECT SCRAP, SONREVNAME INTO :SCRAP, :REV
FROM PARTARC ORIG
WHERE PART = :SQ
AND SON = :SON;
INSERT INTO STACK4 (KEY, REALDATA, INTDATA)
VALUES (:SONACT, :SCRAP, :REV);
The final lines above show :SCRAP and :REV being stored into an auxiliary table that gets passed to the report along with the linked PARTARC table, so that the report can show the necessary values. The cursor uses ROUND (PARTARC.SONQUANT) as SONQUANT is a real number, but an internal part number is an integer, of course.
I wasted about an hour today because of this problem with SONREVNAME. It's not a field that I use myself, but a company for whom I am writing an exceedingly complicated BOM report needs it. Eventually the penny dropped as to the problem: I already had the code for obtaining the correct value of SCRAP so I adopted it for this new program.
