p_cash_03 report run after p_cir_23 has hundreds of "Borrower deleted" messages.

• Article Type: General
• Product: Aleph
• Product Version: 20
• Relevant for Installation Type: Dedicated-Direct; Direct; Local; Total Care

After running cir-23, the cash-03 (Payments Received) report is over 500 pages. Most of the entries have no staff member name and in the type of transaction indicate "Borrower deleted". According to the help for the cir-23 job (below) this was unexpected.

Why did these records show up on this report? The parameters for the job are:
procedure=p_cash_03
Fixed param: ABC50,C003_D_ABC___20080325,AL,,,20080325,20080325,0000,2300,SUB,00,

"Note that Z31 records are not really deleted, but rather the patron ID is scrubbed from all relevant record fields. If the cash transactions are not closed (in which case deleting the patron is possible only with the override flag), their status will be changed to 'Waived'. This way cash reports remain accurate even if a patron is deleted. "

(Note: Before running p_cir_23 we updated the z31_sub_library to "ABC50" so that p_cir_23 could match on it.)

Resolution:

[Note: See the articles linked to in Additional Information, below, for a general discussion of patron (and cash) deletion.]

The abc50 p_cir_23 runs from 2008-03-25 to 2008-03-27 scrubbed the z31-ID (the patron ID) for various z31's, changing it to "SCR.........".

The p_cash_03 report found these "SCR" patrons, looked for the corresponding Z305, and, finding none, issued the "Borrower deleted" message.

This SQL confirms that such cash records exist:

abc50@ALEPH18> select substr (Z31_PAYMENT_DATE_KEY,1,8), count(*) from z31 where Z31_PAYMENT_DATE_KEY like '200803%' and z31_rec_key like 'SCR%' group by substr (Z31_PAYMENT_DATE_KEY,1,8);
**** Hit return to continue ****

SUBSTR(Z31_PAYMENT_DATE_ COUNT(*)
------------------------ ----------
20080304 42
20080305 1
20080310 2
20080317 9
20080325 17580
20080326 18933
20080327 291


It might be that p_cash_03 could be changed to ignore "SCR" z31's, but it may be better to run p_cir_23 so that it will not delete patrons with Z31's which could appear on the report.

How? Use the following SQL to create an input file file for p_cir_23 which excludes patrons who have had made payments since yyyymmdd (the oldest date for which you will want to be running p_cash_03):

SQL> select substr(z31_rec_key,1,12) from z31 where z31_rec_key not like 'SCR%' minus select substr(z31_rec_key,1,12) from z31 where z31_payment_date_key > 'yyyymmdd';