Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    No way to batch delete old, unpaid fines

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

    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." So that won't work.

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

    The p_cir_23 Override Debts (Fines) option does not work when the job is submitted with P-SUB-LIBRARY blank (All). Note: like p_cir_32, p_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 will be no match on z31_sub_library (which is almost always the specific, actual sublibrary -- from the item).

    If p_cir_23 is submitted with a specific, actual sublibrary as the P-SUB-LIBRARY, it could match the z31_sub_library, but would fail to match on the Z305-SUB-LIBRARY, which is almost always "ALEPH" or the xxx50 ADM library code.

    What you could do is to submit p_cir_23 multiple times, 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), and then submit the job with P-SUB-LIBRARY blank or xxx50 in order to delete the z305's, z303's, and the z31's which have "xxx50" as the z31_sub_library.

    Note:  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.... 

    > 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).    Then run cir-23 specifying "All" as the sublibrary value.

    Note:  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 the above. We suggest doing it on your Test server first.



    • Article last edited: 13-Jul-2018