Improving Bill of Materials data in Priority

I have often wanted in Priority that the lines in the Bills of Material (BOM) would show when a given line was added. Looking at the screen which displays this data yesterday, I discovered that there is indeed such a field and that there is a screen trigger which fills that field (and also stores the name of whoever added the line). So why were all our BOMs lacking this data?

I don't know whether the company for which I work is exceptional in this, but I estimate that 99.99% of our BOMs are created by copying previously existing parts. Obviously, changes are made in the BOMs themselves, but again about 99.99% of these are made by a configuration program. It seems as though the copying program and the configurator, both of which are external C programs, directly insert parts into the BOM without using the screen interface. So these lines lack the date they were entered, and by whom.

This is how the COPYPART procedure works: first, the user is asked for the catalogue number of the part which is to be copied. Then an empty screen is presented, in which the user writes the catalogue number(s) of the new part (s) - one can copy an existing part to a new part, or to several parts. Then the copying begins. My code takes advantage of the screen in which the new part numbers are written: these numbers are stored in a table called NPART.

My code runs at the very end of the COPYPART procedure: it loops over all the parts in the NPART table created by the current user and inserts a tuple into the 'extended BOM table', storing the user name and date. PARTARC is the name of the table which stores the BOM data.

DECLARE D1 CURSOR FOR
SELECT PART.PART
FROM NPART, PART
WHERE NPART.PARTNAME = PART.PARTNAME
AND NPART.USER = SQL.USER
AND PART.TYPE = 'P';
OPEN D1;
GOTO 300 WHERE :RETVAL <= 0;  
LABEL 100;
FETCH D1 INTO :NPART;
GOTO 200 WHERE :RETVAL <= 0;
DECLARE D2 CURSOR FOR
SELECT SON, SONACT, ACT, RVFROMDATE
FROM PARTARC
WHERE PART = :NPART;
OPEN D2;
LOOP 100 WHERE :RETVAL <= 0;
LABEL 110;
FETCH D2 INTO :SON, :SONACT, :ACT, :RVDATE;
GOTO 120 WHERE :RETVAL <= 0;
/* This is effectively PARTARC/BUF5 */
INSERT INTO PARTARCA (PART, SON, SONACT, ACT, RVFROMDATE)
VALUES (:NPART, :SON, :SONACT, :ACT, :RVDATE);
UPDATE PARTARCA
SET USER = SQL.USER, UDATE = SQL.DATE
WHERE PART = :NPART
AND SON = :SON
AND SONACT = :SONACT
AND ACT = :ACT
AND RVFROMDATE = :RVDATE;
LOOP 110;
LABEL 120;
CLOSE D2;
LOOP 100;
LABEL 200;
CLOSE D1;
LABEL 300;

The only strange part of this is code which was adapted from the existing screen trigger (PARTARC/BUF5): first an insert is performed into PARTARCA and then the same table is updated. As I understand it, this code first inserts a new record; the insertion could fail if there were no existing record - if someone were editing an existing BOM line. The update naturally updates; this is now guaranteed to work. If there were only an update without an insert, then new lines would not be inserted. Inserting without updating would fail when editing. So this slightly odd approach has been used.

I suppose I could simply have written an insert for my code, as at the time when it executes, there is guaranteed to be no existing line. I've kept it like it is in order not to depart too far from the original.