• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
Our state requires that cash records be kept no longer than five years, even if unpaid. We can use cir-32 (Scrub/Remove Patron IDs) or cir-23 (Delete patron records) to eliminate paid or waived cash records, but we are not able to delete *un*paid cash transactions.

The p_cir_32 Help says: "The patron IDs of cash transaction records that have been closed or waived will be removed."  [This means that when 'Override Cash' = 'No', z31's with status "C" or "W" will have the patron ID anonymized  and those with status "O" or "T" will not.]  

p_cir_23 has an "Override Debts" option, but we can't get it to work: it doesn't waive open transactions.

Resolution:
The p_cir_23 Override Debts (Fines) option does not work when the job is submitted with P-SUB-LIBRARY blank ("All"). (The cir-23 Help says:  "The Override debts flag is relevant only for deleting a patron locally.")   Note that, like the cir-32 Service, cir-23 does not actually delete the z31 record; it scrubs the patron ID in the z31_rec_key to disassociate the patron from the z31 record.   

But, if p_cir_23 is submitted with the ADM library (xxx50) specified as the P-SUB-LIBRARY, there may be no match on the z31_sub_library (which may be either "XXX50" or the specific, actual sublibrary -- from the item).

If p_cir_23 is submitted with the actual specific sublibrary as the P-SUB-LIBRARY, it can match the z31_sub_library, but may fail to match on the Z305-SUB-LIBRARY.

Thus, if you want to override debts, we suggest submitting cir-23 multiple times:

  1. with each specific, actual sublibrary as the P-SUB-LIBRARY (in order to process the z31's which have an actual sublibrary as the z31_sub_library),

  2. with "xxx50" as the P-SUB-LIBRARY (in order to process the z31's which have "XXX50" as the z31_sub_library); and 

  3. with P-SUB-LIBRARY blank ("All")  in order to delete the z305's, z303's, z304's, z308's, (and the z31's which have "xxx50" as the z31_sub_library?).

Note:  it may be that run #3 (with "All" as the P-SUB-LIBRARY) does delete the "XXX50" z31's, but including #2 will not take much longer and is more certain.  

Note 2:  There have been cases where the preceding (runs of cir-23 alone) simply didn't work.   (The site was unable to delete certain patrons with fines they wanted to delete.)   We have found that the following SQL *always* works (and actually deletes the z31).... 

> s+ xxx50
SQL-XXX50> delete from z31 where substr (z31_rec_key,1,12) in (select substr(z305_rec_key,1,12) from z305 where z305_expiry_date < '20060101' and substr(z305_rec_key,13,5) = 'XXX50');

(where "XXX50" is the ADM library).    [Of course, be sure to back up the z31 table using the file-03 Service before doing any such SQL.]

Or, to limit to closed/waived z31's:

SQL-XXX50> delete from z31 where (z31_status = 'C' or z31_status = 'W') and substr (z31_rec_key,1,12) in (select substr(z305_rec_key,1,12) from z305 where z305_expiry_date < 'yyyymmdd' and substr(z305_rec_key,13,5) = 'XXX50');

Then, if you want to delete the patrons, run cir-23 specifying "All" as the sublibrary value.

Note 3:  The following SQL shows what Z305-SUB-LIBRARY values you have:

SQL>  select substr(z305_rec_key,13,5), count(*) from z305 group by  substr(z305_rec_key,13,5);

If you have z305 records with an actual sublibrary rather than the "XXX50" ADM library, you would need to also do runs of the above SQL with that actual sublibrary as the substr(z305_rec_key,13,5)value (rather than "XXX50"); and then run cir-23, specifying that actual sublibrary as the sublibrary value. 

You should back up the xxx50 z31, z309, and z111 tables, and the $usr_library z303, z304, z305, z308, and z353  tables before doing any the above. We suggest doing it on your Test server first.

• Article last edited: 15-Oct-2018