Notice counter incremented in 70,000 loan records

• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
We have noticed that the notice counter of thousands of loans incremented sometime in the last three days.
As a result, patrons are unable to renew loans via the OPAC regardless of the due date.

This incident has high priority for us because graduation is next week and many students will have outstanding fines.

Resolution:
The following SQL confirms that something unusual happened on April 30:

abc50@ALEPH20> select Z36_LETTER_DATE, count(*) from z36 where z36_letter_date > '20120426' group by Z36_LETTER_DATE;
**** Hit return to continue ****

Z36_LETTER_DATE COUNT(*)
--------------- ----------
20120427 301
20120428 410
20120429 622
20120430 74219
20120501 11

This SQL shows that there were *no* cases where a loan was changed to status Lost as result of this activity:

abc50@ALEPH20> select count(*) from z36 where z36_status = 'L' and Z36_LETTER_DATE = '20120430';
**** Hit return to continue ****

COUNT(*)
----------
0

That's good.

This SQL shows an unexpected number of loan records with 4 notices:

abc50@ALEPH20> select z36_LETTER_NUMBER, count(*) from z36 where Z36_LETTER_DATE = '20120430' group by z36_LETTER_NUMBER;
**** Hit return to continue ****

Z36_LETTER_NUMBER COUNT(*)
----------------- ----------
1 1120
2 934
3 769
4 70402
5 888

It seems that the problem process set the z36_letter_number to "4" incorrectly for some loans.

The z309 records each overdue notice as a z309_action '16' and the z309_rec_key_2 is the unique z36_number. Thus, the following SQL can be used to correct the Z36_LETTER_NUMBER's:

SQL> update z36 set Z36_LETTER_NUMBER = (select count(*) from z309 where z36_number = z309_rec_key_2 and z309_action = '16');

You should, of course, back up the z36 with p_file_03 before running this SQL.

• Article last edited: 10/8/2013