The comptroller of a company for whom I do contract work complained that two standalone reports that present similar data have different values. These reports give sales invoice totals converted in dollars; the invoices may be in at least three different currencies, some with and some without VAT. Years ago I developed an algorithm for calculating the value of a single line: multiply the quantity by the price of the line, taking into account both the discount on the line and the invoice level discount. Once this is known, this intermediate value can be converted to dollars by means of a routine that I saved as a global trigger (i.e. as a trigger in the 'func' screen), that checks the currency of the line (I've seen invoices that have different currencies in the lines and in the header) and acts accordingly. It looks something like this:
GOTO 110 WHERE :$.CURR = -2; /* dollars */ SUB 900 WHERE :$.CURR <> -1; /* convert to NIS */ SUB 910; /* convert to dollars */ LABEL 110; ... #INCLUDE func/TEST_CNV2DOLLAR
Subroutine 900 converts the given amount of currency X to NIS according to the exchange rate at the invoice date; subroutine 910 converts NIS to $ is similar to subroutine 900 but does the same thing in reverse.
One of the reports uses this global trigger in a subroutine that iterates over all the lines of a given invoce. The other report used the FNCTRANS.SUM2 field that gives the dollar value of the financial transaction - whilst it might seem that it gives the appropriate value with no work, this is true only of export invoices; Israeli invoices include VAT and the SUM2 field gives the complete total for the invoice. I tried various approaches that would use this field in order to give the dollar value of the invoice but never succeeded (a new approach to this problem has just occurred to me: subtract from the SUM2 field the value of the VAT field in the invoice header divided by the exchange rate of the transaction. If the VAT is zero then SUM2 will be returned). Eventually I converted the local subroutine that uses a cursor to iterate over the invoice lines to a global trigger and saved it in the 'func' screen.
This company has a prediliction for what I am now calling 'container documents', aka complicated 'clever dick' HTML documents: the one that I am working on now (and have been doing so for maybe the past two months) has ten or eleven different reports displayed within a single container. Most of the reports are standalone, but it happens that one report uses a total that has been calculated in a previous report in the container. This requires the use of global parameters, e.g. a variable such as :$.AM, whose name is preceeded by a dollar sign. Internal variables appear to have scope that is restricted to the SQLI section in which they appear, although I am not totally convinced about this*. I suppose that I should check this by defining such a variable in one SQLI section of type integer then redefining it as a real in a different section. If this is so, then there's no real need for the use of global parameters, whose name is constricted to three letters.
Several of these reports display data regarding invoices, so it occurred to me that I could use my new global trigger code instead of having several SQLI stages have the same subroutine. I updated the various stages to use the global trigger; the syntax check of the entire container procedure showed no errors but I was unable to run the procedure. This normally means that I've redeclared a cursor in the procedure. I checked all the stages but could see no duplication; I renamed cursors but saw no duplication; I ran the entire procedure source through my cross referencer but still saw no duplication. I tried removing sections of the code but still the problem existed. After about an hour of extreme frustration, I restored previous versions of the sections (making use of the version control system that I developed a few years ago) and decided to leave things as they are.
This morning, in a splendid display of right brain thinking, the reason for the cursor redeclaration suddenly appeared. I was using a global trigger ... that was using a cursor! In other words, stage 89 defined a cursor C6200, stage 99 - by virtue of including the global trigger - also defined cursor C6200, as did stage 109. In other words, the redeclarations arose because of the use of the global trigger that contains a cursor. Ooops.
The idea that I had at the beginning of this blog as to how to calculate the dollar value of an invoice without using a cursor will provide a solution to the redeclaration problem, assuming that the new function provides the correct result.
* After declaring the same variable as a real in one stage and as a string in another stage, I found out that local variables have scope of the entire procedure and not of a SQLI stage. So there's no need to use 'global variables' prefixed with a dollar sign - one can use regular variables to carry values between stages.
No comments:
Post a Comment