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.
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.
- 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'
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';
So where are the pitfalls?
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.
No comments:
Post a Comment