Exception Reporting with Suprtool - Some Ideas
The IF command supports arithmetic operations between fields, even fields of different numeric data types. This is useful in checking some of the 'business rules' that most applications have. These rules are usually included in the data entry programs but sometimes 'stuff happens' and the data gets messed up. Here is a simple task that uses this feature to isolate which records are breaking the rule of "Invoice amount = price times quantity".
>base sales.db,5,reader {MPE, on HP-UX use >open ..}
>get d-invoices {MPE, on HP-UX use >select ..}
>if price * quantity < > amount
>list standard title "Price * Qty not = Amount" device LP
>xeq
This idea can be used in a monthend job that prints exception reports.
Take Action Based on Exceptions
When Suprtool completes a task such as that shown above, it also sets a variable named SUPRTOOLFULLCOUNT to the number of output records. If this value is 0, then no exceptions were found and the report can be discarded. If this value is greater than zero, then someone should review the results.If exceptions are found, you could just not delete the spoolfile:
!if suprtoolfullcount <= 0 then
! set stdlist=delete
!endif
On HP-UX, the total is written to the stoutcount file, which can
be tested as follows:
if [ `cat .stoutcount` -ge 10 ]; then
echo "More than 10 records found"
fi
Or you could handle exceptions in a more creative way, but emailing the exception report to the person in charge. To find out how to accomplish this, read our application note on remote database admin.
Do the debits equal the credits?
Financial transactions are often processed in batches. Between jobs aborting, program bugs, and less-than-careful data fixes by MIS staff, these batches can get out-of-balance. Using the Total command can quickly verify that all transactions net to zero.
>base fms.gl,5,reader {MPE, on HP-UX use >open ..}
>get d-transactions {MPE, on HP-UX use >select ..}
>output $null
>total trans-amt
>xeq
Totals (SUN, JAN 29, 1995, 2:56 PM):
TRANS-AMT 81451+
IN=6, OUT=6. CPU-Sec=1. Wall-Sec=1.
By default the results of the Total command are displayed on the screen ($STDLIST), but can be appended to the output file. To do, this you need to turn Set Squeeze Off and add a new Total command as follows:
total $file filename,append {MPE only}
Up to 15 fields can be totalled in each pass.