CURREGITEMS

CURREGITEMS is the name of the table that contains historical data regarding representative rates of different currencies. It has three fields of interest, namely CURRENCY, CURDATE and EXCHANGE: these are self-explanatory. The table is populated automatically upon the entry of new data into the connected CURRENCIES table. So if one runs the report Financials > Maintenance of Financials > Exchange Rates > Daily Exchange Rates (CURRENCIES) for USD, one gets the following data:

Date	Rate
30/11/23	3.714
01/12/23	3.739
04/12/23	3.708
05/12/23	3.728

Thus if one wants the dollar representative rate for an invoice dated 04/12/23, simple code to do so would be 

SELECT EXCHANGE FROM CURREGITEMS
WHERE CURRENCY = -2
AND CURDATE = 04/12/23;

Of course, one would use parameters for CURRENCY and CURDATE; The result for this query would be 3.708. 

But what happens if one wants the exchange rate for Sunday, 03/12/23? One must remember that the Bank of Israel does not publish representative exchange rates for Saturdays and Sundays, and indeed the above table does not have an entry for 03/12/23. The way that I was taught (or taught myself) many years ago when first working with Priority is that when there is no value in CURREGITEMS, one uses the value of the EXCHANGE field in the CURRENCIES table which is the most up-to-date value. As a result, I have written in many reports or procedures expressions such as

SELECT (:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE :
CURRENCIES.EXCHANGE)) INTO :EXCH
FROM CURRENCIES, CURREGITEMS ?
WHERE CURRENCIES.CURRENCY = CURREGITEMS.CURRENCY
AND CURRENCIES.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;

A problem with this code is if one is showing data from several months ago; the representative rate for the dollar might have been 3.5 but now it's much higher. I thought that I would have to write a loop to get the representative rate of the date previous to the desired rate, something like

LABEL 10;
:EXCH = 0.0;
(:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE)) INTO :EXCH
FROM CURREGITEMS 
WHERE CURREGITEMS.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;
:DATE = :DATE - 24:00;
LOOP 10 WHERE :EXCH = 0.0;

But it transpires that this is totally unnecessary! There is a value for 03/12/23 in CURREGITEMS - it's the same value as for 01/12/23, or in other words, the last known representative rate. There's no need for left joins between CURRENCIES and CURREGITEMS.

I have no idea whether this has always been the case and that the queries that I wrote in 2007 were misguided even then, or whether the method of inserting tuples in CURREGITEMS has changed in the passing years. I suspect that there has been no change and that I have been perpetuating a misunderstanding for the last 17 years. I learn something new every day.

APPENDUM: the original code with a left join between CURRENCIES and CURREGITEMS is necessary when the given date is in the future. In this case, there definitely will not be an appropriate entry in CURREGITEMS and so it's 'correct' to use the value from CURRENCIES. 

Sonraw

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 

1. a table linked to PART - this will contain the parent parts
2. 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
3. a table linked to PARTARC - this will contain the output
4. 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 (?)
5. 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.

Welcome

Welcome to 'Programming Pitfalls in Priority'! 'Programming' is to be read as an adjective, not a verb, so I'm writing about the pitfalls which await programmers in the Priority ERP system. I am not affiliated with this company; I work for a normal manufacturing company as an ERP developer and trainer. I have been working with Priority for 13 years and before that with a simpler ERP system, so I have plenty of experience in my field. 

In this blog I will be writing about pitfalls which I personally have come across in the Priority system during the course of my work. Generally these topics are not documented or hinted at in the Software Development Kit (SDK) and so they are quite advanced. As my descriptions and solutions are based on my own work, there may be better ways of overcoming the pitfalls; I am not privy to internal documentation and so am forced to depend on my own ingenuity, sometimes with the help of colleagues.