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

Description:
We are trying to purge all patrons with an expiration date on or before 12/31/2005 for our consortium's rcp50 ADM. We were able to purge the patrons that had no items associated with their patron record. Now we are trying to purge the patrons with items attached. We want to mark all the items as lost and delete all the fines. How would we accomplish this.

Resolution:
[Note: The deletion of loan records should be done *only* in consultation with Ex Libris Support staff.]

The p_cir_23 patron delete job does not delete patrons who have loan records. (From the cir-23 Help: "Note that a patron that has loans or outstanding ILL requests can not be deleted. This can not be overridden.")

First, you should change the z30_item_process_status of the affected items which don't already have it to "Lost".  This could be done via the item-62 ("Update Item Records") Service.  The z30_item_process_status might, alternatively, be changed to "MI" or "MS", if you don't use "LO".  ("LO" has the advantage of indicating that the item did formerly have a patron associated with it.)

Second, delete the rcp50 z36 loan records for these patrons:
SQL-RCP50> delete from z36 where z36_rec_key in (select z36_rec_key from z36, z305 where z36_id = substr(z305_rec_key,1,12) and z305_expiry_date < '20060101');
You should definitely back up the rcp50 z30 and z36 tables before doing the above steps.  We suggest doing it on your Test server first.

The p_cir_23 job has an option to override "debts". This is described in the article " No way to batch delete old, unpaid fines " (KB 16384-2625).   As described there, the p_cir_23 Override Debts option does not work when the job is submitted with P-SUB-LIBRARY blank (All).  And it does not actually delete the z31 records. If physical deletion of the fine records is desired, the following SQL will do that:
> s+ rcp50
SQL-RCP50> delete from z31 where substr (z31_rec_key,1,12) in (select z36_rec_key from z36, z305 where z36_id = substr(z305_rec_key,1,12) and z305_expiry_date < '20060101');

You should definitely back up the rcp50 z31 table before doing this. We suggest doing it on your Test server first.

Additional Information

This would be SQL to do the z30_item_process_status update:

> s+ rcp50
SQL-RCP50> update z30 set z30_item_process_status = 'LO' where z30_rec_key in (select z36_rec_key from z36, z305 where z36_id = substr(z305_rec_key,1,12) and z305_expiry_date < '20060101');

Note:  The z30_item_process_status might, alternatively, be changed to "MI" or "MS", if you don't use "LO".

• Article last edited: 15-Feb-2016