Running COSTING automatically

From a financial/inventory point of view, the external program COSTING is one of the most important programs under the Priority umbrella. For those that don't know, this program calculates the value of every part stored in a given 'accumulator', that in itself stores the inventory of each part in every warehouse at a given date (the program can also calculate for previous accumulators). Unlike the daily costing program, this program saves its data forever and so is extremely useful when wanting to know the value of inventory at a given date (normally the last day of a month). 

There is a parameter (ARG) passed to COSTING that determines whether it will be a daily run (deleting previous values) or a monthly run (saving values). In the first case, ARG will equal 4, whereas in the second case, ARG will equal 1.

Until not so very long ago, one could schedule COSTING and it would run automatically, using the most current accumulator. Unfortunately, this behaviour changed a few versions ago, when a scheduled run would fail because it didn't have a date for the accumulator. As this program runs for 14-16 hours on my server, this meant that I would have to get up at 4:15 on a Friday morning, stumble to the computer, connect to the server and start the program.

Eventually I got fed up with this and tried to determine what would be needed to run COSTING automatically on the last accumulator only. Basically a two step procedure is required, but I added a third step to send me email that the procedure has completed. This code does indeed run COSTING automatically, creating a monthly costing.

Stage 10 - SQLI                                                                                                                
/* TEST_COSTINGACC - No'am, 25/09/25                                
A cut-down version of COSTINGACC that calculates for the last tzovar
only - automatically.
Parameters: DAT, type LINE
*/                                                                  
:LASTDATE = 0;                                                      
#include func/FashionCosting                                        
SELECT CURDATE INTO :LASTDATE FROM ACCDATES ORIG                    
WHERE COSTFLAG <> 'Y' AND NOT EXISTS                                
(SELECT 'X' FROM ACCDATES ORIG2                                     
WHERE ORIG2.COSTFLAG = 'Y' AND ORIG2.CURDATE > ORIG.CURDATE)        
ORDER BY CURDATE DESC;                                              
/* My stuff */                                                     
:CURDATE = 01/01/88;                                                
SELECT MAX (CURDATE) INTO :CURDATE FROM ACCDATES;                   
LINK ACCDATES TO :$.DAT;                                            
GENMSG 1001 WHERE :RETVAL <= 0;                                     
INSERT INTO ACCDATES                                                
SELECT * FROM ACCDATES ORIG                                         
WHERE CURDATE = :CURDATE;                                           
UNLINK ACCDATES;                                                    
/* Input stage */                                                   
SELECT VALUE INTO :STARTDATE FROM LASTS WHERE NAME='COSTSTARTDATE'; 
:CURDATE = :FIRSTDATE = 0;                                          
:COUNT = 0;                                                         
LINK ACCDATES TO :$.DAT;                                            
SELECT MIN(CURDATE), COUNT(*) INTO :CURDATE, :COUNT                 
FROM ACCDATES WHERE CURDATE > 0;                                    
UNLINK ACCDATES;                                                    
SELECT CURDATE INTO :FIRSTDATE FROM ACCDATES WHERE CURDATE > 0      
AND COSTFLAG = 'Y' ORDER BY CURDATE;                                
SELECT DTOA(0+:STARTDATE, 'XX/XX/XX') INTO :PAR1 FROM DUMMY;        
ERRMSG 1 WHERE :FIRSTDATE = 0 AND 0+:STARTDATE <> :CURDATE          
AND :STARTDATE <> 0;                                                
:ENAME = 'COSTINGACC'; :DATE = 0 + :CURDATE;                        
#include func/CostingLog    
-----------------------------------------------------------------
STAGE 20: COSTING
Parameters: ARG, type INT, value 1
MSG, type ASCII
DAT, type LINE
/* The parameters have to be in this order */
-----------------------------------------------------------------
STAGE 30: SQLI
#include func/CostingMpart
:PAR1 = '$';
MAILMSG 40 TO EMAIL 'tabula@gmail.com';

Saving a part's picture by means of an interface

The scenario: a part is copied from company A to company B, along with various fields, including the path to the part's picture. The picture has a special status in the table PARTEXTFILE: the value of the field EXTFILENUM will be -1.

In the procedure that has been running for the past few years, the path to the picture has been obtained correctly, but was being stored as a regular entry in company B. The person responsible for company B had never brought this subject to my attention until now. When I saw that the picture was being saved as a regular entry, I tried to overcome this by passing the value -1 in the appropriate tuple in GENERALLOAD. This did not work.

My next attempt was a typical hack: find the value of EXTFILENUM for the part in company B and update it to -1. This worked, but obviously was not the correct way of solving the problem. Fortunately sanity was restored when I asked myself how regular saving of the picture works in the form LOGPART. This is when I discovered that the path is stored in the form variable EXTFILENAME, and that the trigger BUF11 causes this value to be stored as a picture in PARTEXTFILE (below is the standard code).

/* Insert, Update and Delete EXTFILENAME */
GOTO 111 WHERE :$.EXTFILENAME = :$1.EXTFILENAME
;
/* clean previous record */
DELETE FROM PARTEXTFILE
WHERE PART = :$.PART
AND EXTFILENUM = -1
;
GOTO 112 WHERE :$1.EXTFILENAME = '';
EXECUTE DELATTACH '-I', :$1.EXTFILENAME;
LABEL 112
;
/* insert new attachment */
GOTO 111 WHERE :$.EXTFILENAME = '';
INSERT INTO PARTEXTFILE (PART, EXTFILENUM, EXTFILENAME)
VALUES (:$.PART, -1, :$.EXTFILENAME)
;
LABEL 111;

Note the use of the procedure DELATTACH: this can cause the actual file to be deleted if it is not referenced anywhere. Saving the file parth in PARTEXTFILE is done by an insertion, thus enabling the value -1 to be set; doing this via an interface doesn't work.

Now I save the file path as the equivalent to :$.EXTFILENAME in the same tuple as the part name before passing it to the interface. And this works.