More fixing a complicated problem with BOM revisions and 'info only' parts

The code given in the previous blog was almost correct, but subtly wrong. I had ignored the fact that each son part would appear three times, and that the first instance would set SONACT to be -1 thus causing the son not to appear in the report, even though there would be a correct instance later on. A more correct version of this code is

 SUB 820;
 :OK = 0; /* the query below may fail */
 :RVDATE = 01/01/88;
 SELECT MAX (ORIG.RVTILLDATE) INTO :RVDATE
 FROM PARTARC ORIG
 WHERE ORIG.PART = :PARENT
 AND ORIG.SON = :SON
 AND ORIG.INFOONLY <> 'Y';
 :OK = (:RVDATE = 01/01/50 ? 1 : 0);
 GOTO 821 WHERE :OK = 1;
 UPDATE PARTARC /* effectively remove the part from the tree */
 SET SONACT = -1
 WHERE SON = :SON;
 LABEL 821;
 RETURN;

Saving the maximum value of RVTILLDATE into a variable was not in my original code; this was intended to help with running the procedure with the debugger. But for some reason the 'wonderful' web interface didn't work so I didn't get an automatic debug output.

There was a strange phenomenon whose source I failed to track down, even after wasting an hour on it. The part that I was using for testing should have four active sons, but for some reason five were being displayed. That fifth part had been marked 'for info only' in all the versions so the 'select max' query above should have failed. But somehow this fifth part never even got to the subroutine, a failure that I could not track. Eventually I added a cursor that is called immediately after the SONRAW procedure.

 DECLARE C840 CURSOR FOR
 SELECT SON, SONPARTREV
 FROM PARTARC
 WHERE SON > 0;
 OPEN C840;
 GOTO 843 WHERE :RETVAL <= 0;
 LABEL 841;
 FETCH C840 INTO :SON, :PARENT;
 GOTO 842 WHERE :RETVAL <= 0;
 :A = :B = 0;
 SELECT COUNT (*), SUM ((INFOONLY = 'Y' ? 1 : 0)) INTO :A, :B
 FROM PARTARC PA
 WHERE PART = :PARENT
 AND SON = :SON;
 LOOP 841 WHERE :A <> :B;
 UPDATE PARTARC /* this is the linked table */
 SET SONACT = -1
 WHERE SON = :SON;
 LOOP 841;
 LABEL 842;
 CLOSE C840;
 LABEL 843;

This cursor got rid of the spurious part and probably helped the rest of the procedure run a bit faster as there would have been fewer parts to check in the main cursor.

Working with BOM revisions is very complicated!